updating column value in Ms-Access 2010 using c# and oledb query

Question

I am struggling for updating record/columnvalue in MS-ACCESS database... help would be appreciated a lot..!

I am displaying a list of partnumbers retrieved from a table in Ms-access using Datagridview in which I am supposed to update/change partnumber. ( 'partno' is 3rd column of my datagridview.)

But I am unable to Update a single record in database..no exceptions.. everything is going fine.!

But no rows are effected!

Here is my code:

 private void UpdateDetails_Click(object sender, EventArgs e)
 {
      try
      {
          con = new OleDbConnection();
          con.ConnectionString = Helper.MyConnectionString;
          con.Open();

          for (int i = 0; i <= datagridview1.Rows.Count-1; i++)
          {
             int j = i + 1; // j is the serial number corresponding to partnumber

             string partno = dgv1.Rows[i].Cells[2].Value.ToString(); //getting part number from Datagridview

             String partquery = "";

             if (partno == null || partno == "") //checking whether part number updated or not
             {
                 partquery = "update Vendor SET PartNo=NULL where Vendor.Sno=" + j + " ";
             }
             else           
                 partquery = "update Vendor SET PartNo='" + partno + "' where Vendor.Sno=" + j + " ";

             //Vendor is the table name containg 'partno' list

             cmd = new OleDbCommand();
             cmd.Connection = con;
             cmd.CommandType = CommandType.Text;
             cmd.CommandText = partquery;
             cmd.ExecuteNonQuery();
          }
      }
      catch(Exception ex)
          {
        //exception handler
      }

}

Use parameterized sql instead of string concatenations. This kind of concatenations are open for SQL Injection attacks. — Soner Gönül
– Soner Gönül, Commented Sep 12, 2013 at 6:50
I guess this link will resolve your issue [1]: stackoverflow.com/questions/4589348/… — ZubinAmit
– ZubinAmit, Commented Sep 12, 2013 at 6:57
Thanks for the response.! I tried that too.. but no use.. Can u please suggest me parameterised sql code snippet.. @Soner Gonul — rtvalluri
– rtvalluri, Commented Sep 12, 2013 at 6:57
I already visited that link.. and tried too.. Still no rows effected..@ZubinAmit — rtvalluri
– rtvalluri, Commented Sep 12, 2013 at 7:00

Nilesh · Accepted Answer · 2013-09-12 07:26:11Z

As @Soner suggested you should use parameters. Something like this.

Modified the code did you do something like this?

    private void UpdateDetails_Click(object sender, EventArgs e)
    {
        try
        {
            con = new OleDbConnection();
            con.ConnectionString = Helper.MyConnectionString;
            con.Open();

            for (int i = 0; i <= datagridview1.Rows.Count - 1; i++)
            {
                int j = i + 1; // j is the serial number corresponding to partnumber

                string partno = dgv1.Rows[i].Cells[2].Value.ToString(); //getting part number from Datagridview

                //String partquery = "";

                //if (partno == null || partno == "") //checking whether part number updated or not
                //{
                //    partquery = "update Vendor SET PartNo=NULL where Vendor.Sno=" + j + " ";
                //}
                //else
                //    partquery = "update Vendor SET PartNo='" + partno + "' where Vendor.Sno=" + j + " ";

                OleDbCommand cmd = new System.Data.OleDb.OleDbCommand("update Vendor SET PartNo='@partno' where Vendor.Sno=@vndid");
                OleDbParameter parameter = new System.Data.OleDb.OleDbParameter("@partno", partno);
                cmd.Parameters.Add(parameter);
                parameter = new System.Data.OleDb.OleDbParameter("@vndid", j);
                cmd.Parameters.Add(parameter);


                cmd.Connection = con;
                cmd.CommandType = CommandType.Text;
                cmd.ExecuteNonQuery();
            }
        }
        catch (Exception ex)
        {
            //exception handler
        }

    }

Collectives™ on Stack Overflow

updating column value in Ms-Access 2010 using c# and oledb query

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related