CODEWITHSUNDEEP
c#datagridview
J.S.
J.S.

Reputation: 83

c# updating datagridview using sqlcommand

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!

Upvotes: 0

Views: 2016

Answers (3)

BizApps
BizApps

Reputation: 6130

First: make sure your Update query is correct.

Update syntax:

UPDATE [TableName] 
SET [ColumnName] = 'new value' 
WHERE [ColumnName] = 'yourFilterValue'

Your update query must be something like this:

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

Second: you just missed to add the SqlConnection on your SqlCommand.

SqlCommand update = new SqlCommand(updateData, con);

Third: you have parameters in your query, but you don't even set their values.

Check this sample at csharp-station : ado.net lesson 6

This is what 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

Upvotes: 1

Karthik D V
Karthik D V

Reputation: 926

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

update.Connection = con;

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

Upvotes: 0

Jeremy Thompson
Jeremy Thompson

Reputation: 65722

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;
}

Upvotes: 1

Related Questions