Like any other question I am facing problem whenever I am trying to UPDATE my DataGridView through UI. Here is the error I am getting:
SQLException was unhandled. String or binary data would be truncated. The statement has been terminated.
Now here is my column list which I am trying to update:
-
ShipTrackingNumber (nvarchar(100), null)
-
ShipMethodTransmitted (nvarchar(50), null)
-
DateShipTransmitProcessed (datetime, null)
-
ShipmentProcessedBy (nvarchar(50), null)
-
Critical (nchar(1), null)
-
ShipTransmitStatus (nvarchar(50), null)
Now you got the columns listings and their properties. Here is the code which updates these columns:
public void SaveDataSet(DataTable table)
{
foreach (DataRow row in table.Rows)
{
SqlCommand cmd2 = new SqlCommand(
"update dbo.JobStatus SET ShipTrackingNumber = '@trackingNumber', ShipMethodTransmitted = '@TransmitMethod', DateShipTransmitProcessed = @DateProcessed, ShipmentProcessedBy = '@ProcessedBy', Critical = '@Critical', ShipTransmitStatus = '@TransmitStatus' WHERE JobTableId = @JobTableId ", _mySqlConnec);
//Updated the parameters to the SQL Query!
cmd2.Parameters.Add(new SqlParameter("@trackingNumber", row["Tracking#"].ToString()));
cmd2.Parameters.Add(new SqlParameter("@TransmitMethod", row["TransmitMethod"].ToString()));
cmd2.Parameters.Add(new SqlParameter("@DateProcessed", row["DateProcessed"]));
cmd2.Parameters.Add(new SqlParameter("@ProcessedBy", row["ProcesssedBy"].ToString()));
cmd2.Parameters.Add(new SqlParameter("@Critical", row["Critical"].ToString()));
cmd2.Parameters.Add(new SqlParameter("@TransmitStatus", row["Status"].ToString()));
cmd2.Parameters.Add(new SqlParameter("@JobTableId", row["JobID"].ToString()));
cmd2.Connection = _mySqlConnec;
_mySqlConnec.Open();
cmd2.ExecuteNonQuery();
_mySqlConnec.Close();
}
}
}
Can anyone please help me in this regard. Thank You!
Best Answer
You do not need to have quotes around any of your paramters.
change your sql to:
It is basically trying to insert the string "@Critical" into a nchar(1) which is causing the truncation error.