GetWhatYouWant

SQL - Alternative to FOR XML PATH

mo meng — Fri, 08 Jan 2010 08:19:00 GMT

If you try to use the FOR XML PATH to concate multiple rows into one over a huge set of records, you will eventually hit the very slow performance issue. One way to overcome this issue is to create a function to help you concatenate the value as below

Calling SQL
-----------
SELECT ID, dbo.Concatenate_Mulitple_Columns(ID)
FROM TableName1

Function
--------
CREATE FUNCTION Concatenate_Mulitple_Columns
(
@ID INT
)
RETURNS VARCHAR(1000) --Amend this to the desired length
AS
BEGIN

DECLARE @Conc AS VARCHAR(1000)
SET @Conc = ''

SELECT @Conc = Coalesce(@Conc + ', ', '') + ColumnName
FROM TableName2
WHERE ID = @ID

RETURN @Conc

END

ASP.Net - Window form application change / set start up form

mo meng — Wed, 16 Dec 2009 07:54:00 GMT

1. Find the .cs file under the project i.e. program.cs or class1.cs

2. Find the Application.Run(new Form1()); line

3. Edit the Form1 to Form2 (your desired new startup window form name), done

Good tools

mo meng — Thu, 10 Dec 2009 08:07:00 GMT

There are variety of free tools out there for you to choose of.
Listed below, are all the tools which i found useful that can enhance your performance during your development as well as in daily life

1. ScreenHunter
Just a simple F6 key you can capture the picture on the screen
Download: http://wisdom-soft.com/products/screenhunter.htm

2. HotKeyz
Configure your shortcut keys to open programs
Download: http://www.skynergy.com/hotkeyz.html

3. Notepad++
Dont like to use notepad? Cannot undo more than one steps? Use this tools. Able to open many tabs in on environment
Download: http://notepad-plus.sourceforge.net/uk/download.php

4. Taskbar Shuffle
Sometime you dont want the tabs in your taskbar to be appended at the back, instead you want to shiff it to the front or middle. This tools offers great feasibility to shifting icons and tabs
Download: http://download.cnet.com/Taskbar-Shuffle/3000-2072_4-10531265.html

.Net Compressing file using SharpZipLib

mo meng — Wed, 09 Dec 2009 05:27:00 GMT

If you want to compress or decompress a file, SharpZipLibis a good alternative apart the build-in GZipStream / DeflateStream (2.0 & above) in System.IO.Compression. If you are using .net 1.0/1.1, you can find this library very useful since there is no build in compression in those frameworks.

download SharpZipLib: http://sharpdevelop.net/OpenSource/SharpZipLib/Download.aspx

In this scenario, i wrote a console app to generated data into an excel. The file size came out to about 25M, which is impossible for me to send the file out via email. therefore i need to compress the file, here is an example on how you can compress a file.

FileStream ostream;
byte[] obuffer;
string outPath = @"C:\\excel.zip";
string inPath = @"C:\\excel.xls";
ZipOutputStream oZipStream = new ZipOutputStream(File.Create(outPath)); // create zip stream
oZipStream.SetLevel(5);
ZipEntry oZipEntry;
oZipEntry = new ZipEntry(Path.GetFileName(inPath));
oZipEntry.Size = new FileInfo(inPath).Length;
oZipStream.PutNextEntry(oZipEntry);
ostream = File.OpenRead(inPath);
obuffer = new byte[ostream.Length];
ostream.Read(obuffer, 0, obuffer.Length);
oZipStream.Write(obuffer, 0, obuffer.Length);
oZipStream.Finish();
oZipStream.Close();

note the bolded text, you need to include this line of code, especially when you are using .net 1.0/1.1 because if try to extract out the file you compressed using window defaulted, you will get an error like "the compressed (zipped) Folder is invalid or corrunpted (Note: you will getting the error only when you are trying to extract using the windows default extraction tools, if you using winrar or 7zip i suppose you are able to extract without that line of code).

I encountered the error above when i was trying to extract the file, thanks to: http://blog.tylerholmes.com/2008/12/windows-xp-unzip-errors-with.html which help me solved the problem

ASP.Net - Javascript onChange does not trigger AutoPostBack

mo meng — Tue, 08 Dec 2009 01:30:00 GMT

If you have an AutoPostBack set to true inorder to fire some code behind methods but before the AutoPostBack event you want to check the selected value to do something else before the postback event occur, here's the code :

<asp:dropdownlist id="ddl1" runat="server" AutoPostBack="True" />

if (document.Query.productLineDL.value == "Voice"){ location.href = "QueryII.aspx"; }else{ this.form.submit(); } return true; if (document.Query.productLineDL.value == "Voice"){ location.href = "QueryII.aspx"; }else{ this.form.submit(); } return true;

ASP.NET - Class file get Session, Application and QueryString

mo meng — Mon, 23 Nov 2009 02:49:00 GMT

Session

System.Web.HttpContext.Current.Session["name"]

Application

System.Web.HttpContext.Current.Application["name"]

QueryString

System.Web.HttpContext.Current.Request.QueryString["name"]

EXCEL - Simple Date format

mo meng — Wed, 18 Nov 2009 03:03:00 GMT

DATE & TIME

=(DATE(YEAR(NOW()), MONTH(NOW()), DAY(NOW())) + TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW())))

Calculate working days different between two dates

=NETWORKDAYS(startdate,enddate)

SQL - Multiple rows into single column

mo meng — Wed, 20 May 2009 10:16:00 GMT

SELECT a.id,

concatename = substring((SELECT ( ', ' + name )

FROM table b

WHERE a.id = b.id

ORDER BY id

FOR XML PATH( '' )
), 3, 1000 )FROM table a

GROUP BY id

refer more at here:

http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=createacommadelimitedlist

ASP.NET - GridView RowCommand get row index

mo meng — Fri, 15 May 2009 04:48:00 GMT

if you did assign a value into the CommandArgument e.g. below, you need to use the #1 methond else #2

<ItemTemplate>
<asp:ImageButton ID="imgUpdate" runat="server" CommandName="Update1"
CommandArgument='<%# Eval("id") %>' ImageUrl="~/icons/Update001 (2).gif" />
</ItemTemplate>

Dim selectedRow As GridViewRow = DirectCast(DirectCast(e.CommandSource, LinkButton).NamingContainer, GridViewRow)
Dim intRowIndex As Integer = Convert.ToInt32(selectedRow.RowIndex)
GridView.Rows(intRowIndex).BackColor = System.Drawing.Color.Blue

GridViewRow selectedRow = (GridViewRow)((ImageButton)e.CommandSource).NamingContainer;
int intRowIndex = Convert.ToInt32(selectedRow.RowIndex);
GridView.Rows[intRowIndex].BackColor = System.Drawing.Color.Blue;

or #2

Dim selectedRow As GridViewRow = GridView.Rows(Convert.ToInt32(e.CommandArgument))
selectedRow.BackColor = System.Drawing.Color.Blue

GridViewRow selectedRow = GridView.Rows[Convert.ToInt32(e.CommandArgument)];
selectedRow.BackColor = System.Drawing.Color.Blue;

ASP.NET - Javascript and Validator

mo meng — Wed, 13 May 2009 05:24:00 GMT

when you need to call a javascript with a validator together, somehow the validator get passed by and the page get a refresh before the validator's message shows up. to cater for this look at the example below where i include the Page_ClientValidate() checking in the javascript

function Validation()

{

var tempString = document.getElementById('<%=TextBox1.ClientID %>').value;

"text/javascript">

function Validation()

{

var tempString = document.getElementById('<%=TextBox1.ClientID %>').value;

function Validation()

{

var tempString = document.getElementById('<%=TextBox1.ClientID %>').value;

if (tempString != "a" & Page_ClientValidate())

(tempString != "a" & Page_ClientValidate())

{alert('a')

return false;

}

else {

return true;

}

'a')

return false;

}

else {

return true;

}

return false;

}

else {

return true;

}

else {

return true;

}

return true;

}

</script>

script>

<asp:TextBox ID="txtUserId" runat="server"></asp:TextBox>

asp:TextBox ID="txtUserId" runat="server"></asp:TextBox>

<asp:RequiredFieldValidator ID="RequiredFieldValidator" ControlToValidate="txtUserId" runat="server" ErrorMessage="Please enter a user Id" Display="Dynamic" />

<asp:Button ID="Button2" runat="server" OnClientClick="return Validation()" Text="Button" />

ASP.NET - Copy file to another folder

mo meng — Mon, 11 May 2009 01:50:00 GMT

foreach (string item in Directory.GetFiles(Server.MapPath("~/PdfFolder/"), "*.*"))
{

File.Copy(item, Server.MapPath("~/Destination/") + Path.GetFileName(item), true);
File.Delete(item);

}

ASP.NET - INPUT button fire javascript and serverclick

mo meng — Sat, 09 May 2009 12:05:00 GMT

if you encounter problem when you want to use an html buttom control and you wish to fire both javascript and serverclick event, you will notice that you only can fire the javascript event ONLY. inorder for you to fire both the javascript and serverclick event you can try to change the type of input to submit

html
<script type="text/javascript">
function validate()
    {
    if (document.getElementById('TextBox1').value =="")
    {
    alert("Enter value into textbox")
    document.getElementById('TextBox1').focus()
    return false;

    }
    return true;
    }
</script>

<asp:textbox id="TextBox1" runat="server"></asp:textbox>
<input id="Button1" runat="server" onclick="return validate();" type="submit" value="button" onserverclick="Button1_ServerClick" />

code
protected void Button1_ServerClick(object sender, EventArgs e)
{
Response.Write("B");
}

ASP.NET - JavaScript radiobutton div visibility

mo meng — Tue, 05 May 2009 06:19:00 GMT

function displayPanelReason() {

document.getElementById('<%= PanelReason.ClientID %>').style.display = "block";

document.getElementById('<%= PanelUpdateTicketIT.ClientID %>').style.display = "none";

}

function displayPanelUpdateTicketIT() {

document.getElementById('<%= PanelReason.ClientID %>').style.display = "none";

document.getElementById('<%= PanelUpdateTicketIT.ClientID %>').style.display = "block";

}

function hideDIV() {

document.getElementById('<%= PanelReason.ClientID %>').style.display = "none";

document.getElementById('<%= PanelUpdateTicketIT.ClientID %>').style.display = "none";

}

document.getElementById('<%= PanelReason.ClientID %>').style.display = "block";

document.getElementById('<%= PanelUpdateTicketIT.ClientID %>').style.display = "none";

}

function displayPanelUpdateTicketIT() {

document.getElementById('<%= PanelReason.ClientID %>').style.display = "none";

document.getElementById('<%= PanelUpdateTicketIT.ClientID %>').style.display = "block";

}

function hideDIV() {

document.getElementById('<%= PanelReason.ClientID %>').style.display = "none";

document.getElementById('<%= PanelUpdateTicketIT.ClientID %>').style.display = "none";

}

document.getElementById('<%= PanelUpdateTicketIT.ClientID %>').style.display = "none";

}

function displayPanelUpdateTicketIT() {

document.getElementById('<%= PanelReason.ClientID %>').style.display = "none";

document.getElementById('<%= PanelUpdateTicketIT.ClientID %>').style.display = "block";

}

function hideDIV() {

document.getElementById('<%= PanelReason.ClientID %>').style.display = "none";

document.getElementById('<%= PanelUpdateTicketIT.ClientID %>').style.display = "none";

}

function displayPanelUpdateTicketIT() {

document.getElementById('<%= PanelReason.ClientID %>').style.display = "none";

document.getElementById('<%= PanelUpdateTicketIT.ClientID %>').style.display = "block";

}

function hideDIV() {

document.getElementById('<%= PanelReason.ClientID %>').style.display = "none";

document.getElementById('<%= PanelUpdateTicketIT.ClientID %>').style.display = "none";

}

document.getElementById('<%= PanelReason.ClientID %>').style.display = "none";

document.getElementById('<%= PanelUpdateTicketIT.ClientID %>').style.display = "block";

}

function hideDIV() {

document.getElementById('<%= PanelReason.ClientID %>').style.display = "none";

document.getElementById('<%= PanelUpdateTicketIT.ClientID %>').style.display = "none";

}

document.getElementById('<%= PanelUpdateTicketIT.ClientID %>').style.display = "block";

}

function hideDIV() {

document.getElementById('<%= PanelReason.ClientID %>').style.display = "none";

document.getElementById('<%= PanelUpdateTicketIT.ClientID %>').style.display = "none";

}

function hideDIV() {

document.getElementById('<%= PanelReason.ClientID %>').style.display = "none";

document.getElementById('<%= PanelUpdateTicketIT.ClientID %>').style.display = "none";

}

document.getElementById('<%= PanelReason.ClientID %>').style.display = "none";

document.getElementById('<%= PanelUpdateTicketIT.ClientID %>').style.display = "none";

}

document.getElementById('<%= PanelUpdateTicketIT.ClientID %>').style.display = "none";

}

<asp:RadioButton ID="RadioButton1" runat="server" GroupName="rbtn" onclick="displayPanelReason()" Text="PanelReason" />

asp:RadioButton ID="RadioButton1" runat="server" GroupName="rbtn" onclick="displayPanelReason()" Text="PanelReason" />

<asp:RadioButton ID="RadioButton2" runat="server" GroupName="rbtn" onclick="displayPanelUpdateTicketIT()" Text="PanelUpdateTicketIT" />

<asp:RadioButton ID="RadioButton3" runat="server" GroupName="rbtn" onclick="hideDIV()" Text="None" />

<div id="PanelReason" runat="server" style="display:none">PanelReason</div>

<div id="PanelUpdateTicketIT" runat="server" style="display:none">PanelUpdateTicketIT</div>

ASP.NET - alert

mo meng — Thu, 30 Apr 2009 03:34:00 GMT

ClientScript.RegisterStartupScript(typeof(Page), "Startup",

typeof(Page), "Startup",

"<script>alert('Put your message here')</script>");

);

use typeof instead of Me.GetType() or this.GetType()

http://blogs.ipona.com/james/archive/2006/10/03/6710.aspx

ASP.NET - datetime format

mo meng — Thu, 30 Apr 2009 00:57:00 GMT

Response.Write("<br />today:" + DateTime.Now.ToShortTimeString() + "<br />"); //today:8:56 AM

Response.Write("<br />day:" + DateTime.Now.ToString("dd") + "<br />"); //day:30

Response.Write("<br />month:" + DateTime.Now.ToString("MM") + "<br />"); //month:04

Response.Write("<br />month:" + DateTime.Now.ToString("MMM") + "<br />"); //month:Apr

Response.Write("<br />year:" + DateTime.Now.ToString("yyyy") + "<br />"); //year:2009

Response.Write("<br />hour:" + DateTime.Now.ToString("hh") + "<br />"); //hour:08

Response.Write("<br />minute:" + DateTime.Now.ToString("mm") + "<br />"); //minute:56

Response.Write("<br />AM/PM:" + DateTime.Now.ToString("tt") + "<br />"); //AM/PM:AM