How to Pick up values from datagrid ,search the database and update new values?

Question

I have a datagrid and in it I have many rows. I just want to pick up 2 columns from which one row from which one column will search the database for that row with that value and the second column will update that row with the new value. Please help.

My code which is giving a syntax error

Incorrect syntax near the keyword 'VALUES'

my code

{
            using (SqlConnection con = new System.Data.SqlClient.SqlConnection("Data Source=rex;Initial Catalog=PersonalDetails;Integrated Security=True"))
            {
                con.Open();
                for (int i = 0; i <= dataGridView2.Rows.Count - 1; i++)
                {
                    String insertData = "UPDATE  Test SET AvailableQty = " + "VALUES (@Qty) Where ItemCode = " + "VALUES (@ItemCode) ";

                    SqlCommand cmd = new SqlCommand(insertData, con);
                   cmd.Parameters.AddWithValue("@ItemCode", dataGridView2.Rows[i].Cells[0].Value ?? DBNull.Value);
                   cmd.Parameters.AddWithValue("@Qty", dataGridView2.Rows[i].Cells[4].Value ?? DBNull.Value);


                    cmd.ExecuteNonQuery();

                }
            }
        }

It's probably rejecting the second VALUES in the WHERE clause. — DOK, May 14 '13 at 12:37

Rahul · Answer 1 · 2013-05-14T12:56:23.030

0

You have wrong update query change your query like

String insertData = "UPDATE Test SET AvailableQty = @Qty Where ItemCode = @ItemCode";

For more information click here

For Getting the availableQty from database use select query like

Select availableQty from tablename where `use here primary value column and it's value'

like i have id as a primary column with value 1 then i write

Select availableQty from tablename where id = 1

After getting value you can easily substract like

double substractval = availableQty  - dataGridView2.Rows[i].Cells[4].Value;

Now at last use your update query like

Update tablename set availableQty = '"+substractval +"' where "pass primary column and value

You have to use this type of scenario. Hope you understand and works for you.

edited May 14 '13 at 12:56

answered May 14 '13 at 12:35

Rahul

5,603
6
34
57

i want i want the availableqty to subtract the qty from it and update database ... – user2308205 May 14 '13 at 12:40
you want to substract available qty from which quantity.plz explain .i will tell that how to update but plz explain deep and more,it's not clear.thanks – Rahul May 14 '13 at 12:42
i want to the availableQty from the database to subtract the qty from the datagrid and update it in the datbase field availableqty – user2308205 May 14 '13 at 12:48
i tried this but it dident work String insertData = "UPDATE Test SET AvailableQty = (AvailableQty - @Qty) Where ItemCode = @ItemCode"; – user2308205 May 14 '13 at 12:50
brother no one can give you whole code it's a wrong thing,just understand my logic it's easy.Already I have given you all code just check the logic. – Rahul May 14 '13 at 13:03

score 0 · Answer 2 · answered May 14 '13 at 12:37

First of all, you should always use parameterized queries. This kind of codes are open for SQL Injection attacks.

I think you misunderstand the syntax of Update in T-SQL.

using (SqlConnection con = new System.Data.SqlClient.SqlConnection("Data Source=rex;Initial Catalog=PersonalDetails;Integrated Security=True"))
{
       con.Open();
       for (int i = 0; i <= dataGridView2.Rows.Count - 1; i++)
       {
            string insertData = "UPDATE Test SET AvailableQty = @Qty Where ItemCode = @ItemCode";
            SqlCommand cmd = new SqlCommand(insertData, con);
            cmd.Parameters.AddWithValue("@ItemCode", dataGridView2.Rows[i].Cells[0].Value ?? DBNull.Value);
            cmd.Parameters.AddWithValue("@Qty", dataGridView2.Rows[i].Cells[4].Value ?? DBNull.Value);

            cmd.ExecuteNonQuery();

        }
}

Thankx Bro ... could u correct this also (String insertData = "UPDATE Test SET AvailableQty = (AvailableQty - @Qty) Where ItemCode = @ItemCode";) — user2308205, May 14 '13 at 12:54

score 0 · Answer 3 · edited May 23 '17 at 11:49

0

Your query string is wrong, that's why it's giving to you a syntax error:

string insertData = "UPDATE Test SET AvailableQty = @Qty WHERE ItemCode = @ItemCode";

And please try to avoid using the String class: look here.

edited May 23 '17 at 11:49

Community

1
1

answered May 14 '13 at 12:37

Omar

16,329
10
48
66

Thankx Bro ... could u correct this also (String insertData = "UPDATE Test SET AvailableQty = (AvailableQty - @Qty) Where ItemCode = @ItemCode";) – user2308205 May 14 '13 at 12:55

How to Pick up values from datagrid ,search the database and update new values?

3 Answers3