Solved

Insert on each row

Posted on 2011-03-02
3
272 Views
Last Modified: 2012-08-14
I want to insert a timestamp on each row that is generated in Gridview, Right now I get the date in a separate row,   Can someone tell me where to move the code ? or how can I fix rthis?
private void InsertRecords(StringCollection sc)
    {
        SqlConnection conn = new SqlConnection(GetConnectionString());
        StringBuilder sb = new StringBuilder(string.Empty);
        DateTime datevar = DateTime.Parse(dateLabel.Text.ToString());
        string[] splitItems = null;
                
      
        string sqlStatement2 = "INSERT INTO SampleTable (Date) values('" + datevar + "')";



        foreach (string item in sc)
        {
            //string sqlStatement2 = "INSERT INTO SampleTable (Date) values('" + datevar + "')";
            const string sqlStatement = "INSERT INTO SampleTable (NT1,NT1_Status,NT2,NT2_Status,NT3,NT3_Status,ICU,ICU_Status,SDU,SDU_Status,Tele3rd1,Tele3rd1_Status,Tele3rd2,Tele3rd2_Status,Tele4th1,Tele4th1_Status,Tele4th2,Tele4th2_Status,Pedi3rd,Pedi3rd_Status,Pedi4th,Pedi4th_Status,PICU_SD,PICU_SD_Status,ER,ER_Status,MICU,MICU_Status,CPV,CPV_Status,CVOR,CVOR_Status,CLOF,CLOF_Status,CVOR_HOLDING,CVOR_HOLDING_Status) VALUES";
            if (item.Contains(","))
            {
                splitItems = item.Split(",".ToCharArray());
           
                sb.AppendFormat("{0}('{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}','{21}','{22}','{23}','{24}','{25}','{26}','{27}','{28}','{29}','{30}','{31}','{32}','{33}','{34}','{35}','{36}'); ", sqlStatement, splitItems[0], splitItems[1], splitItems[2], splitItems[3], splitItems[4], splitItems[5], splitItems[6], splitItems[7], splitItems[8], splitItems[9], splitItems[10], splitItems[11], splitItems[12], splitItems[13], splitItems[14], splitItems[15], splitItems[16], splitItems[17], splitItems[18], splitItems[19], splitItems[20], splitItems[21], splitItems[22], splitItems[23], splitItems[24], splitItems[25], splitItems[26], splitItems[27], splitItems[28], splitItems[29], splitItems[30], splitItems[31], splitItems[32], splitItems[33], splitItems[34], splitItems[35]);
           
            }
            


        }
             
        
        try
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand(sb.ToString(), conn);
            SqlCommand cmd2 = new SqlCommand(sqlStatement2, conn);

            cmd.CommandType = CommandType.Text;
            cmd.ExecuteNonQuery();

            cmd.CommandType = CommandType.Text;
            cmd2.ExecuteNonQuery();


            Page.ClientScript.RegisterClientScriptBlock(typeof(Page), "Script", "alert('Records Successfuly Saved');", true);

        }
        catch (System.Data.SqlClient.SqlException ex)
        {
            string msg = "Insert Error:";
            msg += ex.Message;
            throw new Exception(msg);

        }
        finally
        {
            conn.Close();
        }
    }

Open in new window

0
Comment
Question by:TonyReba
  • 2
3 Comments
 
LVL 12

Expert Comment

by:HugoHiasl
Comment Utility
One way would be changing it to :

            const string sqlStatement = "INSERT INTO SampleTable (Date, NT1,NT1_Status,NT2,NT2_Status,NT3,NT3_Status,ICU,ICU_Status,SDU,SDU_Status,Tele3rd1,Tele3rd1_Status,Tele3rd2,Tele3rd2_Status,Tele4th1,Tele4th1_Status,Tele4th2,Tele4th2_Status,Pedi3rd,Pedi3rd_Status,Pedi4th,Pedi4th_Status,PICU_SD,PICU_SD_Status,ER,ER_Status,MICU,MICU_Status,CPV,CPV_Status,CVOR,CVOR_Status,CLOF,CLOF_Status,CVOR_HOLDING,CVOR_HOLDING_Status) VALUES( '" +DateVar+"'";

            if (item.Contains(","))
            {
                splitItems = item.Split(",".ToCharArray());
           
                sb.AppendFormat("{0}'{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}','{21}','{22}','{23}','{24}','{25}','{26}','{27}','{28}','{29}','{30}','{31}','{32}','{33}','{34}','{35}','{36}'); ", sqlStatement, splitItems[0], splitItems[1], splitItems[2], splitItems[3], splitItems[4], splitItems[5], splitItems[6], splitItems[7], splitItems[8], splitItems[9], splitItems[10], splitItems[11], splitItems[12], splitItems[13], splitItems[14], splitItems[15], splitItems[16], splitItems[17], splitItems[18], splitItems[19], splitItems[20], splitItems[21], splitItems[22], splitItems[23], splitItems[24], splitItems[25], splitItems[26], splitItems[27], splitItems[28], splitItems[29], splitItems[30], splitItems[31], splitItems[32], splitItems[33], splitItems[34], splitItems[35]);
           
            }

Open in new window


Additionally you need to comment out or remove this line:

cmd2.ExecuteNonQuery();

The line above this line is wrong. I assume you did not want to set the commandtype for the cmd twice but set it once for cmd and once for cmd2


But the complete structure looks weird...
0
 
LVL 9

Author Comment

by:TonyReba
Comment Utility
Hi hugo , I am using your suggestion, and i get a error saying sqlStatement must be a constant, do yiou know what could be wrong?

private void InsertRecords(StringCollection sc)
    {
        SqlConnection conn = new SqlConnection(GetConnectionString());
        StringBuilder sb = new StringBuilder(string.Empty);
        DateTime datevar = DateTime.Parse(dateLabel.Text.ToString());
        string[] splitItems = null;
                
      
        //string sqlStatement2 = "INSERT INTO SampleTable (Date) values('" + datevar + "')";



        foreach (string item in sc)
        {
            const string sqlStatement = "INSERT INTO SampleTable (Date, NT1,NT1_Status,NT2,NT2_Status,NT3,NT3_Status,ICU,ICU_Status,SDU,SDU_Status,Tele3rd1,Tele3rd1_Status,Tele3rd2,Tele3rd2_Status,Tele4th1,Tele4th1_Status,Tele4th2,Tele4th2_Status,Pedi3rd,Pedi3rd_Status,Pedi4th,Pedi4th_Status,PICU_SD,PICU_SD_Status,ER,ER_Status,MICU,MICU_Status,CPV,CPV_Status,CVOR,CVOR_Status,CLOF,CLOF_Status,CVOR_HOLDING,CVOR_HOLDING_Status) VALUES( '" + datevar + "'";

Open in new window

0
 
LVL 12

Accepted Solution

by:
HugoHiasl earned 500 total points
Comment Utility
There are two problems.

I missed a comma at the end of the sqlStatement.

And... this is the error... you are defining sqlStatement as constant which does not allow the variable part of the datevar.

remove the word const in this line:

string sqlStatement = "INSERT INTO SampleTable (Date, NT1,NT1_Status,NT2,NT2_Status,NT3,NT3_Status,ICU,ICU_Status,SDU,SDU_Status,Tele3rd1,Tele3rd1_Status,Tele3rd2,Tele3rd2_Status,Tele4th1,Tele4th1_Status,Tele4th2,Tele4th2_Status,Pedi3rd,Pedi3rd_Status,Pedi4th,Pedi4th_Status,PICU_SD,PICU_SD_Status,ER,ER_Status,MICU,MICU_Status,CPV,CPV_Status,CVOR,CVOR_Status,CLOF,CLOF_Status,CVOR_HOLDING,CVOR_HOLDING_Status) VALUES( '" + datevar + "',";
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

This article introduced a TextBox that supports transparent background.   Introduction TextBox is the most widely used control component in GUI design. Most GUI controls do not support transparent background and more or less do not have the…
A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now