CODEWITHSUNDEEP
sql
RG-3
RG-3

Reputation: 6188

UPDATE Query : Incorrect Syntax (Edited)

I have a button which saves the contents edited in datagridview in my UI design. Here is the code for that Button_Save:

    public void btnUpdate_Click(object sender, EventArgs e)
    {

        foreach (DataGridViewRow row in dataGridView1.Rows)
        {
                 System.Data.SqlClient.SqlConnection sqlConnection1 =
                 new System.Data.SqlClient.SqlConnection("server=Test; Integrated Security=true; Database=Test;");

                System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
 var sql = new StringBuilder();
                sql.AppendLine("UPDATE dbo.JobStatus");
                sql.AppendLine("Set ShipTrackingNumber = @ShipTrackingNumber");
                sql.AppendLine(", ShipMethodTransmitted = @ShipMethodTransmitted");
                sql.AppendLine(", DateShipTransmitProcessed = @DateShipTransmitProcessed");
                sql.AppendLine(", ShipmentProcessedBy = @ShipmentProcessedBy");
                sql.AppendLine(", Critical = @Critical");
                sql.AppendLine(", ShipTransmitStatus = @ShipTransmitStatus");
                sql.AppendLine("Where jobtableId = @jobTableId");

                cmd.Connection = sqlConnection1;
                cmd.CommandText = sql.ToString();
                cmd.Parameters.AddWithValue("@TrackingNumber", row.Cells[7].FormattedValue);
                cmd.Parameters.AddWithValue("@ShipMethodTransmitted", row.Cells[8].FormattedValue);

                cmd.Parameters.AddWithValue("@DateShipTransmitProcessed", row.Cells[9].FormattedValue);
                cmd.Parameters.AddWithValue("@ShipmentProcessedBy", row.Cells[10].FormattedValue);
                cmd.Parameters.AddWithValue("@Critical", row.Cells[11].FormattedValue);
                cmd.Parameters.AddWithValue("@ShipTransmitStatus", row.Cells[13].FormattedValue);
                cmd.Parameters.AddWithValue("@jobTableId", row.Cells[5].FormattedValue);


                sqlConnection1.Open();
                cmd.ExecuteNonQuery();
                sqlConnection1.Close();

What I am getting error is that: "Must declare the scalar variable "@ShipTrackingNumber". I dont want to put ShipTrackingNumber from the code. Instead I want to fetch it from the UI.

What I am doing wrong here?

Upvotes: 0

Views: 194

Answers (3)

Eric K Yung
Eric K Yung

Reputation: 1784

You need to replace this line:

cmd.Parameters.AddWithValue("@TrackingNumber", row.Cells[7].FormattedValue);

with this line:

cmd.Parameters.AddWithValue("@ShipTrackingNumber", row.Cells[7].FormattedValue);

Upvotes: 0

p.campbell
p.campbell

Reputation: 100567

To answer your direct question, add the parameter with its value taken from the UI. Let's say it's a checkbox named CheckBox1:

cmd.Parameters.AddWithValue("@ShipTrackingNumber", CheckBox1.Checked);

Upvotes: 0

Donut
Donut

Reputation: 112815

Well, there's no entry in cmd.Parameters for "@ShipTrackingNumber". What do you mean by "fetch it from the UI"? You could just write a line to add an entry for this parameter:

cmd.Parameters.AddWithValue("@ShipTrackingNumber", GetShipTrackingNumberFromUI());

And then implement GetShipTrackingNumberFromUI() to get the value you want.

It looks like this line is supposed to be something along these lines, but you need to change "@TrackingNumber" to "@ShipTrackingNumber":

cmd.Parameters.AddWithValue("@TrackingNumber", row.Cells[7].FormattedValue);

Upvotes: 1

Related Questions