update qty datagrid in c# and sql

Question

I have message from this line like this: "

Object reference not set to an instance of an object"

//update tblproduct qty
connection.Open();
command=new SqlCommand("Update tblproduct set qty = "+int.Parse(dataGridView2.Rows[i].Cells[5].Value.ToString())+" WHERE pcode like '" +dataGridView2.Rows[i].Cells[3].Value.ToString() + "'",connection); 
command.ExecuteNonQuery();
connection.Close();



 private void btnSave_Click(object sender, EventArgs e)
 {
     try
     {
         if (dataGridView2.Rows.Count > 0)
         {
             if (MessageBox.Show("Are you sure you want to save this records?", "DZNPOS", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
             {
                  for (int i = 0; i < dataGridView2.Rows.Count; i++)
                  {
                      //update tblproduct qty
                      connection.Open();
                      command=new SqlCommand("Update tblproduct set qty = "+int.Parse(dataGridView2.Rows[i].Cells[5].Value.ToString())+" WHERE pcode like '" +dataGridView2.Rows[i].Cells[3].Value.ToString() + "'",connection); 
                      command.ExecuteNonQuery();
                      connection.Close();

                      //update tblStockin
                      connection.Open();
                      command = new SqlCommand("update tblStockin set qty = qty +" + int.Parse(dataGridView2.Rows[i].Cells[5].Value.ToString()) + ", status = 'Done' where id like '" + dataGridView2.Rows[i].Cells[1].Value.ToString() + "'", connection);
                      command.ExecuteNonQuery();
                      connection.Close();
                  }
                  Clear();
                  LoadStockin();
              }
          }
      }
      catch (Exception ex)
      {    
          connection.Close();
          MessageBox.Show(ex.Message, "DZN POS", MessageBoxButtons.OK, MessageBoxIcon.Warning);
       }
  }

Exactly what line do you get the error? And please format the error message better, as that is very hard to read. Also maybe add some more code, like the gridview. — Neophear, Jul 15 '20 at 06:03
As a general note: Don't open and close DB connections for each query. They are VERY slow to open. You should keep the connection open for the lifecycle of your application, and ideally use connection pooling. — jason.kaisersmith, Jul 15 '20 at 06:10
command=new SqlCommand("Update tblproduct set qty = "+int.Parse(dataGridView2.Rows[i].Cells[5].Value.ToString())+" WHERE pcode like '" +dataGridView2.Rows[i].Cells[3].Value.ToString() + "'",connection); this line — Davut Taş, Jul 15 '20 at 06:24
Maybe this will help you: https://stackoverflow.com/questions/218384/what-is-a-nullpointerexception-and-how-do-i-fix-it — Homungus, Jul 15 '20 at 06:31
@jason.kaisersmith, Sorry, but you're wrong. Please, read about [connection pool](https://en.wikipedia.org/wiki/Connection_pool). — Maciej Los, Jul 15 '20 at 06:35
It's obvious. Cell[5] in a Row[i] stores `null`. BTW: Please, do not use concatenated string to build query. This is sql injection vulnerable. Rather than it, use [parameterized queries](https://visualstudiomagazine.com/articles/2017/07/01/parameterized-queries.aspx). — Maciej Los, Jul 15 '20 at 06:37
@MaciejLos What exactly is wrong about my statement? I admit that in this case the OP maybe doesn't need a connection pool, but opening and closing DB connections like this is not good. — jason.kaisersmith, Jul 15 '20 at 06:40
@jason.kaisersmith, please, refer this: [Why always close Database connection?](https://stackoverflow.com/questions/4111594/why-always-close-database-connection) — Maciej Los, Jul 15 '20 at 06:50
@MaciejLos Sorry, but this does not prove what I said as wrong. In fact, it shows the opposite. If you are using connection pools then you open and close when required, but this should not be like above between statements which are so close together and not in a loop, as it just adds unnecessary overhead for no benefit. Open once at the beginning of the block of code and close after. Or if you are not using connection pooling then you should keep it open for longer, maybe the whole application lifecycle, maybe not, depending on the application and use case. — jason.kaisersmith, Jul 15 '20 at 06:57

update qty datagrid in c# and sql

0 Answers0