Updating and Deleting Records from the DataGridView and database

i m new here, i want to开发者_StackOverflow中文版 update and delete the records from the datagridview as well as from database i have the following code for the update and delete..

int num = 0;
foreach (DataGridViewRow row in this.dataGridView1.Rows)
{
    if (Convert.ToBoolean(row.Cells[0].Value))
    {
        string ID = this.dataGridView1.Rows[row.Index].Cells["ID"].Value.ToString();
        string CoCode = this.dataGridView1.Rows[row.Index].Cells["CoCode"].Value.ToString();
        string CoName_mar = this.dataGridView1.Rows[row.Index].Cells["CoName_mar"].Value.ToString();

        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Documents and Settings\\mayur patil\\My Documents\\Dairy_db\\tblCompany_1.mdb";
        con.Open();

        OleDbCommand cmd = new OleDbCommand("UPDATE tblCompany_1 SET (CoCode=@CoCode,CoName_mar=@CoName_mar where ID=@ID)", con);

        cmd.Parameters.AddWithValue("CoCode", CoCode);
        cmd.Parameters.AddWithValue("CoName_mar", CoName_mar);
        cmd.Parameters.AddWithValue("ID", ID);
        cmd.ExecuteNonQuery();
        con.Close();
        num++;
    }
}
if (num > 0)
{
    MessageBox.Show("Record Updated !");        
}
this.Hide();
listOfCompany v = new listOfCompany();
v.ShowDialog();

Add for delete records..

OleDbConnection con = new OleDbConnection();
con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data  Source=C:\\Documents and Settings\\mayur patil\\My Documents\\Dairy_db\\tblCompany.mdb";
OleDbCommand cmd = new OleDbCommand ("DELETE FROM tblCompany WHERE (CoCode = i)");

con.Open();

cmd.ExecuteNonQuery();
con.Close();

1. for the update query i m getting the error that"OleDbException was handled syntax error in UPDATE" at cmd.ExecuteNonQuery();

2. for the delete query i m getting the error "OleDbException was handled.. Could not find installable ISAM." at con.Open();

how should i overcome from this problem?

1) Remove the unnecessary parentheses:

OleDbCommand cmd = new OleDbCommand("UPDATE tblCompany_1 SET CoCode=@CoCode,CoName_mar=@CoName_mar where ID=@ID", con);

You can try putting the connection opening and closing outside the foreach loop so you don't have to do that every time.

2) Remove the extra space in your connection string:

con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Documents and Settings\\mayur patil\\My Documents\\Dairy_db\\tblCompany.mdb";

Also, you have an undefined i in your delete statement. Is that supposed to be a parameter?

For your 2 problems:

1. Remove the parenthesis altogther after your SET keyword.

2. This error indicates that there's a problem with the connection string. You have an extra space in your Data Source. It was Data^^Source.

Suggest moving your .mdb to a filepath WITHOUT spaces.

string conn = @"PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DB\tblCompany.mdb";

1. The first query doesn't need parenthesis inside of the query. Replace with

   UPDATE tblCompany_1 SET CoCode=@CoCode, CoName_mar=@CoName_mar where ID=@ID 
   

2. The delete query: What is i? If you have it defined you can do something like this:

   "DELETE FROM tblCompany WHERE (CoCode = " + i + ")"
   

   Although, using parameters would be better.