Update requires a valid UpdateComand when passed DataRow collection iwth modified row

[HDokes]

Greetings,

 

I have used the .NET 2003 "Add Inherited Form" "Data form wizard" to create a form which uses two tables from an Access 2000 database. The tables are relational in nature with a 1 to many relationship. The wizard creates the form with no issues and loads the data accordingly however when I attempt an update I receive the message provided in the subject line above.

 

I have seen another thread on this forum with this same message however I believe that my approach is a bit differenct than the gentleman in the other thread.

 

I have re-created this form using two tables, a table and a query, and a query, in every instance I receive the same message.

 

The following represents the 'update' code for this particular form:

 

Public Sub UpdateDataSet()

'Create a new dataset to hold the changes that have been made to the main dataset.

Dim objDataSetChanges As SoundFX.dstButtonTemplate = New SoundFX.dstButtonTemplate

'Stop any current edits.

Me.BindingContext(objdstButtonTemplate, "Button Template Table").EndCurrentEdit()

Me.BindingContext(objdstButtonTemplate, "Button Assignment Table").EndCurrentEdit()

'Get the changes that have been made to the main dataset.

objDataSetChanges = CType(objdstButtonTemplate.GetChanges, SoundFX.dstButtonTemplate)

'Check to see if any changes have been made.

If (Not (objDataSetChanges) Is Nothing) Then

Try

'There are changes that need to be made, so attempt to update the datasource by

'calling the update method and passing the dataset and any parameters.

Me.UpdateDataSource(objDataSetChanges)

objdstButtonTemplate.Merge(objDataSetChanges)

objdstButtonTemplate.AcceptChanges()

Catch eUpdate As System.Exception

'Add your error handling code here.

Throw eUpdate

End Try

'Add your code to check the returned dataset for any errors that may have been

'pushed into the row object's error.

End If

 

End Sub

 

I am pariculary confused as there has been no alteration in every method tried to the actual code generated by the Wizard.

[jfackler]

The error is on the .update method not in your UpdateDataSet sub you have written. Do you have a sub called UpdateDataSource as per the line of code:

 

'There are changes that need to be made, so attempt to update the datasource by
'calling the update method and passing the dataset and any parameters.
Me.UpdateDataSource(objDataSetChanges)

 

that calls a DataAdapters update method?

 

Jon

[HDokes]

Hi Jon,

 

Yes.... here is the UpdateDataSource Routine.

 

Public Sub UpdateDataSource(ByVal ChangedRows As SoundFX.dstButtonTemplate)

Try

'The data source only needs to be updated if there are changes pending.

If (Not (ChangedRows) Is Nothing) Then

'Open the connection.

Me.OleDbConnection1.Open()

'Attempt to update the data source.

OleDbDataAdapter1.Update(ChangedRows)

OleDbDataAdapter2.Update(ChangedRows)

End If

Catch updateException As System.Exception

'Add your error handling code here.

Throw updateException

Finally

'Close the connection whether or not the exception was thrown.

Me.OleDbConnection1.Close()

End Try

 

End Sub

 

As I stated, this is code that is auto generated by .NET's form database wizard. Seems odd it wouldn't work 'out of the box' a s it were.

[jfackler]

Hi Jon,

 

Yes.... here is the UpdateDataSource Routine.

 

Public Sub UpdateDataSource(ByVal ChangedRows As SoundFX.dstButtonTemplate)

Try

'The data source only needs to be updated if there are changes pending.

If (Not (ChangedRows) Is Nothing) Then

'Open the connection.

Me.OleDbConnection1.Open()

'Attempt to update the data source.

OleDbDataAdapter1.Update(ChangedRows)

OleDbDataAdapter2.Update(ChangedRows)

End If

Catch updateException As System.Exception

'Add your error handling code here.

Throw updateException

Finally

'Close the connection whether or not the exception was thrown.

Me.OleDbConnection1.Close()

End Try

 

End Sub

 

As I stated, this is code that is auto generated by .NET's form database wizard. Seems odd it wouldn't work 'out of the box' a s it were.

 

I agree, seems odd, but, the command builder that is probably being utilized to generate your update command leaves some to be desired.

 

Find the OleDbDataAdapter1 and 2 update command objects.

Will look something like:

'OleDbUpdateCommand 
       '
       Me.OleDbUpdateCommand1.CommandText = "UPDATE PayDates SET PayDate = ? WHERE (PayDate = ?)"
       Me.OleDbUpdateCommand1.Connection = Me.OleDbConnection1
       Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("PayDate", System.Data.OleDb.OleDbType.VarWChar, 100, "PayDate"))
       Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_PayDate", System.Data.OleDb.OleDbType.VarWChar, 100, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "PayDate", System.Data.DataRowVersion.Original, Nothing))

 

in the #Region " Windows Form Designer generated code "

 

That's where your problem is orginating from.

 

Jon

[HDokes]

Ok... a bit of discovery, I have checked both OleDbDataAdapter objects and it is clear there is no UpdateCommand in their respective 'update command' properties. Shouldn't the wizard have allowed for this?

 

The question is, what commands should I have placed in here?

[HDokes]

hehehe... posted the last one before I realized you posted yours...

 

As I look through the code... there are entries for the 'select' and 'insert' options but none for 'update'

 

Here is the select and insert for the 1st adapter....

 

'OleDbSelectCommand1

'

Me.OleDbSelectCommand1.CommandText = "SELECT ButtonIDSet, TemplateID, TemplateName FROM [button Template Table]"

Me.OleDbSelectCommand1.Connection = Me.OleDbConnection1

'

'OleDbConnection1

'

Me.OleDbConnection1.ConnectionString = "Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database L" & _

"ocking Mode=1;Jet OLEDB:Database Password=;Data Source=""C:\Sound Effects System\" & _

"SoundFX\SoundSystem.mdb"";Password=;Jet OLEDB:Engine Type=5;Jet OLEDB:Global Bulk" & _

" Transactions=1;Provider=""Microsoft.Jet.OLEDB.4.0"";Jet OLEDB:System database=;Je" & _

"t OLEDB:SFP=False;Extended Properties=;Mode=Share Deny None;Jet OLEDB:New Databa" & _

"se Password=;Jet OLEDB:Create System Database=False;Jet OLEDB:Don't Copy Locale " & _

"on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;User ID=Admin;Je" & _

"t OLEDB:Encrypt Database=False"

'

'OleDbInsertCommand1

'

Me.OleDbInsertCommand1.CommandText = "INSERT INTO [button Template Table] (ButtonIDSet, TemplateName) VALUES (?, ?)"

Me.OleDbInsertCommand1.Connection = Me.OleDbConnection1

Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("ButtonIDSet", System.Data.OleDb.OleDbType.Integer, 0, "ButtonIDSet"))

Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("TemplateName", System.Data.OleDb.OleDbType.VarWChar, 50, "TemplateName"))

 

'OleDbDataAdapter1

'

Me.OleDbDataAdapter1.InsertCommand = Me.OleDbInsertCommand1

Me.OleDbDataAdapter1.SelectCommand = Me.OleDbSelectCommand1

Me.OleDbDataAdapter1.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "Button Template Table", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("ButtonIDSet", "ButtonIDSet"), New System.Data.Common.DataColumnMapping("TemplateID", "TemplateID"), New System.Data.Common.DataColumnMapping("TemplateName", "TemplateName")})})

Me.OleDbDataAdapter1.UpdateCommand = Me.OleDbCommand1

 

This seems to be all the references to the updatecommand.

[jfackler]

Beautiful day here in Cincinnati, opportunity to take the kids to the pool.... I'll check back later.

 

In the mean time, follow this link...

 

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/CommandBuilder.asp

 

Anyone else want add your experience/expertise?

 

 

Jon

[HDokes]

I think I am just about there....

 

I have created an Update command for both tables and initialized them through the program. My last hurdle seems to be with the actual command text ..... here is what I have thus far tho I can not determine how to state the 'WHERE' statement to allow the values to 'update' the datasource

 

UPDATE [button Assignment Table]

SET ButtonTemplateNumber = ?, ButtonTitle = ?, ClipName = ?, ButtonID = ?

[HDokes]

Are we having fun yet????? I have performed the same creation of a data form using 2 tables and I'll be darned if I haven't gotten 3 different results in the wizard.... sometimes it will add the delete/update statements to one table and not the other... sometimes no statements.... no rhyme or reason...

 

... in an UDATE statement like the following... what does the "=?" mean. I am assuming it means "equals anything" however the added 'or' statements with their "IS NULL" would have me wondering why would care that they are... if a change was made in the dataset.... then update the dern thing...

 

UPDATE [button Template Table]

SET ButtonIDSet = ?, TemplateID = ?, TemplateName = ?

WHERE (TemplateID = ?) AND (ButtonIDSet = ? OR

? IS NULL AND ButtonIDSet IS NULL) AND (TemplateName = ? OR

? IS NULL AND TemplateName IS NULL)

[HDokes]

Oh... hey Jon.... rain is heading your way.... did I mention I'm in Indianapolis? :)

[jfackler]

Oh great now I have to get the lawn cut before the rains comes in. Here's an old quote from a previous post to start the explanation.

The OLE DB .NET Provider does not support named parameters for passing parameters to a SQL Statement or a stored procedure called by an OleDbCommand when CommandType is set to Text. In this case, the question mark (?) placeholder must be used. For example:

 

SELECT * FROM Customers WHERE CustomerID = ?

 

As a result, the order in which OleDbParameter objects are added to the OleDbParameterCollection must directly correspond to the position of the question mark placeholder for the parameter.

 

And, a past thread with some more info to help perhaps.

http://www.xtremedotnettalk.com/showthread.php?s=&threadid=72691&perpage=5&pagenumber=1

 

I'll check back later....keep after it.

 

Jon

[HDokes]

Hi Jon,

 

I been at all day... storm came in shortly after you headed out so there wasn't much need to go find something else to do. I finally overcame my road blocks here. The jury is still out as to the effectiveness of this .NET environment. Seems to make things much more difficult then the need to be.

 

In the end, I found that the wizards seen to be highly unstable. That is to say, you can run the wizard twice in a row with the same information and get different results. In this case, even though it supplied the controls to insert, delete, and update the wizard did not always create the runtime code to allow the buttons to work. Hit or miss it seams.

 

Anyway.... was able to figure out how to go through the properties of each DataAdapter and enter in the information for the missing routines... i.e. in seemed to drop 'delete' and 'update' if it was gonna drop anything. The insert command always came through.

 

Now... after updating the record set my grid adds additional line (duplicates of what is there already)... I suspect this is an issue of positioning in some fashion but have not been able to determine what. If I close the form than reopen it, the additions are there and the duplicate lines are gone so it seems to be just a screen thing.

 

I really appreciate your assistance. The combination of that and tenacity has prevailed..... I hate tenacity.... :)

[jfackler]

Tenacity is, in some ways, it's own reward. What you learn in the struggle will go a long way toward solving the next dilemma. The .NET framework is worth the effort....keep at it.

You might try a .clear or an .acceptchanges to clear up that duplication issue.

 

Looking forward to the rain.

 

Jon