index > Windows Forms Data Controls and Databinding > Updating the ms access database using datagridview control

Updating the ms access database using datagridview control

I am binding datagridview control to a dataset. How to the values i edit in the cells will be updated in the access database?

SheikMeeran Thursday, September 10, 2009 5:50 PM

So you are manually using ADO.NET to connect to the database and populate the DataTable and then binding to the DataTable.

The corresponding way to save this then is to have some type of Save option (button or menu option). When the user clicks Save, you again connect to the database and execute an Update SQL statement.

I have similar code here:

http://msmvps.com/blogs/deborahk/archive/2009/08/25/update-a-microsoft-access-database.aspx

Pasted here:

string update = "Update Customer Set Title = ? Where PersonId = ?"; 
string cnnString = 
      "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Customer.mdb;"; 

using (var cnn = new OleDbConnection(cnnString)) 
{ 
    cnn.Open(); 

    using (var cmd = new OleDbCommand(update, cnn)) 
     { 
        // Add the parameters 
        cmd.Parameters.AddWithValue("@Title", "President"); 
        cmd.Parameters.AddWithValue("@PersonId", 1); 

        // Execute the command 
        cmd.ExecuteNonQuery(); 
    } 
}

You will have to change the Update statement to update Supply and set the appropriate parameters.

Hope this helps.

www.insteptech.com ; msmvps.com/blogs/deborahk
We are volunteers and ask only that if we are able to help you, that you mark our reply as your answer. THANKS!

Marked As Answer bySheikMeeran Wednesday, September 16, 2009 5:00 PM

DeborahK Friday, September 11, 2009 3:38 PM

This really depends on how you have the DataGridView bound.

Are you binding to a dataset? Did you bind to the dataset using the wizards provided in Visual Studio?

www.insteptech.com ; msmvps.com/blogs/deborahk
We are volunteers and ask only that if we are able to help you, that you mark our reply as your answer. THANKS!

DeborahK Thursday, September 10, 2009 10:43 PM

No, i didn't bind the dataset using wizards. is it possible to do that only if i am using dataset using wizards?

SheikMeeran Thursday, September 10, 2009 11:01 PM

The problem is not that it is not possible. The problem is that there are *many* ways to do it and how you can do it most easily is dependent on how you have things set up.

So how are you binding to the DataGridView? How are you getting the data into your application?

I have an example of "manually" saving data into an Access database here:

http://msmvps.com/blogs/deborahk/archive/2009/08/25/update-a-microsoft-access-database.aspx

But there are other ways ... depending on how you have things set up.

www.insteptech.com ; msmvps.com/blogs/deborahk
We are volunteers and ask only that if we are able to help you, that you mark our reply as your answer. THANKS!

DeborahK Thursday, September 10, 2009 11:51 PM

Here is the peace of code whic i am using to bind.

DataSet ds = new DataSet("myDataset");

DataTable dt = new DataTable("Supply");

OleDbDataAdapter ad;

OleDbConnection mycon;

string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Administrator\My Documents\MilkDB.mdb;Persist Security Info=False";

mycon = new OleDbConnection(connString);

mycon.Open();

ad = new OleDbDataAdapter("SELECT * FROM Supply", mycon);

if ((!ds.Tables.Contains("Supply")))

{

ds.Tables.Add(dt);

}

ad.Fill(dt);

dataGridView1.DataSource = ds.Tables[0];

mycon.Close();

ad.Dispose();

SheikMeeran Friday, September 11, 2009 1:47 AM

If your requirement is to edit the grid dataand save/update the same in database, please check the code(in the below link)which will edit the text in other window and save it in db when you selectany grid cell to edit.

http://www.codeproject.com/KB/vb/DataGridViewEditForm.aspx

Rohini Chavakula Friday, September 11, 2009 8:00 AM

string update = "Update Customer Set Title = ? Where PersonId = ?"; 
string cnnString = 
      "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Customer.mdb;"; 

using (var cnn = new OleDbConnection(cnnString)) 
{ 
    cnn.Open(); 

    using (var cmd = new OleDbCommand(update, cnn)) 
     { 
        // Add the parameters 
        cmd.Parameters.AddWithValue("@Title", "President"); 
        cmd.Parameters.AddWithValue("@PersonId", 1); 

        // Execute the command 
        cmd.ExecuteNonQuery(); 
    } 
}

You will have to change the Update statement to update Supply and set the appropriate parameters.

Hope this helps.

www.insteptech.com ; msmvps.com/blogs/deborahk
We are volunteers and ask only that if we are able to help you, that you mark our reply as your answer. THANKS!

Marked As Answer bySheikMeeran Wednesday, September 16, 2009 5:00 PM

DeborahK Friday, September 11, 2009 3:38 PM

Awesome, that worked... ThankYou..... DeborahK and Rohini

SheikMeeran Wednesday, September 16, 2009 5:01 PM

You can use google to search for other answers

Custom Search

More Threads

• DataGrid View Column problems.

• changing the select query for a dataset

• datatable and dataset

• Initial Selection in Combo box

• update textbox binding

• datagridview combobox selected index

• how to moveup and movedown

• Difference between BindingSource.position and BindingContext(DataSet).position

• Double Data Entry for verification

• Format Double to display negatives in parenthesis