Update Statement Syntax?

[PhillD]

Hi.

I have a DataGridView which is bound to a DataSet. The data comes from multiple tables with join statements, therefore, I am unable to use the SQLCommand builder to automatically update the data.

When the data is loaded in the grid, it will contain multiple rows (with a hidden primary key field). The user needs to change the data in 1 of the columns and save the data back to the database with the tableadapter.Update command.

The database is SQL

I have written the update statement but I don't know how to pass the correct parameters to it. Because it is a bulk update, I don't know how to make the correct rows get updated with the correct data. Here's my update statement and the parameters I am trying to pass.

        MyExpenseLinesAdapter.UpdateCommand = New SqlCommand
        MyExpenseLinesAdapter.UpdateCommand.Connection = MyPhantomConn
        MyExpenseLinesAdapter.UpdateCommand.CommandText = "UPDATE ExpenseLines SET ApprovedAmt = @ApprovedAmt WHERE ExpenseLineID = @ExpenseLineID"

        MyExpenseLinesAdapter.UpdateCommand.Parameters.AddWithValue("@ApprovedAmt", ApprovedAmt)
        MyExpenseLinesAdapter.UpdateCommand.Parameters.AddWithValue("@ExpenseLineID", ExpenseLineID)

        MyExpenseLinesAdapter.Update(MyExpenseLinesDataSet, "ExpenseLines")

When I run the code, I get the following error.
"No mapping exists from object type System.Windows.Forms.DataGridViewTextBoxColumn to a known managed provider native type."

I know I haven't mapped the fields correctly but I don't know how else to do it.

Please can someone help me.

Thanks

 

[jmcilhinney]

If you want to update multiple rows from a DataTable then you don't use AddWithValue. You use Add and you tell the parameter which column of the DataTable to get its data from. When you call Update on your adapter, it will automatically loop through all the rows that need to be updated. For each one, it will get the data from the appropriate columns, assign them to the parameters and then execute the SQL. In your case, these lines:

MyExpenseLinesAdapter.UpdateCommand.Parameters.AddWithValue("@ApprovedAmt", ApprovedAmt)
MyExpenseLinesAdapter.UpdateCommand.Parameters.AddWithValue("@ExpenseLineID", ExpenseLineID)

should look something like this:

MyExpenseLinesAdapter.UpdateCommand.Parameters.Add("@ApprovedAmt", SqlDbType.Money, 0, "ApprovedAmt")
MyExpenseLinesAdapter.UpdateCommand.Parameters.Add("@ExpenseLineID", SqlDbType.Int, 0 "ExpenseLineID")

The last argument is the name of the column in the DataTable that contains the data for that parameter. Note that the DataTable column may or may not be the same as the database column.

 

[PhillD]

Thank you, Thank you and Thank you.

I have seen examples where other programmers have traversed entire datagrids/datasets performing 1 update per record. I knew there had to be a better way.

Thanks again.

 

[cjard]

Thank you, Thank you and Thank you.

I have seen examples where other programmers have traversed entire datagrids/datasets performing 1 update per record. I knew there had to be a better way.

Thanks again.

Conceptually, this is all the tableadapter does anyway

What I can't quite work out is why you did all this in code when it's more asily done in the dataset designer. A simple trick I use for u[dating a table that is joined to others is:

Create the tableadapter targeting JUST the table you want to update
Later, modify the query to pull in extra fields from joined tables
When asked the question (to the effect of) "You have altered the main select query for this table adapter, do you want to update the other queries?" say NO

You end up with a TA that will read many tables, and manipulate the original

If you have to manipulate multiple tables (and you don't in this example but if you did) you'll need to craft the update statement yourself:
Write the update statement using all necessary parametters etc
The wizard will populate the parameters collection for you
Browse the parameters given and ensure that their SourceColumnMapping is set to the correct source column

 

[PhillD]

I'm not sure we want to start a flamewar on wizards vs coding but I will tell you that I had previously used the wizard to configure the DataSet/DataAdapter. As things go in development, I needed to add an additional field to the DataSet and BOOOM!!!! My form was broke and no matter what I tried I couldn't fix it. Something in all that hidden code was broken. That was the straw that broke the camels back and made me change directions.

 

[cjard]

I'm not sure we want to start a flamewar on wizards vs coding

There is no flamewar to be had; by eschewing the dataset designer as a "lame wizard" but continuing to use the forms designer to lay out your forms insetad of specifying every property of every textbox by typing code, youre still "wizarding" rather than "coding"

but I will tell you that I had previously used the wizard to configure the DataSet/DataAdapter

In .net 1.1? DataAdapter hasnt really been mentioned in recent years - it now does all the donkey work hidden inside a tableadapter

As things go in development, I needed to add an additional field to the DataSet and BOOOM!!!! My form was broke and no matter what I tried I couldn't fix it. Something in all that hidden code was broken. That was the straw that broke the camels back and made me change directions.

The error message the forms designer gives tells you what is wrong with the code in this case. For example, if you deleted a column from a datatable that you have an on-form textbox bound to, the designer will naturally error out..

If it's any consolation, I've added hundreds of fields months or years after a dataset has first been designed and on the rare occasion that it's broken a form, it has eventually succumbed to methodical fault tracking and careful reading of the emitted errors. Granted, it was more puzzling at first, but several people here will certainly be able to help if you encounter them again - just post about them..

 

[PhillD]

If your happy using the data wizard and it works for you then that is great. Unfortunately, like I said previously, it didn't work in my situation. Not only because of the errors but because of the way the wizards handle data binding with DataGridViews. I have found that I am able to accomplish a lot more things with greater ease than I am relying on the internal workings of some code that I am not able to modify.

I would suspect that a lot of people prefer to code the database integration but use the tool box to place controls on the form. In my opinion, "wizarding" a form works very well, "wizarding" a dataset does not.

 

[jmcilhinney]

If your happy using the data wizard and it works for you then that is great. Unfortunately, like I said previously, it didn't work in my situation. Not only because of the errors but because of the way the wizards handle data binding with DataGridViews. I have found that I am able to accomplish a lot more things with greater ease than I am relying on the internal workings of some code that I am not able to modify.

I would suspect that a lot of people prefer to code the database integration but use the tool box to place controls on the form. In my opinion, "wizarding" a form works very well, "wizarding" a dataset does not.

You don't have to use the Data Source wizard if you don't want to but I can tell you for a fact that if it didn't work for you it's because you did something wrong. Dragging a table or column from the Data Source window doesn't do anything that you couldn't do yourself and it doesn't do anything that you can't change if you want.

 

[jmcilhinney]

Not only that but creating a typed DataSet using the Data Source wizard and then dragging items from the Data Sources window onto a form are two different things. If you use DataSets at all then a typed DataSet is better than an untyped DataSet if for no other reason than the Intellisense support. You can use your DataSet and TableAdapters purely in code, so any data-binding you ever do will be completely your own.

 

[PhillD]

If that is the case, please tell me how I could perform 2 different update statements based on some crtieria in the form.

E.G.

if flag = 1 Then
Perform Update 1
Else
Perform Update 2
End if

Without using 2 seperate table adapters

 

[jmcilhinney]

If that is the case, please tell me how I could perform 2 different update statements based on some crtieria in the form.

E.G.

if flag = 1 Then
Perform Update 1
Else
Perform Update 2
End if

Without using 2 seperate table adapters

You're going to have to provide a more detailed explanation of the circumstances because that description is far too vague for a definitive solution. It might be that using two TableAdapters is exactly the correct option, but that really depends on the specific circumstances.

 

[PhillD]

It was a hypothetical question however, my point is that you can only have 1 static Update, Delete and Insert statement per table adapter. It you want to do something different than what is defined, you have to either modify it permanently or setup another table adapter.

Going back to the comment made by cjard

Create the tableadapter targeting JUST the table you want to update
Later, modify the query to pull in extra fields from joined tables
When asked the question (to the effect of) "You have altered the main select query for this table adapter, do you want to update the other queries?" say NO

You end up with a TA that will read many tables, and manipulate the original

This sounds like something which is bound to go wrong. If another developer has to work on the project and inadvertently hit's the "yes" option, you've just broken your update statement. When you code the stuff yourself, you don’t have this problem, nor any issues when adding additional fields.

 

[cjard]

It was a hypothetical question however, my point is that you can only have 1 static Update, Delete and Insert statement per table adapter. It you want to do something different than what is defined, you have to either modify it permanently or setup another table adapter.

The clue is in the name: TableAdapter, not TablesAdapter, MultiTableAdapter, View Adapter etc

This sounds like something which is bound to go wrong.

You could argue that using visual studio itself is bound to lead to things that will go wrong. Turning a computer on will lead to something that is bound to go wrong..

If another developer has to work on the project and inadvertently hit's the "yes" option, you've just broken your update statement.

Er.. no.. He's just broken the update statement; fixing it is his problem. Ctrl+Z should do it. Closing the dataset without saving the changes should also do it

When you code the stuff yourself, you don’t have this problem, nor any issues when adding additional fields.

So all the code you write by hand is impervious to being damaged by your inept coworker, high performance, secure, well OO encapsulated and you can do it in minutes? Youre entirely correct; you have no need for Visual Studio's assistance in your coding

 

[cjard]

If that is the case, please tell me how I could perform 2 different update statements based on some crtieria in the form.

E.G.

if flag = 1 Then
Perform Update 1
Else
Perform Update 2
End if

Without using 2 seperate table adapters

Right click tableadapter
Add Query
Query type: UPDATE
Write statement e.g. UPDATE person SET driverNumber = random() WHERE age BETWEEN 18 AND 75 and driverNumber IS NULL
Call it something sensible: AllocateDriverNumbersToEligible
Finish

Right click TableAdapter
Add Query
Query type: UPDATE
Write statement e.g. UPDATE person SET driverNumber = null WHERE age > 75
Call it something sensible: DeallocateDriverNumbersForIneligible
Finish

'in myform
If operationCombo.Text = "AllocateNew" Then
  _myTA.AllocateDriverNumbersToEligibile()
Else
  _myTA.DeallocateDriverNumbersForIneligible()
End If

In addition to the two updates you just added, a TA has an update statement intended for saving changes to the local data set back to the DB. If your TA is based on a view you either treat it as read only, update the table that it makes sense to update or base it on a view and use INSTEAD OF triggers to perform DML when your app tries to update the view. You can use local datarelations to decode values for display/edit purposes in grids and preserve the "one table, one tableadapter" notion

As can logically be seen, a TA only has need for the ability to update one table because that is all that its associated datatable represents. There isnt the concept in RDBMS that one tableadapter should update multiple tables because youre nearly always talking about a partly cartesian join where rows from one table are repeated. If you allow the user to edit a repeated row to have 2 different values, which one wins? If youve designed your RDBMS to have a 1:1 relation between tables, youve more likely made an error in the design of your database