C# / Asp.Net / AJAX / Sql Blog

Hello Swati

2010-12-16T20:32:00.001-08:00

Hi This is Swati

Writing a test blog post from MS Word
Kaise ho

SQL Interview Questions - Time Series Based Problems

2008-12-13T19:49:00.001-08:00

I was just wondering about time-series based SQL problems, and decided to put up a data sample based on the tea & chocolate consumption of Microsoft & Sun Microsystems. Let's take the sample data below.

SALESORDERID	ITEM_NAME	QUANTITY	COST_PER_UNIT	ORDERDATE	DUEDATE	ORDERSTATUS	CUSTOMERNAME
1	TEA	45.0000	1.2900	2/29/2008	3/31/2008	P	MICROSOFT
2	TEA	30.0000	1.2500	4/30/2008	7/31/2008	P	MICROSOFT
3	TEA	90.0000	1.5000	5/31/2008	7/31/2008	P	MICROSOFT
4	TEA	12.0000	2.0000	6/30/2008	8/31/2008	P	MICROSOFT
5	TEA	34.0000	4.3800	2/29/2008	4/30/2008	P	SUN
6	TEA	45.0000	3.9900	3/31/2008	5/31/2008	P	SUN
7	TEA	79.0000	3.5000	4/30/2008	7/31/2008	P	SUN
8	TEA	36.0000	5.3800	6/30/2008	7/31/2008	P	SUN
9	TEA	12.0000	4.3800	7/31/2008	7/31/2008	P	SUN
10	TEA	50.0000	4.2200	8/30/2008	9/30/2008	P	SUN
11	CHOCOLATE	110.0000	0.6900	3/31/2008	4/30/2008	P	SUN
12	CHOCOLATE	130.0000	1.2200	4/30/2008	5/31/2008	P	SUN
13	CHOCOLATE	76.0000	0.5500	5/31/2008	6/30/2008	P	SUN
14	CHOCOLATE	200.0000	0.8900	6/30/2008	7/31/2008	P	SUN
15	CHOCOLATE	230.0000	0.2300	7/31/2008	8/31/2008	P	SUN
16	CHOCOLATE	210.0000	0.7800	8/31/2008	9/30/2008	P	SUN
17	CHOCOLATE	199.0000	1.1000	9/30/2008	10/31/2008	P	SUN
18	CHOCOLATE	102.0000	2.0000	2/29/2008	3/31/2008	P	MICROSOFT
19	CHOCOLATE	245.0000	2.0000	3/31/2008	4/30/2008	P	MICROSOFT
20	CHOCOLATE	312.0000	2.0000	4/30/2008	5/31/2008	P	MICROSOFT
21	CHOCOLATE	345.0000	2.0000	5/31/2008	6/30/2008	P	MICROSOFT
22	CHOCOLATE	307.0000	2.0000	6/30/2008	7/31/2008	P	MICROSOFT
23	CHOCOLATE	120.0000	2.0000	7/15/2008	7/31/2008	P	MICROSOFT
24	CHOCOLATE	300.0000	2.0000	7/31/2008	8/31/2008	P	MICROSOFT
25	CHOCOLATE	299.0000	2.0000	8/31/2008	9/30/2008	P	MICROSOFT
26	CHOCOLATE	290.0000	2.0000	9/30/2008	10/31/2008	P	MICROSOFT
27	CHOCOLATE	10.0000	2.0000	10/5/2008	10/31/2008	P	MICROSOFT
28	CHOCOLATE	12.0000	2.0000	10/15/2008	10/31/2008	P	MICROSOFT
29	CHOCOLATE	12.0000	2.0000	10/20/2008	10/31/2008	P	MICROSOFT

--Write a query to get the TOTAL AMOUNT SPENT on tea consumption by each company between 03/31/2008 and 08/31/2008

SELECT CUSTOMERNAME, SUM(QUANTITY*COST_PER_UNIT) FROM #TMP_SALES

WHERE ORDERDATE BETWEEN '03/31/2008' AND '08/31/2008'

GROUP BY CUSTOMERNAME

--QUERY TO FIND AVERAGE NUMBER OF DAYS EACH CUSTOMER GIVES FOR AN ORDER

SELECT CUSTOMERNAME, AVG(DATEDIFF(D, ORDERDATE, DUEDATE))

FROM #TMP_SALES

GROUP BY CUSTOMERNAME

--QUERY TO FIND THE MONTHS WHEN EACH CUSTOMER DID NOT PLACE

--ANY ORDERS

SELECT * FROM

#TMP_SALES WHERE SALESORDERID NOT IN

(

SELECT T_OUTER.SALESORDERID

FROM #TMP_SALES T_OUTER

INNER JOIN #TMP_SALES T_INNER

ON T_OUTER.CUSTOMERNAME = T_INNER.CUSTOMERNAME

AND T_OUTER.ITEM_NAME = T_INNER.ITEM_NAME

AND MONTH(T_OUTER.ORDERDATE) = MONTH(DATEADD(M, -1, T_INNER.ORDERDATE))

)

Email Attachments using C#

2008-09-09T12:53:00.001-07:00

How to automatically generate e-mails with attachments from your C# Code ?

Now-a-days everyone wants Emails to be generated from the applciaiton itself. in my previous blog I have already discussed how to generate simple text e-mails.

But if you want to generate e-mails with attachments , still its not difficult at all. I have done it for sending an Excel Attachment. The txt attachment again is simpler, you just need to follow the same logic as excel but less complicated to copy and send. So, Lets Start.

PART I 

The code below shows the following steps for copying the excel sheet to be posted into the mailing attachment sheet(If you already have the sheet and just need to mail it, you can skip this part):-
1. Create a new excel workbook.
2. Create a new Directory.
3. Copy the old sheet to the new workbook sheet
4. You can turn on the displays on/off for the sheet by just doing
          application.displayAlerts = false/true;
5. Save the new workbook with the name required and close the excel.For saving
          you can use
            newWorkbook.saveAs(Save Name,System.Type.Missing,
            System.Type.Missing,System.Type.Missing,false, false,
            XlSaveAsAccessMode.xlNoChange,System.Type.Missing,
            System.Type.Missing,System.Type.Missing,
            System.Type.Missing,System.Type.Missing);
          and for closing you can use
            newWorkbook.Close(false,false,System.Type.Missing);

PART II 

Now, the steps below shows the e-mailing of the excel sheet as an attachment.
1. Make an object of MailMessage.
2. Set the parameters ,
          TO(toAddress); FROM(fromAddress); SUBJECT(a string one liner);     
          BODY(message to write inside the main body of mail);
3. Set the MailAttachment object with the filename to send an simply add the
           Attachment to the mail message.
4. SmtpMail.SmptServer needs to be set. It is the address of your office mail
          server.And then finally the message needs to be send, SmtpMail.Send();

private void SendEmail()
{

    Excel.Workbook newWorkbook = this.thisApplication.Workbooks.Add
                                 (XlWBATemplate.xlWBATWorksheet);

    string windowsLogin = Environment.UserName.ToString();

    object m = System.Reflection.Missing.Value;

    string fMsg = windowsLogin + @"@gmail.com";

    string note = "";

    try
    {

         Directory.CreateDirectory("ForEmail");

         Directory.SetCurrentDirectory("ForEmail");

         oldSheet.Copy(newWorkbook.Sheets[1],System.Type.Missing);

         Excel.Worksheet emailingSheet = ExcelHelpers.GetWorksheet
                                          (newWorkbook, "Recap");

         Excel.Range tempRange= emailingSheet.UsedRange;

         Excel.Worksheet copySheet = ExcelHelpers.GetWorksheet
                                          (newWorkbook, "Sheet1");

         newWorkbook.Sheets.FillAcrossSheets
                                 (tempRange,XlFillWith.xlFillWithAll);
         string accountName = acctComboBox.Text.Trim();

         copySheet.Cells[5,2] = accountName;

         thisApplication.DisplayAlerts = false;

         emailingSheet.Delete();

         thisApplication.DisplayAlerts = true;

         copySheet.Columns.AutoFit();

         newWorkbook.SaveAs("Info.xls",System.Type.Missing,
            System.Type.Missing,System.Type.Missing,false, false,
            XlSaveAsAccessMode.xlNoChange,System.Type.Missing,
            System.Type.Missing,System.Type.Missing,
            System.Type.Missing,System.Type.Missing);

         newWorkbook.Close(false,false,System.Type.Missing);
       

       

//***************************PART TWO BEGINS HERE******************************//

         string to = null;

         for(int r = 2; r < 10; r++)

         {

             if(((Range)mEmailSheet.Cells[r, 1]).Text != null &&

                  ((string)((Range)mEmailSheet.Cells[r, 1]).Text).Length < 1)

             {

                     break;

             }

             if(to == null)
               to = (string)((Range)mEmailSheet.Cells[r, 1]).Text+";";

             else

               to = to +((Range)mEmailSheet.Cells[r, 1]).Value2.ToString()+ ";";

         }

         MailMessage message = new MailMessage();

         message.To = to;

         message.From = fMsg.ToString();

         message.Subject="Email Submitted " ;  

         message.Body ="Attachment enclosed";

         Thread.Sleep(3000);

         DirectoryInfo dirInfo = new DirectoryInfo(Directory.GetCurrentDirectory
                                                                            ());

         FileInfo[] fInfo = dirInfo.GetFiles();

         string fileName = fInfo[0].Name;

         MailAttachment attach = new MailAttachment
                                 (Directory.GetCurrentDirectory() +

                                 @"\"+fileName,
                                 System.Web.Mail.MailEncoding.Base64);

         message.Attachments.Add(attach);

         SmtpMail.SmtpServer = "xyz.CompanyDomain.com";

         SmtpMail.Send(message);

         MessageBox.Show("E-Mail send.");

         Directory.SetCurrentDirectory("..");

         Directory.Delete("ForEmail",true);

     }

     catch(Exception e)

     {

          Console.WriteLine(e);

          newWorkbook.Close(false,false,System.Type.Missing);

          Directory.SetCurrentDirectory("..");

          Directory.Delete("ForEmail",true);

     }

}

How to automatically generate E-mails from your C# code?

2008-06-04T08:55:00.000-07:00

Generating E-mails through C# code can be very helpful to users. They just need to click a button and boom, the information is sent on the e-mail to the required people. If you want to generate a normal E-mail without attachment, these are the steps you can write:-- 1. You need to include , using System.Web.Mail; 2. The code for sending e-mail is as follows, string userID = Environment.UserName.ToString(); MailMessage message = new MailMessage(); string windowsLogin = Environment.UserName.ToString(); string fromMsg =windowsLogin+@"@companyDomainName.com"; string toMsg = null ; toMsg = (string) ((Range)sheet.Cells[i,15]).Text; // In case of Excel Application "To field can take in any constant or user supplied value from Text box etc. message.To = toMsg.ToString(); message.From = fromMsg.ToString(); message.Body = "STATUS HAS BEEN CONFIRMED. PLEASE CHECK YOUR RESPECTIVE UPDATES FROM THE APPLICATION."; message.Subject=" STATUS CONFIRMED BY " + userID.ToUpper(); SmtpMail.SmtpServer = "server-name.com"; SmtpMail.Send(message); Console.WriteLine(“E-mail Has Been Sent successfully”);

You have to know your server name so that you can send an e-mail. For knowing this, go to your Outlook Express

Click on Tools --> Send/Receive --> Send/Receive Settings --> Define Send/Receive Groups --> Edit --> Account Properties -->

There you find Microsoft Exchange Server name written in General Tab

Also in Tools --> Options --> Mail Setup --> You can find Send/Receive Settings , this will take you to the same pop-up , Define Send/Receive Groups. In case you want to Email an attachment using C# , please read my another blog topic "Emailing Attachments ".

Using Microsoft.Office.Interop.Excel, How to sort rows in a worksheet?

2008-06-03T12:47:00.000-07:00

It is very easy to sort rows using Microft.Office.Interop.Excel in a worksheet. PART I - SORTING BASED ON 1 OR 2 COLUMNS We can use the Sort method of the NamedRange object to sort the data in a worksheet. Just create a Range object i.e. Excel.Range sRng; Also, you already have a worksheet object, Worksheet SummSheet; Now assign Range to sRng , sRng = SummSheet.get_Range("A4","N" + rowNum);// here A4 is the starting cell and N"rowNum" is the ending cell Apply this sort method

sRng.Sort(sRng.Columns[1,System.Type.Missing],XlSortOrder.xlAscending, sRng.Columns[10,System.Type.Missing], System.Type.Missing,XlSortOrder.xlAscending,  System.Type.Missing,XlSortOrder.xlAscending,XlYesNoGuess.xlNo,System.Type.Missing,System.Type.Missing,XlSortOrientation.xlSortColumns,XlSortMethod.xlPinYin,XlSortDataOption.xlSortNormal,XlSortDataOption.xlSortNormal,XlSortDataOption.xlSortNormal);

In the above method we are sorting the rows as per 1st column .In case 1st column has same values the row will be sorted according to the 10th column. The Sorting Order is Ascending order. The same can be applied for Descending Order also. Only the SortOrder needs to be changed. PART II - SORTING BASED ON MORE THAN 2 COLUMNS In case you need to sort the rows based on more than 2 columns, we can implement ICOMPARABLE Interface's CompareTo method. We can have a class implement IComparable Interface and then re-define the CompareTo method. For e.g.


public class TryCompare:IComparable
{
 public string ID
 {
  get
  {
   return Did;
  }
  set
  {
   Did = value;
  }
 }
 public string QuanDiff
 {
  get
  {
   return DQuanDiff;
  }
  set
  {
   DQuanDiff = value;
  }
 }
 public string PriceDiff
 {
  get
  {
   return DPriceDiff;
  }
  set
  {
   DPriceDiff = value;
  }
 }


 public int CompareTo(object obj)
 {
  TryCompare CompareObj =(TryCompare)obj;
  int compResults = this.ID.CompareTo(CompareObj.ID);
  if(compResults==0)
  {
   if(this.QuanDiff==CompareObj.QuanDiff)
   {
    if(this.PriceDiff==CompareObj.PriceDiff)
     return 0;
    else if(this.PriceDiff>CompareObj.PriceDiff)
     return 1;
    else if(this.PriceDiff&lt;CompareObj.PriceDiff)
                  return -1;
   }
   else if(this.QuanDiff>CompareObj.QuanDiff)
   {
    return 1;
   }
   else if(this.QuanDiff&lt;CompareObj.QuanDiff)
              {
               return -1;
              }
  }
  return compResults;
 }

}

So in the example above , we are sorting based on 3 values. Now we just need to call , TryCompareObj.Sort(); This will solve all our sorting requirements.

To convert an INT into ASCII Char

2008-06-03T12:29:00.000-07:00

Converting INTEGER into an ASCII char is very simple . In C# we dont need to do anything special, just type-cast the INT into a CHAR. int i = 65; char a = (char) i; Console.WriteLine("Result : "+a.toString()); Result : A Converting ASCII char into an INTEGER is also same way but vice versa.Type-casting CHAR into an INT. char a = 'A'; int i = (int) a; Console.WriteLine("Result : "+i.toString()); Result : 65

Error : The assembly could not be found at or could not be loaded

2008-05-29T06:57:00.000-07:00

I have an excel application created using .Net framework 1.1 and Microsoft Office Excel 2003. This application named CBlotter with the same settings and dll in all the user computers was running fine but only in 1 computer , I encountered the error saying "The assembly CBlotter could not be found at .\CBlotter_bin\ or could not be loaded. You can still edit and save the document.Contact your administrator or the author of this document for further assistance." I checked all the user permissioning of the folder. I gave the full trust policy if not already given to the folder by using Caspol.exe(Code Access Security Policy Tool). This can be done by going to the specified v1.1/v2.0 folder. If you want to grant full trust to a local folder , then type caspol -u -ag All_Code -url C:\\\* FullTrust -n "" -d "" If you want to grant full trust to a netwrok folder , then type caspol -pp off -m -ag LocalIntranet_Zone -url \\\\* FullTrust -n "" -d "" After assigning the right permissioning and full trust , usually the assembly loading/cannot find error goes away but in my case it dint. So Now, I checked for the assemblies required for my .net code to run . In VS2003 we add reference to the COM library called 'Microsoft Excel 11.0 Object Library'. This helps us use the Excel Objects and write our piece of code using VS2003. When I checked the library in assembly cache (GAC), using microsoft 1.1 configuration GUI, I was not able to locate the assembly. This actually raised a question as Microsoft Office Excel 2003 and .Net 1.1 framework was completely installed on User's PC then why were the Primary Interop Libraries (PIA) missing. After researching I found out that these libraries require re-registration or installation sometimes when some other office application initiates a setup that causes library registration to be rewritten. In my case Microsoft Excel SP3 installation had removed the PIAs altogether . So I just installed the PIAs again by going to the site http://support.microsoft.com/kb/897646 This solved my problem.

Access Previous Page's Controls Using Cross Page Postback

2008-01-01T20:02:00.000-08:00

How to access previous page's controls

Before Asp.Net 2.0 was introduced, accessing the previous page's controls from a given page was a tricky affair. You had to use nice but not so right features like Session, HttpContext, even cookies in some cases, to persist relevant information from one page to another. Session is useful when you have to persist a bunch of data across the user's session, but it is not suitable for passing random values between pages. The primary reason for this being that as your application gets more and more complex, the Session state could become cluttered and confusing because of similarly named session values, and could easily become a maintainence nightmare. After a period of time, you would not remember what Session state variables you used to pass values between which pages.

Asp.Net 2.0 comes with a very handy feature called the PreviousPage property. This is a public property of the Page class, and refers to the PreviousPage instance. Using this property, you can access the previous page's public properties. You can also access the previous page's controls using the FindControll method.

Let us take an example. Suppose we have the following web page, that has a simple textbox and a button. Note that the button's PostBackUrl property is set to another page, meaning that when the button is clicked, the current webform will post back to the PostBackUrl page (If you are not clear with cross page HTTP postbacks, look at this post).

    <%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

    <html xmlns="http://www.w3.org/1999/xhtml" >
    <head runat="server">
     <title>Untitled Page</title>
    </head>
    <body>
     <form id="form1" runat="server">
     <div>
      <asp:Button ID="Button1" runat="server" PostBackUrl="~/Default2.aspx" Text="Button" />
      <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox></div>
     </form>
    </body>
    </html>

What happens here is that Asp.Net looks at the PostBackUrl property of the button and understands that form needs to be submitted to another form. To achieve this, it emits a javascript function called WebForm_DoPostBackWithOptions that makes sure that the form is correctly submitted to another page. To further understand what I am saying, look at the source for your page (right click, view source). Here's what our button looks like in actual HTML :

     <input type="submit" name="Button1" value="Button" onclick="javascript:WebForm_DoPostBackWithOptions(new WebForm_PostBackOptions(&quot;Button1&quot;, &quot;&quot;, false, &quot;&quot;, &quot;Default2.aspx&quot;, false, false))" id="Button1" />

Now to understand fully how Asp.Net posts back to another form, we must first understand the mechanism Asp.Net uses to postback a form. If you carefully look at the above HTML source for the button, you will see that onclick, the button calls a javascript method called [WebForm_DoPostBackWithOptions]. This method comes out of the box with Asp.Net. To view it's contents, we need to find the Javascript file to which the method belongs to. If you glance at the HTML source of the page, you will see that there is a file called WebResource.axd included with your page :

    <script src="/WebSites1/WebResource.axd?d=wKUbMd8wFV3uZ8WZXaQryA2&amp;t=633245101798773778" type="text/javascript"></script>

To look at the actual webresource.axd, do this : Internet Explorer --> Tools --> Settings --> View Files. This will open up a folder, where there will be one or many instances of WebResource.axd. This is a standard Javascript file that contains common Asp.Net page related functionality.

You can go through the WebForm_DoPostBackWithOptions method in the file to understand the exact mechanism for postback. Specifically, note how the [action] property of the webform is changed to the other page. Upon form submission, Asp.Net posts the form to the other page, instead of doing a normal page postback to itself. Upon postback, even though the form posts to another page, the original page's page lifecycle executes upto the [OnLoadComplete] event. After that, Asp.Net transfers the page execution to the other page (I assume it does a Server.Transfer), and also sets the IsCrossPagePostback property for the next page. Once on the other page, it continues the normal page lifecycle of the other page, and renders on page.

All this can get a bit confusing, so let's go through the entire thing again :

1. The first page is Page_A. User clicks a button that has the PostBackURL property set to Page_B.

2. Upon the button click, the [WebForm_DoPostBackWithOptions] javascript method is called. This method is part of the WebResource.axd file.

3. This method sets the form's [action] property to the Page_B, and posts the form.

4. Page_A page lifecycle starts executing upto the OnLoadComplete event, after which Asp.Net transfers execution to the Page_B, also setting Page_B's [IsCrossPagePostback] property on the way. Also, Page_B's PreviousPage property is set to the Page instance object of Page_A. (You know that each page is compiled into an object that derives from System.Web.UI.Page, right. That's the Page Instance Object I was referring to.)

5. Page_B page now starts executing it's normal page lifecycle. Since PreviousPage refers to the instance of Page_A, you can access any public property on the page, any web control on the page, infact, you can call public methods on Page_A.

6. Page_B now executes its normal page lifecycle, accessing public properties/methods/controls on Page_A if required, and renders it's HTML.

I would have loved to explain how I figured out all this internal stuff about Cross Page postbacks, but can't for "lack of space" (it's more like "lack of desire to keep on drooling on the same thing" ;) ), but you can take a look at the source code right here I used to figure out what I did. You can put this code into Debug, setting breakpoints at various key methods to understand how it works.

An oh yes, do remember that you can access 1. Public Properties, 2. Page Controls, 3. Static Variables, 4. Call public methods, using the PreviousPage property. So go ahead and play around with this powerful feature, and find interesting ways to use it in your Asp.Net WebApps.

Passing values using HTTP POST in Asp.Net

2007-12-27T20:42:00.001-08:00

By doing HTTP POST to another page

A lot of Asp.Net developers might not be aware of this, but one of the ways of passing values from one page to another is a normal HTTP POST. Before we dive into it, let me ask if you know exactly what this means : "An Asp.Net webform posts back to itself".

Basically, when you click on a button to submit a webform, you are sending that form back to the server. Here, webform means whatever falls between the <form></form> tags on your page. In technologies such as ASP, you can have multiple <form></form> elements on the same web page. When the user posts back the form, the [action] attribute of the form decides to which page the form gets submitted.

For example, suppose you have a form that looks like this :

<form action="PostPage.aspx" id="SimplePageForm" method="Post">

    Please enter text here that you want to see on the next page : 
                <input type="text" name="myText" id="theTxt" />

    <hr />

    <input type="submit" name="btn" id="btnSubmit" />

</form>

We basically want to create a form that posts to some other page. For e.g., our current form has action="PostPage.aspx". This means that when you submit the current page, it shall post back to PostPage.aspx page, and not to itself. The current FORM's elements shall be available on this other ASPX page, and can be accessed using the .net Request object.

Let me dwell a little bit more on this. On the current form, you have a textbox with the [name] property set to "myText". When you post this page, the entire form's data posts back to the server as a name-value collection. Meaning, a name-value collection will be sent back to the server, where one entry has name = "myText", and value = whatever value you entered in the textbox. There will be similar name-value pair entries for other controls on the page. As an aside, note that HTML handles the POSTed values differently for different controls. For e.g., for a textbox, the value is set to whatever text you enter in the textbox, whereas for a dropdown list, it will be set to the value that has been selected at the time of postback.

Bear in mind that the form is posted to a different page, i.e., PostPage.aspx, so the name-value collection is accessible on the PostPage.aspx. Instead of providing you with the raw posted values, Asp.Net wraps this information in the Request object. You can access the posted value using Request.Form["myText"]. Again, as an aside ;), if you are using <input type="file"> on your form, i.e., if you are using a file upload control, the story is slightly different. In this case, you can access the path of the files to be uploaded using Request.Files.

Finally, all we need to do in the PostPage.aspx is to access the posted values using the Request.Form collection. Take a look at the source code below, before we move to the final "prescriptive guidance" for this type of solution.

using System;

using System.Data;

using System.Configuration;

using System.Collections;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

public partial class A_2_Default : System.Web.UI.Page

    protected void Page_Load(object sender, EventArgs e)

    Response.Write("You typed in : " + Request.Form["myText"]);

As is evident, we just get the posted value for the control named "myText" using the Request.Form collection, and print it out.

The above solution is not feasible if you are trying to pass values between ASPX pages. The simple reason for this being that Asp.Net does not allow <form></form> elements to post to any other form other than itself. However, you can have multiple HTML forms on your webpage (ones without runat="server"). The advantage of doing this is that it allows you to show one/more forms to the user at the same time. Depending upon the user action, one of the form is posted back. This way, each form can have its own "processing page" to which it posts back on each submit. I'm not saying this is the best practice solution, but just that even simple HTTP POST can be handy at times.

How to run serverside code from client side script

2007-12-05T10:42:00.000-08:00

In the last article, we saw how to access server side control values on the client side. However, an even more interesting (and lesser known) mechanism in Asp.Net is running server side code in client side script. This can come in very handy in some situations, as we'll see through the following example. Suppose we need to create a graphical representation of some quantity. Let us say we have two variables on the server side, a [barCount] variable, to indicate the value to be shown on the graph, and a maxCount variable, that determines the maximum value that can be shown in the graph. To give you an idea of what I'm talking about, look at the following screenshot: The variables [maxCount] and [barCount] are serverside variables in your C# code behind file. Note that they have to be made public. maxCount is the total number of bars on the graph, while barCount gives the actual value to be represented on the graph. How did we do it ? One way, of course, is to use a repeater control, and modify it to render the required output. We are not particularly in favor of this approach, because a repeater is more conducive to databinding kind of functionality, not the one we are discussing here. Instead, here's the code we used :

<form id="form1" runat="server">
You Entered :
<br />
maxCount = <%=maxCount %>
<br />
barCount = <%=barCount %>
<table width="100%">
<tr>
<%for(int i = 0; i < maxCount; i++ ) %>
               <%{ %>
                  <%  if( i <= barCount )  %>
<%  { %>
<td style="border-right: #000000 1px solid; border-top: #000000 1px solid; border-left: #000000 1px solid;
color: #6600ff; border-bottom: #000000 1px solid; background-color: #0000cc;">
&nbsp;
</td>
<% }%>
<% else%>
<%  { %>
<td style="border-right: #000000 1px solid; border-top: #000000 1px solid; border-left: #000000 1px solid;
border-bottom: #000000 1px solid;">
&nbsp;
</td>
<%  }%>
<%} %>
</tr>
</table>
</form>

Recall that the <% %> blocks are used to emit server side code on the client side. What is happening here is that when the Page HTML is being generated, the [for] loop code gets executed. Since we declared barCount & maxCount as public variables, these are accessible in the code block. Depending on these values, an appropriate number of <TD> elements are inserted in the code, which eventually gives us the output graph. Please note that the script blocks are executed during the [RENDER] phase of the page. It means that if you are setting the variable values multiple times during your page lifecycle, the value set just prior to Render will be used. Keep in mind the following points about client code blocks :  This mechanism was put in place for backward compatibility with ASP.  Only public variables / properties / methods are accessible in the client code blocks.  Should be used only where appropriate. Check out the actual source code here. View the running application here. Happy Coding !!

How to refer a server side control inside client side script

2007-11-29T21:29:00.000-08:00

Referencing a control inside a user control / master page on the client side is a little complicated because Asp.Net "fudges" the ID of all controls rendered as part of a user control, or a master page template. We'll look at the "why" of this a little later in the article, but let us look at the "how" of it with an example. Create a simple User Control which has a single button btnInput :

<%@ Control Language="C#" AutoEventWireup="true" CodeFile="UsrCtrl.ascx.cs" Inherits="A_1_UsrCtrl" %>
<script language="javascript" type="text/javascript">
    function btnClicked(btnID)
    {
        var obj = document.getElementById("<%=btnInput.ClientID %>");
        alert('There is a button named : ' + obj.id + ' on this user control');
    }
</script>

<input type="button" runat="server" id="btnInput" value="Click Me" onclick="btnClicked()" />

Pay attention to the javascript function - we're getting the object using document.getElementById( ... ), and displaying an alert box with the ID of our button. Embed this on an Aspx page, and run it. When the button is clicked, you get the following error : So what happened ? To understand, view the source of your page (you can do this by right click on the page --> View Source). Look at our button control in the rendered HTML, it looks something like this :

<input name="UsrCtrl1$btnInput" type="button" id="UsrCtrl1_btnInput" value="Click Me" onclick="btnClicked()" />

What happened ? The ID of the User Control has been prepended to the front of the button's ID. You might be wondering, "why on earth would asp.net fudge the ID of controls". To understand why, imagine a situation where you have created five different user controls, each having a label named "btnInput". When you put all these five user controls on a single page, and if Asp.Net renders all the controls as is, we would have five buttons on the same webpage, having the ID "btnInput". This would not be acceptable, since each HTML element on a web page should have a unique ID. But in this case, when the page renders, there would be no way to distinguish which button is from which usercontrol, since all of them have the same ID. That is the reason for the following rule in Asp.Net : "If a control is embedded in a user control / master page, it's ID property is prepended with the parent user control's ID when the page is rendered". That is the reason that the user control's ID has been prepended to the button's ID in our above example. But this behavior creates problems in client side script. You would normally try to refer to a control as
document.getElementById([control id]
But in our case, you can't just use the control's ID. You have to use the actual "fudged" ID of the control. To account for this behavior in our client side javascript, we can use a handy asp.net control property called "ClientID". This property is always set to the actual ID of the control on the rendered page. Which means that if the control is not inside a user control, the ClientID would be same as the ID, but if the control is inside a user control, the ClientID will be [User Control ID]_[Control ID]. For e.g. in our case, ClientID will be set to "UsrCtrl1_btnInput". Here's how to use it to get the actual ID of a page webcontrol :

    function btnClicked(btnID)
    {
        var obj = document.getElementById("<%=btnInput.ClientID %>");
        alert('There is a button named : ' + obj.id + ' on this user control');
    }

Look at the highlighed code. The magic here is the <%= ... %> block. What this does is that when the page is rendered(as html), it executes the code between the

 <%= ... %>

, and places the result of the embedded code in its place. Please note the fact that the code blocks are evaluated after the Render page method fires. So, if you are changing the server side variable in different methods, (say, page_load, preRender, and some event handler), only the most recent value will be reflected on the client side (in our case, the value set in the preRender method). The above highlighted line looks like the following when the page is rendered :

var obj = document.getElementById("UsrCtrl1_btnInput");

Once you have this, you can access the control on the client side just like any other normal control. Interestingly, the <%= %> blocks are not limited to only control properties. You can even call the page's public methods, and the returned values will be substituted in the output HTML. For e.g., let's say we have a C# method codeBehindMethod() that always returns "TEST". The following line is gonna show an alert box with the message "TEST".

alert(<%= codeBehindMethod() %>);

Until now, we saw how to access a server side variables in client side script. However, it is also possible to run your C# code on the client side. This can be put to use to achieve some pretty sleek functionality. In my next post, we'll see how to execute C# code on the client side to achieve convenient results.

How to pass values from popup window to calling page

2007-11-23T21:53:00.000-08:00

Passing values from popup window to parent page can get tricky sometimes, especially if you are attempting to do heavy duty stuff in the popup window. The best way to do this is to create hidden variables on the parent page, and populate these from the popup window with whatever values need to be passed to the parent. As usual, let us take an example. Suppose we have the following requirement : 1. Create an .aspx page, that opens a popup window on a button click. 2. The popup window that opens up should have a user information form that collects UserName, Address, Phone No. 3. Upon filling up the details, the user shall click a button that closes the window and passes the information to the main page. 4. The user information from the popup window should be displayed on the main page now. So basically, we need to pass values from the popup window to the parent page. Let us look at the parent page code first :

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default3.aspx.cs" Inherits="Default3" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Untitled Page</title>

    <script language="javascript" type="text/javascript">
        function openWindow()
        {
            window.open('/website3/testpage.aspx', null, 'height=200,width=400,status=yes,toolbar=no,menubar=no,location=no');
            return false;
        }
    </script>

</head>
<body>
    <form id="form1" runat="server">
        <div>
            <input type="button" runat="server" onclick="openWindow();" value="Click Me" />
            <b>Information from the popup window :</b>
            <br />
            <label>
                UserName :
            </label>
            <label id="lblUName">
            </label>
            <br />
            <label>
                Address :
            </label>
            <label id="lblAddress">
            </label>
            <br />
            <label>
                Phone no :
            </label>
            <label id="lblPhone">
            </label>
        </div>
    </form>
</body>
</html

Run the page and click on the button to open a popup window that shows the a page TestPage.aspx. This is the aspx page for our popup window. As per the requirements, this should have some data entry fields in which the user can enter values, and also a button that closes the window. Upon window close, the user-entered values should be persisted to the main page. Look at the code below :

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="TestPage.aspx.cs" Inherits="TestPage" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Popup Window</title>
    <script language="javascript" type="text/javascript">
        function passValues()
        {
            ;
        }
    </script>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <label>
                UserName :
            </label>
            <input type="text" id="txtUName" />
            <br />
            <label>
                Address :
            </label>
            <input type="text" id="txtAddress" />
            <br />
            <label>
                Phone No. :
            </label>
            <input type="text" id="txtPhone" />
            <br />
                        <input type="submit" onclick="passValues(); window.close()" value="Submit" />
        </div>
    </form>
</body>
</html>

Note that we have created the function passValues but have not yet implemented it. Now run the main page, you should see something like this : So much for laying the groundwork. The tough task now is to propagate the values that the user enters in the popup window, to the parent page. As you can guess, we'll have to first access the popup window's parent window, and once we have the parent window reference, we can get the required elements by doing the following : [parentWindow Object].document.getElementById('lblUName'); To access an element on the parent window, you can do the following : window.opener.document.getElementById('lblPhone').innerText = 'xyz'; Look at the code below to get a better idea of what we are talking about :

        function passValues()
        {
            window.opener.document.getElementById('lblUName').innerText = document.getElementById('txtUName').value;
            window.opener.document.getElementById('lblAddress').innerText = document.getElementById('txtAddress').value;
            window.opener.document.getElementById('lblPhone').innerText = document.getElementById('txtPhone').value;
        }

This function grabs the window's parentWindow's document object, and searches for the required elements (lblUName, lblAddress, lblPhone) therein. It assigns these objects the values from the popup window textboxes. Now to complicate things a little bit further, let us try to access elements of the parent page that are in an iFrame. Here's the (modified) parent page :

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default3.aspx.cs" Inherits="Default3" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Untitled Page</title>
.
.
.
.
            <label>
                Phone no :
            </label>
            <label id="lblPhone">
            </label>
        </div>
        <iframe src="HTMLPage.htm" width="400px" height="200px"></iframe>
    </form>

Confused ? Review the screenshot below to jog your memory : Our goal here is this : -- When the user clicks the Submit button on the popup window, the UserName, Address and Phone no. fields should be displayed on the parent page. In addition to that, the UserName should also be propagated to the iFrame's page's [lblPopupValue] label control. To achieve this, we would have to access the [frames] collection of the parent window object. To cut a long story short, here's the magical line of code : window.opener.frames(0).document.getElementById('lblPopupValue').innerText = 'blah blah'; Pardon the blah blah. Here's the complete code :

        function passValues()
        {
            window.opener.document.getElementById('lblUName').innerText = document.getElementById('txtUName').value;
            window.opener.document.getElementById('lblAddress').innerText = document.getElementById('txtAddress').value;
            window.opener.document.getElementById('lblPhone').innerText = document.getElementById('txtPhone').value;   
            window.opener.frames(0).document.getElementById('lblPopupValue').innerText = document.getElementById('txtUName').value;
        }

` And that's it. To summarize, we've successfully opened / closed a popup window, passed values to the parent window, and passed values to an iFrame embedded in the parent window.

How to call a javascript method from the server side code

2007-11-19T20:14:00.000-08:00

Calling a javascript method from a server side control requires adding an attribute to the control. Let us take an example -- Suppose we have a simple asp.net button on our web page, and want to display a javascript alert when this button is clicked. Our first instinct would be to put the script in the .aspx page itself, like this :

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default2" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Javascript Experiment</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <asp:Button ID="btnClickMe" Text="Click Me" runat="server"     OnClick="alert('Clicked');" />
    </div>
    </form>
</body>
</html>

Try compiling it (in VS 2005), and you get the following error : error CS1012: Too many characters in character literal Why ? Because the [OnClick] here refers to the serverside OnClick event(the one you define in your code-behind). It does not refer to the javascript onclick event of the button. So how do you "tell" the server-side button control to call the client side onclick event ? By writing the following in your code-behind :

btnClickMe.Attributes.Add("onclick", "alert('Clicked');");

Recall that each asp.net server control is rendered as an HTML element on the page. So before including the above line in our code, here's how our button is rendered :

<input type="submit" name="btnClickMe" value="Click Me" id="btnClickMe" />

As you can see, our button [btnClickMe] is rendered as an <input /> html element. It has four attributes : type, name, value, and ID. When we add the Attributes.Add method to our code-behind, here's how the button looks :

<input type="submit" name="btnClickMe" value="Click Me" onclick="alert('Clicked');" id="btnClickMe" />

Note the [onclick] attribute rendered here. This basically causes the "alert" to be shown when the button's onclick event is raised (in other words, when the button is clicked). You can further extend this example to call a javascript method that does something meaningful on the button click. Guess it can look something like this :

btnClickMe.Attributes.Add("onclick", "someMeaningfulMethod();");

So to summarize, we don't directly call the javascript method from the server side code. Instead, we add attributes to the control so that when the control is rendered (as html) on the browser, it also contains the attributes (event handlers, in this case) we have added.

How to access a client-side control in asp.net code behind file

2007-11-17T21:34:00.000-08:00

Accessing client-side variables/elements on the server side is not a straightforward affair. This is because the elements you create using javascript are not "server" controls, so you cannot directly access them in your code behind code like regular asp.net server controls (by the way, server controls are controls that have the runat="server" attribute set.) To achieve this functionality, we use hidden controls. Hidden controls are HTML controls that remain "hidden" on the page. In other words, you can assign a text value to the hidden controls (just like label, textbox etc.), but their contents are not rendered on the screen. By creating hidden controls with runat="server" set, we can access them both on client-side and server-side code. Let us take an example. Suppose we have a simple .aspx page which has three elements : 1. An Asp.Net ListBox control, 2. A button for adding new options to the listbox, and 3. A button for posting back the page. Here's what it looks like :

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Untitled Page</title>
    <script language="javascript" type="text/javascript">
        function myMethod()
        {
            var listbox = document.getElementById('ListBox1');
            var newElement = document.createElement('option');
            newElement.text = 'Test';
            listbox.options.add(newElement);
            window.event.returnValue = false;
        }       
    </script>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <input id="Submit1" onclick="myMethod();" type="submit"
                value="Add Element" />
            <asp:ListBox ID="ListBox1" runat="server"></asp:ListBox>
            <input id="Submit2" type="submit" value="PostBack Page" />
        </div>
    </form>
</body>
</html>

Run the above page. Observe that on clicking on the first button, our javascript method "myMethod()" successfully adds new elements to the listbox. However, as soon as you postback the page using the second button, the elements that you added are lost. Why ? The reason for this is that although we have added the controls on the client-side, the viewstate variable on our page hasn't changed at all. It still says that the page has only one server control - the Listbox. So while processing the page upon postback, when asp.net constructs the "tree" of all controls on the page, it does not realize that there have been a few listbox elements added to the page on the client side (because the viewstate doesn't say so). Hence, when it renders back the page upon postback, it just renders an empty listbox. To get around this, we would have to make an "entry" for the new elements in the viewstate, which is not possible because viewstate is encrypted. The second option is to use a hidden variable with runat="server", and put a comma seperated list of the new listbox elements added in this hidden variable. That way, when the page posts back, we read the value in the hidden variable and accordingly add new elements to the Listbox. Here's the modified .Aspx page for this :

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Untitled Page</title>
    <script language="javascript" type="text/javascript">
        function myMethod()
        {
            var listbox = document.getElementById('ListBox1');
            var newElement = document.createElement('option');
            newElement.text = 'Test';
            listbox.options.add(newElement);
            document.getElementById('hdnListElements').value = document.getElementById('hdnListElements').value + ',' + newElement.text;
            window.event.returnValue = false;
        }         
    </script>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <input id="Submit1" onclick="myMethod();" type="submit"
                value="Add Element" />
            <asp:ListBox ID="ListBox1" runat="server"></asp:ListBox>
            <input id="Submit2" type="submit" value="PostBack Page" />
            <input type="hidden" runat="server" id="hdnListElements" />
        </div>
    </form>
</body>
</html>

Observe carefully the highlighted line. We are basically creating a comma-seperated list of all the elements added to the listbox on the clientside. On the server-side, we read these values, and populate the listbox with the newly added child elements. Here's the C# code :

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
            string str = hdnListElements.Value;
            // If the hidden variable is empty, no new elements added.
            if (!String.IsNullOrEmpty(str))
            {
                // Split the comma-seperated list into an array of strings.
                string[] strArray = str.Split(",".ToCharArray(),StringSplitOptions.RemoveEmptyEntries);
                foreach (string sTemp in strArray)
                {
                    // foreach newly added element, add a ListItem to the listbox control
                    ListBox1.Items.Add(new ListItem(sTemp, sTemp));
                }
                // Clear the hidden variable, so it does not contain the currently added elements.
                hdnListElements.Value = String.Empty;
            }
    }
}

And that is it. That's a pure and simple way to access client-side variables / controls in server-side code.

Adding Rows dynamically to a gridview [Part 2]

2007-11-14T20:48:00.000-08:00

In our last post, we saw how to add a row dynamically to a gridview. But we still have to see how to persist values added on the client side to the server side. We shall not be using the recent AJAX based methodologies, instead we'll be using the good old fashioned way of persisting client side data to the server-side -- hidden variables. Before we move further, we need to make two changes to the gridview we created in our last post: 1) Add a hidden variable with runat="server", and id="hdnRowCount". This will contain the number of rows in the gridview when the gridview was created, so that we can identify which rows have been dynamically added by the user. 2) Add a hidden variable with runat="server" and id ="hdnPostAddedRows". This variable is responsible for persisting the changes we made on the client side to the server side. More on how to do this later in the article. Here is how the updated page looks like :

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="WorkOrderID"
                DataSourceID="SqlDataSource1">
                <Columns>
                    <asp:BoundField DataField="WorkOrderID" HeaderText="WorkOrderID" InsertVisible="False"
                        ReadOnly="True" SortExpression="WorkOrderID" />
                    <asp:BoundField DataField="ProductID" HeaderText="ProductID" SortExpression="ProductID" />
                    <asp:BoundField DataField="OrderQty" HeaderText="OrderQty" SortExpression="OrderQty" />
                    <asp:BoundField DataField="StockedQty" HeaderText="StockedQty" ReadOnly="True" SortExpression="StockedQty" />
                    <asp:BoundField DataField="ScrappedQty" HeaderText="ScrappedQty" SortExpression="ScrappedQty" />
                    <asp:BoundField DataField="StartDate" HeaderText="StartDate" SortExpression="StartDate" />
                    <asp:BoundField DataField="EndDate" HeaderText="EndDate" SortExpression="EndDate" />
                    <asp:BoundField DataField="DueDate" HeaderText="DueDate" SortExpression="DueDate" />
                    <asp:BoundField DataField="ScrapReasonID" HeaderText="ScrapReasonID" SortExpression="ScrapReasonID" />
                    <asp:BoundField DataField="ModifiedDate" HeaderText="ModifiedDate" SortExpression="ModifiedDate" />
                </Columns>
            </asp:GridView>
            <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:AdventureWorksConnectionString %> "
                SelectCommand="select top 10 * from Production.WorkOrder"></asp:SqlDataSource>
        </div>
        <input type="hidden" runat="server" id="hdnRowCount" />
        <input type="hidden" runat="server" id="hdnPostAddedRows" />
    </form>
</body>
</html>

Next, let us revise the problem we're trying to solve here : We are adding new rows to a gridview using client side script. These rows are not accessible on the server side, and there are no events being raised for these new rows on the server side. Our objective is to persist the rows that have been added, to the database. So, the question here is, "How do we access controls added to the page on the client side using Javascript, in our server side (or C# / VB.Net) code ?" To solve this, we are going to create a pipe-delimited string for all the values inserted on client side, and put that string in the hidden variables created above. For e.g., if the following three rows were added to our gridview Saurabh 45 Testing Data Description Swati 89 Some more description TestName 2322 End of new rows We shall convert this into the following string : Saurabh45Testing Data Description~~Swati89Some more description~~TestName2322End of new rows In the above case, the character "" is the column seperator, while the character combination "~~" is the row seperator. Once we put this string in one of the hidden variables created above, and post back the page. Upon postback, on the server side, we can still access the hidden variable, since we declared it with runat="server". We shall read values from the hidden variable, and run an insert query for each row inserted into the gridview. First, in your Page_Load method, populate the number of rows in the gridview in the hidden variable we created above (hdnRowCount)

protected void Page_Load(object sender, EventArgs e)
{
    hdnRowCount.Value = GridView1.Rows.Count;
}

Next, add a button "Save" to the page that triggers the save process :

<asp:Button ID="btnSave" runat="server" OnClick="btnSave_Click" Text="Save" />

Also create a server side event handler btnSave_Click. Next, create a javascript function that will construct the pipe-delimited string we discussed above. Here's a short pseudocode :

{
    1. Get the number of rows originally present in the gridview using the hidden variable hdnRowCount
    2. Create a variable strExpression, and set it to blank ""
    2. Loop through all the newly added rows
    {
        Note : Ignore the first cell, since this is blank.
        3. Read the second cell, append a "" character at the end
        4. Read the third cell, append a "" character at the end
        .
        .
        .
        .
        8. Read the eigth cell, append a "" character at the end
        9. Append a row seperator character, "~~" at the end of the row
        10. Append the string created above to strExpression variable.
    }
    10. strExpression is the string we want. Assign this to the hidden variable "hdnPostAddedRows" so we can access this on the server side.
}

Before we dive into the code, let us ponder over one question. How do we access the values the user has inserted in the table cells. For this, we need to access the individual textboxes in the table cells. We can do this using : table.rows([rowcount]).cells([cell number]).childNodes(0).value This would first access the cell using table.rows([rowcount]).cells([cell number]), and extract the value of the first child of the cell (childNodes(0).value). Again, if one of the columns is a checkbox, you cannot do a .childNodes(0).value to get its value. The reason being, a checkbox does not have a "value" property. Instead, it has the "checked" property. So, to access a checkbox in a table, we need to use : table.rows([rowcount]).cells([cell number]).childNodes(0).checked Let us look at the code now :

            function fn_BuildPostback()
            {
                var rowCount = 0;
                var strExpression = '';
            var table = document.getElementById("GridView1");

    //        1. Get the number of rows originally present in the gridview using the     //        hidden variable hdnRowCount
                var originalRowCount = parseInt(document.getElementById("hdnRowCount").value);
           
            if ( originalRowCount == table.rows.length )
            {
                // No new rows have been added
                return "";
            }
 
    //    2. Loop through all the newly added rows            
                for ( rowCount = originalRowCount - 1; rowCount < table.rows.length ; rowCount++ )
                {
                    var str = '';
                    var operator = '';
                 
                    var tr = table.rows(rowCount);
        // Ignore the first column, move to the second one.
                    str = str + tr.cells(1).childNodes(0).value + '';                
                    str = str + tr.cells(2).childNodes(0).value + '';                
                    str = str + tr.cells(3).childNodes(0).value + '';                
                    str = str + tr.cells(4).childNodes(0).value + '';                
                    str = str + tr.cells(5).childNodes(0).value + '';                
                    str = str + tr.cells(6).childNodes(0).value + '';                
                    str = str + tr.cells(7).childNodes(0).value + '';                
                    str = str + tr.cells(8).childNodes(0).value + '';                

               strExpression = strExpression + str + '~~';
                }

                10. strExpression is the string we want. Assign this to the hidden variable "hdnPostAddedRows" so we can access this on the server side.
                document.getElementById('hdnPostAddedRows').value = strExpression;
            }

The above function just reads the dynamically added rows of the table one by one, and consolidates the contents of the rows into a single, pipe-delimited string. We assign this string to the hidden variable 'hdnPostAddedRows', which is also accessible on the server side in your c# code. The next thing that needs to be done is to call this function when the user clicks the button "btnSave". That is easy, just add the following line at the end of your page_load method : btnSave.Attributes.Add("onclick", "fn_BuildPostback();") If you run the code now, here's the sequence of events : 1. You dynamically add rows to the gridview. Enter some test values, and hit "Save" button. 2. That would create the pipe-delimited string, assign it to the hidden variable, and finally post back the page. 3. Upon postback, the btnSave_Click event handler is called. Inside the event handler, we can write code to split the string into individual rows, and fire insert statements (or stored procedures, as in this case) for each row. Here's a sample code that you might want to build upon :

    protected void btnSave_Click(object sender, EventArgs e)
    {
        string hdnExpr = hdnPostAddedRows.Value;
        int count = 0;
        int originalRowCount = Convert.ToInt32(hdnRowCount.Value.ToString().Trim());
     
        string [] exprArray = hdnExpr.Split("~~".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
        string [] rowArgs;
        SqlConnection conn = new SqlConnection(@"Data Source=wwwddsd331337\SQLExpress;Initial Catalog=TestDB;User Id=sd;Password=xyz;");
        conn.Open();

        foreach (string expression in exprArray)
        {
            // If this is an extra row added on client side
            if (count + 1 > originalRowCount)
            {
                SqlCommand command = new SqlCommand("re_InsertRow", conn);
                command.CommandType = CommandType.StoredProcedure;
                SqlParameter param;
                rowArgs = expression.Split("".ToCharArray());

                param = new SqlParameter("@workorderid", rowArgs[0]);
                param.Direction = ParameterDirection.Input;
                param.DbType = (DbType)SqlDbType.Int;
                command.Parameters.Add(param);

                param = new SqlParameter("@productid", rowArgs[1]);
                param.Direction = ParameterDirection.Input;
                param.DbType = (DbType)SqlDbType.Int;
                command.Parameters.Add(param);

                param = new SqlParameter("@orderqty", rowArgs[2]);
                param.Direction = ParameterDirection.Input;
                param.DbType = (DbType)SqlDbType.VarChar;
                param.Size = 30;
                command.Parameters.Add(param);

                param = new SqlParameter("@stockedqty", rowArgs[3]);
                param.Direction = ParameterDirection.Input;
                param.DbType = (DbType)SqlDbType.VarChar;
                param.Size = 7;
                command.Parameters.Add(param);
            .
            .
            .
            .
            .
            // Add parameters for all columns
                }

                command.ExecuteNonQuery();
            }
            count++;
        }
    // Call a method to bind the gridview again.
        LoadGridData();
    }

That's it !! You've created a gridview with client-side insert functionality. Give yourself a pat on the back, and be prepared for all the good comments ur gonna receive from your website users for making their life easier.

Adding client side insert update delete functionality to a Gridview

2007-11-11T20:02:00.000-08:00

All of us have, at some point or the other, worked with providing Insert / Update / Delete functionality in a datagrid, and you can find quite a few good articles on this topic. Each approach uses some sort of server side code when modifying rows of a datagrid. There is nothing wrong with this as such, but it could become frustrating for the user because of the continous round trips to the server when modifying rows. We have decided to start a series of tutorials explaining the task of building up an client side insert / update / delete gridview.

We have split the operation of dynamically adding rows to a gridview into two phases : 1. Create a javascript function that dynamically adds a new row to the gridview. 2. Create a button on your page that calls this function when clicked.

Simple, huh. To get started, create an aspx page as below, and populate the gridview with records from the AdventureWorks.Production.WorkOrder table (You can use any other database, but then you would have to modify the code below to work with the columns in your database). Please be sure of the following factors when you create the gridview : a) The query for your SQLDataSource should be the following : "select top 10 * from Production.WorkOrder" This will limit the number of rows returned, and save you time unnecessarily waiting for the page to load, in addition to putting less burden on your database. b) Disable paging for the gridview, so that we do not have a pager at the bottom of our gridview. c) Make sure that you are not using master pages, or the gridview is not part of a user control. This is because when a control is placed inside a parent control, user control, or a master page, it's HTML id is prepended with some string identifying the parent. More on this in a later post. If you do not know how to do this, please refer to this documentation on msdn to make sure the above simple conditions are met. Note that we have also added an html button named "btnAddRow" with runat = "server" set. Once done, here's what our page looks like.

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default2" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <input type="button" id="btnAddRow" value="Add" runat="server" />
    <div>
        <asp:GridView ID="myGridView" runat="server" DataSourceID="SqlDataSource1">
            <Columns>
                <asp:TemplateField>
                    <HeaderTemplate>
                        Modify
                    </HeaderTemplate>
                </asp:TemplateField>
            </Columns>
        </asp:GridView>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:AdventureWorksConnectionString %>"
            SelectCommand="SELECT top 10 * FROM [Production].[WorkOrder]"></asp:SqlDataSource>
    </div>
    </div>
    </form>
</body>
</html>

Test the page by running it once, the output should show 10 rows from the AdventureWorks.Production.WorkOrder database table.

Note that I have added a template column as the first column, so that the first column always shows empty when the gridview is rendered on browser. This column has been intentionally left blank so we can show the insert / update / delete buttons.

Next, take a moment to inspect the HTML code of your application. You can do this by right clicking on your page and choosing "View Source". Our gridview now looks like this (text clipped for brevity) :

<div>
    <table cellspacing="0" rules="all" border="1" id="myGridView" style="border-collapse:collapse;">
        <tr>
            <th scope="col">
                        Modify
                    </th><th scope="col">WorkOrderID</th><th scope="col">ProductID</th><th scope="col">OrderQty</th><th scope="col">StockedQty</th><th scope="col">ScrappedQty</th><th scope="col">StartDate</th><th scope="col">EndDate</th><th scope="col">DueDate</th><th scope="col">ScrapReasonID</th><th scope="col">ModifiedDate</th>
        </tr><tr>
            <td>&nbsp;</td><td>1</td><td>722</td><td>8</td><td>8</td><td>0</td><td>7/4/2001 12:00:00 AM</td><td>7/14/2001 12:00:00 AM</td><td>7/15/2001 12:00:00 AM</td><td>&nbsp;</td><td>7/14/2001 12:00:00 AM</td>
    </table>
</div>

This reiterates the fact that our gridview is actually rendered as an html table in the user's browser. It also means that if we want to dynamically add some rows to the gridview, we are actually adding rows to an HTML table. Our goal is that when the user clicks on the "Add" button that we created in our aspx page, a new row should be dynamically added to the gridview. For this, I have created a short pseudocode which we could base our code upon.

{
    // 1. Get the <table> element corresponding to the gridview from the page's DOM.

    // 2. Insert a new row in the table fetched in step 1.

    // 3. Insert an empty cell for the first "Modify" column

    // 4. Insert an empty cell for "WorkOrderID" in the row created in step 2. above.
        // In the cell created above, add a textbox in which the user will input the workorderID value he wishes to insert.
    // 5. Insert an empty cell for ProductID in the row created above.
        // In the cell created above, add a textbox in which the user will input the ProductID value he wishes to insert.
    // 6. Insert an empty cell for OrderQty in the row created above.
        // In the cell created above, add a textbox in which the user will input the OrderQty value he wishes to insert.
    .
    .
    .
    .
    // N. Insert cells similar to the above for the remaining columns (StockedQty,ScrappedQty,StartDate, EndDate, DueDate, ScrapReasonID, ModifiedDate)
}

So, basically we're getting the table object from the DOM, and adding a new row and new cells dynamically using javascript. Before you look at the code, recall that you can add a child element to a parent element using the parentElement.appendChild(childElement) method. But if you are adding a new table row (child), to a table (parent), you can directly use the tableName.insertRow() method. Similarly, to add a new table cell to a row, use the tableRow.insertCell() method. Let us see the code now :

            function fn_AddFilterRow()
            {
        // 1. Get the <table> element corresponding to the gridview from the document
            var table = document.getElementById('myGridView');

        // 2. Insert a new row in the table fetched in step 1.
                var newRow = table.insertRow();

        // 3. Insert an empty cell for the first "Modify" column
                // Column 1 : An empty cell for the "Modify" column
                var newCell = newRow.insertCell();
                newCell.appendChild(btnDelete);

        // 4. Insert an empty cell for "WorkOrderID" in the row created in step 2. above.                                    
                // Column 1 : WorkOrderID
                newCell = newRow.insertCell();
        // In the cell created above, add a textbox in which the user will input the  workorderID value he wishes to insert.
            var newTextBox = document.createElement('input');
            newTextBox.type = 'text';
            newCell.appendChild(newTextBox);
    
        // 5. Insert an empty cell for ProductID in the row created above.
                // Column 2 : ProductID 
                newCell = newRow.insertCell();
        // In the cell created above, add a textbox in which the user will input the ProductID value he wishes to insert.
            var newTextBox = document.createElement('input');
            newTextBox.type = 'text';
            newCell.appendChild(newTextBox);

        // 6. Insert an empty cell for OrderQty in the row created above.                                
                // Column3 : OrderQty
                newCell = newRow.insertCell();
        // In the cell created above, add a textbox in which the user will input the OrderQty value he wishes to insert.
            var newTextBox = document.createElement('input');
            newTextBox.type = 'text';
            newCell.appendChild(newTextBox);
        .
        .
        .
        .
        .
        .
        .
        // Do the same for the remaining columns.

        }

The last step here is to write some code so that when the button "btnAddRow" gets clicked , our javascript function fn_AddFilterRow gets called. This is easy. Go to your code behind page. In the Page_Load method, add the following line

 btnAddRow.Attributes.Add("onclick", "fn_AddFilterRow();");

That's it. Let it rip, and see for yourself. Launch the page in IE, and click on the button to add a new row to the gridview.

Note that each time you click on the button, you'd get a new row added to the gridview, which is pretty cool if users want to add multiple rows at a time.

Okay, that's half the battle won. The other half is accessing the contents of the newly inserted row on the server side, and eventually inserting the row into the database. We shall discuss that in our next post.

Just so you know, the above is a very basic implementation of the client side editing capability. Notably, you might want to experiment a little bit to fix the following flaws 1. The new row always comes in as a standard with no formatting applied. But if the table has some formatting applied, your new row should also have the same color, font size, alignment etc. as the other gridview rows. 2. Currently we're adding textboxes for all columns in the new row. But there may be situations where you would want the user to choose from a dropdown list, or a checkbox (e.g. a column for sex can have values Male / Female / Yes, Please ;) ). Our gridview does not handle this. 3. If there was a footer/pager present, the code would have to be adjusted. The reason being the new row is always added after the last row of the <table&gr;. The new row should ideally be added just above the footer, not below it. 4. Code does not handle the situation where the gridview is empty, i.e. no rows are being displayed.

Nevertheless, this is a good starting point and you can definitely build up on the fundamentals you learnt here. In the next post, we'll complete the circle by persisting the row entries we added here to the database.

How to add HTML controls / elements dynamically to a page

2007-11-07T21:20:00.000-08:00

How to add HTML controls / elements dynamically to a page ------------------------------------------------------------------------------------------------ Hmm .... now there's a familiar javascript problem. You know what javascript is ... know how it works ... know that you can manipulate controls, but how do you actually add new elements to the page using JS. To revise our principles and beliefs -- The javascript hierarchy looks like this : Document --> HTML --> Body --> Page Elements. The above expression omits one essential fact though - "Controls on a page can be children of other controls on the page". Confused ? Let's look at an example :

<html>
<head>
<title>Untitled Page</title>
</head>
<body>
<div id="myDiv">
<input type="button" id="btn" value="Click Me" />
</div>
</body>
</html>

In the above html, the <input> element with id = "btn", is a CHILD OF the <div> element.Getting the hang of it ??! Let's fudge up things a little bit

<html>
<head>
<title>Untitled Page</title>
</head>
<body>
<div id="myDiv">
<input type="button" id="btn" value="Click Me" />
<div id="childDiv">
<h id="childH"> Child </h>
</div>
</div>
</body>
</html>

Again, the hierarchy we have is : Meaning, myDiv element has two children - btn, and childDiv. In turn, childDiv has one child of its own, called childH. Now do you get the hang of it ??!!. If not, look at my earlier post on debugging javascript, and try debugging this piece of HTML script. Pay close attention to the "children" properties of the elements. Please note that the parent-child relationship determines how elements are displayed on the page. In the above case, the button "myBtn" will be shown "inside" the myDiv element's area on screen. Again, the childH element will be shown inside the childDiv area, which in turn falls inside the area for myDiv, which in turn falls in the area for <body> . When an element is child of the <body> element, it is not shown as a part of any other control, because <body> tag stands for the entire browser area. More on this in a later post. All right, now about adding elements dynamically. There are roughly two situations where you would be adding an element to an html page : 1) Adding an element directly to the page's <body>. 2) Adding an element as a child of some other element. Let us look at case # 1 first - Adding an element to the <body> tag. Theoretically, this is the same as the case # 2, with the only difference that the element that you add is a child of the <body> element. First, we need to define an event. An event is (usually) some kind of action that the user will perform on the browser while viewing a web page. The action can be a button click, or moving the mouse or even typing something in a textbox. The reason we're associating an event here is to allow the user to trigger the process of adding a control dynamically. Meaning, the javascript that dynamically adds the controls to the page should be called only when the user performs that action.

<html>
<head>
    <title>Untitled Page</title>
</head>
<body>
    <div id="myDiv">
        <input type="button" id="btn" value="Click Me" />
        <div id="childDiv">
            <h id="childH"> Child </h>
            <input type="button" id="addCtrl" value="Add a new Elt" onclick="var elt = document.getElementById('childDiv');
var newElt = document.createElement('LABEL');
newElt.innerText = 'I was just added';
elt.appendChild(newElt);" />
        </div>
    </div>
</body>
</html>

Ok, so here we go. First, look at the line beginning with "onclick". This is like telling the browser, "Whenever the user does a click on this button (that is, clicks the button), run this piece of javascript". Simple, huh. And lets dissect the javascript -- var elt = document.getElementById('childDiv'); You should be familiar with this. If not, look at my post on javascript debugging. This is fetching the object for childDiv, and assigning to the variable 'elt'. Next up, var newElt = document.createElement('LABEL'); Here, we are creating a new element of type 'label'. Usually, when you need to display some plain text on the screen, we use this type of element. The new element is now stored in the 'newElt' variable. Next, newElt.innerText = 'I was just added'; This is basically setting a property for that control, so that when the object is shown on the browser, it writes "I was just added" on screen, and we know that it exists. elt.appendChild(newElt); Now this is the most critical part. We are adding the element we created above using the 'createElement' method, to the element that elt refers to. Meaning, we are adding the newly created element 'newElt' to the 'childDiv' element. Once done, the hierarchy would look like this : You can verify this by running the page. Initially you will not see any label text. As soon as you click the button, the text "I was just added" will appear. I would also advise that you look at what is happening by debugging the javascript. In case you are not familiar with debugging javascript, you can refer to my earlier post. Hmm ... that was fun and easy. Next time, we will look at a related problem - dynamically building html tables. Keep learning.

To add value in Hashtable from SQLReader

2007-11-07T16:33:00.000-08:00

If you want to add the values to HashTable which you are reading through SQLDataReader , then this is what you have to write, Hashtable myHash =new Hashtable(); while(reader.Read()) { string type = reader.GetString(0).Trim(); int num = reader.GetInt32(1).Trim(); myhash.Add(num,type); } Now if you want to add more than one values to Hashtable while reading it from a SQLDataReader, you will have to put the values in a class and make an object of it to insert in the value for Hashtable. It goes like this : public class myClass { private string sType; private int sNumber; private Datetime stimeType; public myClass(string Type,int Number,Datetime timeType) { sType = Type; sNumber = Number; stimeType = timeType; } public string getType() { return sType; } public int getNumber() { return sNumber; } public Datetime gettimeType() { return stimeType; } } public void readVal() { Hashtable myHash =new Hashtable(); while(reader.Read()) { string type = reader.GetString(0).Trim(); int num = reader.GetInt32(1).Trim(); Datetime time= reader.GetDateTime(2); myClass myObj = new myClass(type,num,time); myhash.Add(num,myObj); } }

How to debug the javascript DOM [Part 2]

2007-11-06T19:20:00.000-08:00

Well, let me summarize what we understood until now

A] Whatever you see on the browser (including this page), is all pure HTML

B] When the browser "draws" the HTML as buttons, textboxes, colors etc., it also creates a hierarchy of objects called the DOM model.

C] The elements are in this order : HTML --> BODY --> Other Elements

You might be saying "All right, I get it ... but is there a way I can physically see this hierarchy ??". The answer is Yes. But, you should have either Visual Studio or Microsoft Script Editor installed on you machine. Let us see how.

First, create a simple test page called HelloWorld.html. Here's the text

<html> <head> <title>Untitled Page</title> </head> <body> <div id="myDiv"> <input type="button" id="btn" value="Click Me"> </div> </body> </html>

Type the above in a simple notepad and save it as HelloWorld.html. Next, open this page in internet explorer by double clicking on it. You'll see something like this : For debugging purposes, we would have to add some javascript to this page. Let's tweak the HTML slightly :

<html> <head> <title>Untitled Page</title> </head> <body onload="var theDiv = document.getElementById('myDiv');"> <div id="myDiv"> <input type="button" id="btn" value="Click Me"> </div> </body> </html>

As you can see, I have added some javascript to the page (now you know what it looks like). Let's dissect it a little bit before diving into how to debug it. <body onload="XXXXXX" .....

The above line is like effectively telling the browser that "When you start drawing this <body> element, that is, when you "load" this body element, before doing anything else, execute the statements given by XXXXXXX". So, as soon as the browser starts drawing the <body> element on the page, it stops the drawing and first starts executing the javascript. Let's move to the Javascript now. var theDiv = document.getElementById('myDiv'); var -- whenever you create a variable in javascript, the type has to be var only. No matter if it's an int, float, date, even an object, the Type is always VAR. So we declare a variable called "theDiv". Before you wonder what document.getElementById(...) means, let me remind you the hierarchy. HTML --> BODY --> Other Elements

Keep in mind all the page elements (including HTML, and BODY), are part of the page's document. Thats one of the reasons the DOM stands for Document Object Model. So effectively, here's the object hierarchy diagram for any HTML page : So what document.getElementById('myDiv') is doing is searching for an element called "myDiv" in the document object's children. That is gonna return the <div> element that we created in our HTML, and this value is assigned to the variable theDiv.

Whew, so far so good. Now let's get our hands dirty with some debugging stuff. Open the page in a browser, go to Tools -- > Internet Options --> Advanced --> Browsing section. Uncheck the "Disable Script Debugging (Internet Explorer)" and "Disable Script Debugging (Other)" checkboxes. Next, go to your page, from menu choose view --> Script Debugger --> Break at next statement Once done, refresh the page. Immediately you should see the a window similar to the one below :

Choose "Yes", a new session of Visual Studio / Script debugger will open. Once open, you'll see a window like this :

The yellow arrow on the left tells you on which line the debugger is currently at. --Select the text "document.getElementById('myDiv') as shown above. --Right click on the text and choose "Add Watch". The add watch command is used to add the chosen expression to a Watch window, where you can actually see what it's value is. As the program runs, you can see the value change on the fly. On choosing "Add Watch", you'll see a window like this (it usually pops up at the bottom of the Visual Script Debugger window) :

Expand the selection by clicking on the [+] symbol, and voila, you're looking at the internals of the "myDiv" object. Remember, this is actually the Object Representation of the page's myDiv element. If you change this object, the page myDiv will also change, and vice versa.

Let's make sense of some of the more important properties -->

-- id : The id of the element (in our case, "myDiv")

-- tagName : The TYPE of the element, whether it's a <div> or an <input> element, or is it <document>, or <body> ... get the point ?!!

-- parentElement : The parent of the current element. In our case, myDiv is a child of the <body> element object (remember the hierarchy ?). So the parentElement should be the document. To confirm, expand the parentElement by clicking the [+] sign, and check out the tagName property of the parent Element. Sure enough, it says "BODY".

Before going further, scroll down to the "document" element and expand it. Here's what you see : This is the page's document object, which houses all other page element object (including <html>, <body> etc. If you look carefully, you'll see it also contains Scripts, images, links, frames etc. page elements. More on this later.

Next up, OffsetLeft, offsetTop, offsetWidth, offsetHeight : Determine the position of the element on the page.

InnerHTML : The html inside this element

InnerText : If you wanna write some text in an element, you use this.

.
.
.
.

There's a hundred other properties that I'd like to go through, but let's stop here for lack of space and time. For more info, you can always checkout the description for <div> element on the MSDN site http://msdn2.microsoft.com/en-us/library/ms535240.aspx.

To cut a long story short, if you want to look at the internals of the various objects on an HTML page, you can do so by starting to debug the page and looking at various page objects. In my next post, I'll look at the internals of some of the most common operations that you need to perform in javascript while creating websites.

To retrieve fields using SQLReader

2007-11-06T09:51:00.000-08:00

Whenever the fields needs to be retrieved from SQL Database, The smartest way is to do it when there is lot of data to retrieve is through the SQLDataReader. The code can look like this :--

private SqlConnection sConn; 
private SqlCommand slSelectSumDClosedCommand; 
slSelectSumDClosedCommand = new SqlCommand(); 
slSelectSumDClosedCommand.CommandText = "Select * from table1"; 
slSelectSumDClosedCommand.Connection = sConn; 
private SqlDataReader reader=null; 
private Hashtable sDClosedValue = new Hashtable(); 

reader = slSelectSumDClosedCommand.ExecuteReader(); 
while(reader.Read()) 
{ 
string acct = reader.GetString(0).Trim(); 
double dailyVal = reader.GetDouble(1); 
sDClosedValue.Add(acct, dailyVal); 
} 
reader.Close();

The above code is reading values from the table through data reader and putting it in an hashtable. You can carry out whatever function is required. Try using stored procedure everytime. Above code is just an example that is why I have used SQLCommand.CommandText.

How to connect with SQL database?

2007-11-04T17:31:00.000-08:00

For connecting to SQL Database, you can directly write a connection string in the C# code like
SQLConnection sConn = new SQLConnection();
sConn="Data Source=xyz;Initial Catalog=myDtbase;User ID=sl;Password=sa"
But the proper way to do it is by adding the connection string in a web.config, for example in
<configuration>
<appSettings>
<add key="conn1String" value="Data Source=xyz;Initial Catalog=myDtbase;User ID=sl;Password=sa"/> </appSettings>
</configuration> <appsettings><appsettings>
<add key="conn1String" value="Data Source=xyz;Initial Catalog=myDtbase;User ID=sl;Password=sa">
</appsettings>
<add key="conn1String" value="Data Source=xyz;Initial Catalog=myDtbase;User ID=sl;Password=sa">
</configuration></appsettings></configuration>
In C# code , just define
string connString = ConfigurationSettings.AppSettings["conn1String"];
sConn =connString;
Data Source : has to be the complete machine name\SQLExpress
Initial Catalog : has to be the database name
User ID and Password : If you use other than windows authentication If it is windows authentication, just give Integrated Security=SSPI;

Why to use bracket '[' around table name and column name?

2007-11-04T07:22:00.000-08:00

The error : Incorrect syntax near the keyword tablename(before the space) will appear if tried to use a tablename with space in between. We can use square bracket '[' around table name or column name, for generally specifying a table name with spaces. For e.g. Select * from my table ---> this statement will not work as tablename 'my table' has a space. Either you can write , Select * from 'my table' OR Select * from [my table] When sometimes we have defined Schema names for database and they contain some '.' , then we can write Select * from [s.ssd].[my table] For using a particular database, dont forget to type use databaseName Go then your statement

Beginning SQL Statements

2007-11-04T06:22:00.000-08:00

Whenever anybody wants to start learning SQL Server , there are few basic commands which needs to be known,

SQL Select Statement -- Select statement is used for selecting any number of rows from a table. You can specify the 'where' clause and retrieve number of rows selectively. Select * from table1 where column1 = 'name' and column2 = 'ID'
SQL Insert Statement -- Insert Statement is used for inserting rows in a table . Here also 'where' clause can be specified as required. Insert into table1(column1,column2,column3) Values('value1','value2','value3')
SQL Delete Statement -- Delete Statement is for deleting any number of rows based on certain clause. Delete from table1 where column1='value1' and column2='value2'
SQL Update Statement -- Update Statement , updates a particular row as required. Update from table1 set column1 = 'value1'

These are the 4 important statements to know for any beginner . We will keep on posting blogs regarding different ways of writting these statements.