Insert on each row
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();
}
}ASKER
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 + "'";ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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...