Link to home
Start Free TrialLog in
Avatar of MGothelf
MGothelfFlag for United States of America

asked on

How to update a DataGridView in C#?

I have an application where I load a datagridview from a datasource, then make changes in the grid.  HOW DO I GET THE CHANGES BACK TO THE DATABASE?

Here is my code:
private void Form1_Load(object sender, EventArgs e)
 private void btnApplyUpdates_Click(object sender, EventArgs e)
            this.hardware_ShipmentsTableAdapter.Update (WHAT BELONGS HERE???);

I am not sure how to create the UPDATE method in my Table Adapter.  It looks like I create a SQL statement to update values passed as parameters, but how does that fit into my code?  I AM STUMPED.

Avatar of anyoneis
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You can also pass the entire Dataset, or a DataRow, or an array of DataRows.
Avatar of Gautham Janardhan
Gautham Janardhan

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of MGothelf


Thanks, guys.  I understand what you suggested, and i will try when I return to work on Monday.  

However, there was a second part to my question that I need help with - How to create the update method.  When I use the wizard to create, it produces a method with tons of parameters - one for every field in the table.  

What about all the parameters?  In your solutions, you show an update method with no parms, just the name of the dataset & table to be updated.
When you generate a strongly typed dataset (STD), you get four update methods taking:

1) a (STD) dataset
2) a (STD) datatable
3) a DataRow
4) an array of DataRows.

If you also check the "Create methods to send updates directly to the database", then you get an update method with a parameter for each column.

I can't find a way to only get the latter.

Avatar of Gautham Janardhan
Gautham Janardhan

if the dataset was auto generated by the studio it will automatically bind those parameters to the columns in  a datatable contained in ur dataset like

take an example u have a table in ur dataset called "Employee" and it has three fields empid,name,age

then if empid is ur promary key the designer generated update command would be like would be like

"update employee set empname = @empname , empage = @empage where emid = @empid"

u neednt provide these parametrs the designer would have binded these parameters to ur coreesponding columns in the employee table

if ur interested this is how its done

first we set the adapter.update command to the above said query the

add sql parameters to the update command like

Aparameter.ParameterName = "@Empid"
Aparameter.SourceColumn = "EMPID"

similarly for the other two parameters

so all u have to do is call the update command and the adapter would fetch the necessary parameters from the table which u specified in

adpater.Update(Aset.Tables["Mytbale"]) or adpater.Update(Aset.Tables[index])

OK - I have tried to do what you suggested, but I have run into a couple of problems.

(1) As I said before, there is no UPDATE method in the table adapter.  If I try to add one now, the wizard presents me with a dialog box with UPDATE and expects me to type the entire query.  This does not sound right.

(2) If I start from scratch with a new DataSet/TableAdapter over my database view, the same happens.  However, if I try to create a new DataSet/TableAdapter where the datasource is a table, I get an update string with all the parameters.

(3) If I select 'CONFIGURE' my DataSource, and select 'Advanced Options' from the TableAdapter Configuration Wizard, I get the option to Generate Insert/Update & Delete statements, but the check box to "Create methods to send updates directly to the database" is disabled - it is greyed out.  However, if I try this with a datasource built on a TABLE not a VIEW the option is available.  

I changed the application so that my datasource was a table not a view, and it all works as described in your posts.  Is there any way it could work if I am binding a database view, not a dtabase table?
Hmm. Is the view a single table view? I thought that would work. But, in any case, the best you can do is create the dataset with a table and then deploy it to a database that has the view.

if u r trying to update a view i would suggest not to do thatthere are lot of problems working with updatable views  ..first of all u should have all the tables of the table which u intend to update in ur view then only the view is updatable..i'm not sure on this..i would prefer updating a table rather that a view.. i have always run into trouble when i use updatable views
The view is just a subset of one table, so IN THIS CASE, I can change the SQL select in my DataSet to return the rows I need, and the fields I need, but why shouldn't this work with a view.  Also, you said 'make the view updatable'.  How do I tell if a view is updatable?  And how to make it updateable if it is not?
Have you tried hiding the view behind a set of CRUD stored procedures?


I don't know what you mean by CRUD stored procedures?
a view becomes updatable when all the fields of a table are references inside the view (wrt to that table)

hope ur problem is solved
and ne more thing u have to keep in mind is that by calling the adapter.Update method u are not calling the UpdateCommand but in fact

all the three commands are involved The InserCommand the UpdateCommand and the DeleteCommand

the adapter calls each command wrt to the RowState of the DataRow in the DataTable which can be Added,Modified,Deleted,UnChanged
CRUD procedures are insert, update, and delete stored procedures.

The only reason I was using a view was because it was a single table with about 40 fields, only 5 of which are in my DataGridView.

regarding adapter.Update vs. UpdateCommand - If I want to call the UpdateCommand directly, how could I do that other than read through table get changed rows, extract appropriate fields, etc.
ur problem is that u are using a view to dsplay the records and because this view has only five fields from the table this view wont be updatable so u cant rely on the designer's defult commands which will be written based on this view and would obviously fail..
if u were using the table intead and only specifying (selecting in the designer) the fields u want then the command would be built correctly by the designer and would work..

if u have to use the view and update ur orginal table i dont think there is another way other than building the commands as i showed earlier..


Please explain what you mean by:

if u were using the table intead and only specifying (selecting in the designer) the fields u want then the command would be built correctly by the designer and would work..

how are u creating the dataset is it through the designer or through the studio

if u are doing it through a designer by draggign down an adaptor the u can select the fields u want from a table (adpter configuration wizard>BuildQuery)

if u are doing it throu code which i find is more flexibile than the designer one all u have to do is assign the select command of the adapter like

Adpater.SelectCommand = new SqlCommand(
"select col1 , col2 from mytable where myfiled = @myfiled",sqlconnection);
Thanks, I figured out what you meant, and it works just fine.  I replaced the view with the table, and did the selection of fields in the designer and it created the TableAdapter.Update command.

THANKS TO BOTH OF YOU.  I really did learn a lot through this exercise.  I am increasing the points and will split between you.