c# updating datagridview using sqlcommand

Question

I've tried to update my datagridview. (edited)

My code:

private void button1_Click(object sender, EventArgs e)
    {
        con = new System.Data.SqlClient.SqlConnection();
        con.ConnectionString = "Data Source=tcp:SHEN-PC,49172\\SQLEXPRESS;Initial Catalog=LSEStock;Integrated Security=True";
        con.Open();


        if (dataGridView1.Rows.Count > 0)
        {   
            int nRowIndex = dataGridView1.Rows.Count-2;

            if (dataGridView1.Rows[nRowIndex].Cells[1].Value != null)
            {
                textBox2.Text = Convert.ToString(dataGridView1.Rows[nRowIndex].Cells[1].Value);
                String updateData = "UPDATE CostPrice SET SupplierName = @SupplierName, CostPrice = @CostPrice WHERE PartsID = '" +textBox1.Text+"'";
                SqlCommand update = new SqlCommand(updateData, con);
                SqlDataAdapter adapter = new SqlDataAdapter(updateData, con);

                update.Parameters.Add("@SupplierName", SqlDbType.NVarChar, 50, "SupplierName");
                update.Parameters.Add("@CostPrice", SqlDbType.NVarChar, 50, "CostPrice");
                adapter.UpdateCommand = update;

                //update.ExecuteNonQuery();

                if (update != null)
                {
                    update.Dispose();
                    update = null;
                }

            }
            else
            {
                MessageBox.Show("NULL");
            }
        }
        con.Close();
    }

It says that ExecuteNonQuery is not initialized. What's wrong with my codes?

I'm using SqlCommand to update, but what I see from the internet almost everyone is using SqlDataAdapter, what's the difference? Thanks in advance.

If you have a better code, I would like to learn from that. Thanks!

score 1 · Answer 1 · edited May 23 '17 at 12:03

You can read up about Data Adapter Vs Sql Command

To overcome the error, I believe you need to assign the command's Connection = con

update.Connection = con;

Here is the reference: SqlCommand.Connection Property

Or in this line:

SqlCommand update = new SqlCommand(updateData, con);

ps Don't forget to dispose SqlCommand objects once your done with them:

if (sqlCommand != null)
{
    sqlCommand.Dispose();
    sqlCommand = null;
}

BizApps · Answer 2 · 2012-08-03T08:28:15.697

1

1st: make sure your Update Query is Correct

Update Syntax:

   UPDATE [TableName] SET [CoulumnName]='new value' where [ColumnName]='yourFilterValue'

your update query must be somthing like this:

 String updateData = "UPDATE CostPrice SET CostID = @CostID, SupplerName = @SupplierName, CostPrice = @CostPrice WHERE PartsID = @PartsID";

2nd: You just missed to add the sqlconnection on your sqlcommand.

SqlCommand update = new SqlCommand(updateData,con);

3rd: You have Parameters on your query but you even not set its values.

check this sample on csharp-station : ado.net lesson 6

this your code will look like:

string updateData = "UPDATE CostPrice SET SupplierName = @SupplierName, CostPrice = @CostPrice WHERE PartsID =@PartsID";
SqlCommand update = new SqlCommand(updateData, con);

update.Parameters.Add("@SupplierName", SqlDbType.NVarChar, 50, "SupplierName");
update.Parameters.Add("@CostPrice", SqlDbType.NVarChar, 50, "CostPrice");
update.Parameters.Add("@PartsID", SqlDbType.NVarChar, 50,textBox1.Text);
update.ExecuteNonQuery();

Regards

edited Aug 03 '12 at 08:28

answered Aug 03 '12 at 04:48

BizApps

6,048
9
40
62

but then it shows me "Incorrect syntax near ','. at the executenonquery() line – J.S. Aug 03 '12 at 04:52
i've changed my code, no error occured, but the database is not updating. can u take a look pls? – J.S. Aug 03 '12 at 07:06
remove SqlDataAdapter adapter = new SqlDataAdapter(updateData, con); and adapter.Update(); and just use update.ExecuteNonQuery(); that's it.it will work. :D vote up and mark as answer. – BizApps Aug 03 '12 at 07:10
oh my, it comes out with this "The parameterized query '(@SupplierName nvarchar(50),@CostPrice nvarchar(50))UPDATE CostP' expects the parameter '@SupplierName', which was not supplied." – J.S. Aug 03 '12 at 07:25

score 0 · Answer 3 · answered Aug 03 '12 at 04:42

0

as Jeremy Thompson suggested, you are missing one line of code.

update.Connection = con;

Add this code just before calling update.ExecuteNonQuery();

answered Aug 03 '12 at 04:42

Karthik D V

906
1
11
19

but then it shows me "Incorrect syntax near ','. at the executenonquery line – J.S. Aug 03 '12 at 04:47

c# updating datagridview using sqlcommand

3 Answers3