Datagridview / SQL / Saving data

[peterc]

VS 2015, SQL 2008

Okay, have been trying to get my head around this one for a day or so now, and have searched extensively but to no avail......

I have a SQL view (2 tables into 1), for a datagridview.

I am using datasource, and databindings instead of SQL code to populate the datagrid.

How do I go about saving any changes to the database, if someone changes the data on a row.

Have tried, the follwong code, attached to a button (save), but to no avail.

          Me.bsJobvehicle.EndEdit()
                Me.Dsttdjobs.AcceptChanges()

Do I need to be updating the tables explicitly or can the view handle the updating.

Any help greatly appreciated.

 

[jmcilhinney]

I am using datasource, and databindings instead of SQL code to populate the datagrid.

Firstly, that doesn't really make sense. I'm guessing what you actually mean is that you are using a typed DataSet, which simply means that the system generated the SQL code instead of your writing it yourself. The typed DataSet and table adapters are still using the same SqlClient types under the hood that you would have used directly otherwise.

Now, when you use the Data Source wizard to generate a typed DataSet, it generates a typed DataTable for each selected table, view or stored procedure and a table adapter for each DataTable. Each table adapter wraps a data adapter and they have SelectCommand, InsertCommand, UpdateCommand and DeleteCommand. It should be obvious what each of those is for. When you call Fill on the table adapter, it calls Fill on the internal data adapter and that executes the SelectCommand to retrieve data into a DataTable. When you call Update, the InsertCommand, UpdateCommand and DeleteCommand are executed as required to save changes from a DataTable back to the database.

When a table adapter is generated, the InsertCommand is generated if and only if the target is a table while the UpdateCommand and DeleteCommand are generated if and only if the target is a table with a primary key. Whether or not those commands are generated automatically, you are free to use the DataSet designer to add/edit them as you see fit. If you have a DataTable generated from a view then you'll get a SelectCommand to retrieve data but no InsertCommand, UpdateCommand or DeleteCommand. You can modify the table adapter in the designer and add those commands yourself. Presumably you will want to reference the source tables of the view. If you want to reference both then you can add multiple SQL statements separated by semicolons.

 

[peterc]

Okay...I can see where the confusion from my oringal post may have originated, and yes I am can see what you are suggesting.

The problem is that the wizard does not like joined table, and so will not automatically generate the code for me from the wizard. (I am sure this is sorta making sense)

So, my next question then would be how to I create the code.....This is the one area that I am failing at miserably.

I need to write back to the database based on the change on a row on the datagrid. Can you point me in the right direction for creating a update...where clause, when it is based on a row

 

[jmcilhinney]

Start by writing the code to save changes to one of the tables. Once you have that working, it should be fairly simple to modify it to work for both, with maybe a gotcha or two I can help with. You can easily see how to do it for one table by looking at what gets auto-generated by the wizard for a table.

 

[peterc]

Okay, I can sort of see what needs to be done, but it also seems that I am possibly making things over complicated for myself.

All the online tutorials / videos / suggestion seem to indicate that a SQLCon is maybe a better way to go.

Am I better off using an SQLCon and setting things up that way, or continue using a typed dataset?

 

[jmcilhinney]

There's no such thing as a SQLCon. Presumably you mean a SqlConnection. If you use the actual name of the thing you're talking about then there's no rom for misinterpretation or confusion.

A SqlConnection is not an alternative to a typed DataSet. If you're using a typed DataSet then you're already using a SqlConnection; just indirectly.

If you write your own ADO.NET code then you would create a SqlDataAdapter with four SqlCommands and each those would reference the same SqlConnection. You'd call Fill on that to populate a standard DataTable, which might be contained in a standard DataSet, and then call Update to save the changes back to the database.

If you use the Data Source wizard then the system creates the SqlConnection, SqlCommands and SqlDataAdapter for you and wraps them up in a table adapter. It also creates a custom DataTable that inherits the standard DataTable as well as a custom DataSet that inherits the standard DataSet, each of which have some extra code that's specific to your data. When you call Fill on your table adapter, it calls Fill on the underlying SqlDataAdapter to populate the custom DataTable, which is contained in the custom DataSet.

As you can see, ADO.NET is basically the same whether you use typed or untyped ADO.NET. The difference is that typed ADO.NET doesn't force you to use magic strings and numbers to identify tables and columns. The custom classes all have specific properties that match your data, thus providing full Intellisense and compile-time validation. Some people will tell you that using untyped ADO.NET is better because it provides more control but that's a crock. Typed DataSets provide all the control you need in the vast majority of situations; it's just that they are configured via a designer rather than in code. In the small minority of situations where you do need more, you can simply drop back to using untyped ADO.NET code in those situations specifically. In your current situation, you are in complete control of the SQL code used to save the changes back to the database by your table adapter. It's the same code you would write using untyped ADO.NET. The only difference is that you write in the designer instead of in the code window.

 

[peterc]

Thank you.

I will use full words from now on...appreciate that comment.

But what I really appreciate is your honesty and plain language in detailing that there is nothing wrong with the way I am heading.

I prefer using a typed dataset as it allows me to format the datagridview, explicitly, and give me the control I require.