GetWhatYouWant

SQL - Alternative to FOR XML PATH

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

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

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

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

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" />

<script language="javascript" event="onchange" for="ddl1">
 if (document.yourformnamehere.ddl1.value == "ABC"){   
  location.href = "page2.aspx";
 }else{
  this.form.submit();
 }
 return true;
</script>
  
In this case,
1) i have a dropdownlist which AutoPostBack set to true
2) but before the postback event triggered i want to check whether the value selected is it "ABC"
3) if yes, redirect the page to page2.aspx directly
4) if no, call the form.submit() to cause the postback

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

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

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

ASP.NET - GridView RowCommand get row index

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>

#1

VB

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

C#

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

or #2

VB

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

C#

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

ASP.NET - Javascript and Validator

ASP.NET - Copy file to another folder

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

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

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.NET - alert

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

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

ASP.NET - datetime format

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