CODEWITHSUNDEEP
c#sql
Callum A Macleod
Callum A Macleod

Reputation: 165

Updating SQL table with the contents of a datatable

I am creating an application in WPF using C# where users populate a datagrid and the information is then stored in a DataTable called smb1. The following code works for inserting the data into the SQL database but when I modify the code for updating it does not work. Does anyone know how I can modify my code to allow Updates as there are no errors thrown when I run my application in Visual Studio. I must add that the Equipment column cannot be edited in the datagrid so the returned data is the same as the data taken from the database so that the updated rows will be matched to the equipment rows in the SQL database using the WHERE clause. Below is the original insertion code plus my attempt for updating the database.

Insert Code

SqlConnection con = new SqlConnection(MyConnectionString);
string SqlCmdText = "Insert into SHIFTLOG Values(@EQUIPMENT,@BATCHNO,@PRODUCTNO,@STATUS,@DATE,@PERIOD,@MACHINE)";
SqlCommand sc = new SqlCommand(SqlCmdText, con);
con.Open();
foreach (DataRow row in smb1.Rows)
{
    sc.Parameters.Clear();
    sc.Parameters.AddWithValue("@EQUIPMENT", row["EQUIPMENT"]);
    sc.Parameters.AddWithValue("@BATCHNO", row["BATCHNO"]);
    sc.Parameters.AddWithValue("@PRODUCTNO", row["PRODUCTNO"]);
    sc.Parameters.AddWithValue("@STATUS", row["STATUS"]);
    sc.Parameters.AddWithValue("@DATE", DateTime.Now.ToString("yyyy-MM-dd"));
    sc.Parameters.AddWithValue("@PERIOD", DateTime.Now.ToString("tt"));
    sc.Parameters.AddWithValue("@MACHINE", "SMB1");

    sc.ExecuteNonQuery();
}
con.Close();

Attempt for Update Code

SqlConnection con = new SqlConnection(MyConnectionString);
string SqlCmdText = "UPDATE SHIFTLOG SET EQUIPMENT='@EQUIPMENT',BATCHNO='@BATCHNO',PRODUCTNO='@PRODUCTNO',STATUS='@STATUS',DATE='2013-09-12',PERIOD='@PERIOD',MACHINE='@MACHINE' WHERE EQUIPMENT='@EQUIPMENT'";
SqlCommand sc = new SqlCommand(SqlCmdText, con);
con.Open();
foreach (DataRow row in smb1.Rows)
{
    sc.Parameters.Clear();
    sc.Parameters.AddWithValue("@EQUIPMENT", row["EQUIPMENT"]);
    sc.Parameters.AddWithValue("@BATCHNO", row["BATCHNO"]);
    sc.Parameters.AddWithValue("@PRODUCTNO", row["PRODUCTNO"]);
    sc.Parameters.AddWithValue("@STATUS", row["STATUS"]);                
    sc.Parameters.AddWithValue("@PERIOD", DateTime.Now.ToString("tt"));
    sc.Parameters.AddWithValue("@MACHINE", row["MACHINE"]);

    sc.ExecuteNonQuery();
}
con.Close();

Thanks for any help.

Upvotes: 3

Views: 2443

Answers (2)

Damith
Damith

Reputation: 63065

remove the '' in all the parameters in your sql statement

"UPDATE SHIFTLOG SET BATCHNO=@BATCHNO,....... WHERE EQUIPMENT=@EQUIPMENT

if you use quotes all your parameters take as string values, not as SQL parameters

And also you need to use columns with [] like [DATE] if those are reserved keywords

Upvotes: 3

Adriaan Stander
Adriaan Stander

Reputation: 166346

Remove the single quotes around the parameters.

string SqlCmdText = "UPDATE SHIFTLOG SET EQUIPMENT=@EQUIPMENT,BATCHNO=@BATCHNO,PRODUCTNO=@PRODUCTNO,STATUS=@STATUS,DATE='2013-09-12',PERIOD=@PERIOD,MACHINE=@MACHINE WHERE EQUIPMENT=@EQUIPMENT";

Also, I think the update section for EQUIPMENT=@EQUIPMENT is redundant, as the where clause will not be correct if it has changed. So you could use

string SqlCmdText = "UPDATE SHIFTLOG SET BATCHNO=@BATCHNO,PRODUCTNO=@PRODUCTNO,STATUS=@STATUS,DATE='2013-09-12',PERIOD=@PERIOD,MACHINE=@MACHINE WHERE EQUIPMENT=@EQUIPMENT";

Upvotes: 5

Related Questions