Avatar of MGothelf
MGothelf
Flag 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)
        {
            this.hardware_ShipmentsTableAdapter.Fill(this.billOfLadingSQLDataSet1.Hardware_Shipments);
        }
 private void btnApplyUpdates_Click(object sender, EventArgs e)
        {          
            this.hardware_ShipmentsTableAdapter.Update (WHAT BELONGS HERE???);
            billOfLadingSQLDataSet1.AcceptChanges();
        }

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.

C#

Avatar of undefined
Last Comment
Gautham Janardhan

8/22/2022 - Mon
SOLUTION
anyoneis

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
anyoneis

You can also pass the entire Dataset, or a DataRow, or an array of DataRows.
ASKER CERTIFIED SOLUTION
Gautham Janardhan

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
MGothelf

ASKER
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.
anyoneis

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.

David
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
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

we
add sql parameters to the update command like

Adapter.UpdateCommand.Parametrs.ad(Aparameter)

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])






MGothelf

ASKER
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.  
MGothelf

ASKER
SUCCESS - SORT OF.

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?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
anyoneis

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.

David
Gautham Janardhan

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
Gautham
MGothelf

ASKER
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?
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
anyoneis

Have you tried hiding the view behind a set of CRUD stored procedures?

David

MGothelf

ASKER
I don't know what you mean by CRUD stored procedures?
Gautham Janardhan

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Gautham Janardhan

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
anyoneis

CRUD procedures are insert, update, and delete stored procedures.
MGothelf

ASKER
gauthampj-

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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Gautham Janardhan

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..

Gautham

MGothelf

ASKER
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..

Gautham Janardhan

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);
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
MGothelf

ASKER
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.

Mark
Gautham Janardhan

:-)