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

H

HDokes

Active member
Joined
Jun 28, 2003
Messages
32
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 objects 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.
 
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:

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
 
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 .NETs form database wizard. Seems odd it wouldnt work out of the box a s it were.
 
Originally posted by 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 .NETs form database wizard. Seems odd it wouldnt work out of the box a s it were.
Click to expand...

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:
Code: 
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 "

Thats where your problem is orginating from.

Jon
 
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. Shouldnt the wizard have allowed for this?

The question is, what commands should I have placed in here?
 
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:Dont 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.
 
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 = ?
 
Are we having fun yet????? I have performed the same creation of a data form using 2 tables and Ill be darned if I havent 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)
 
Oh great now I have to get the lawn cut before the rains comes in. Heres an old quote from a previous post to start the explanation.
Originally posted by jfackler
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.
Click to expand...

And, a past thread with some more info to help perhaps.
http://www.computerhelp.forum/showthread.php?s=&threadid=72691&perpage=5&pagenumber=1

Ill check back later....keep after it.

Jon
 
Hi Jon,

I been at all day... storm came in shortly after you headed out so there wasnt 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.... :)
 
Tenacity is, in some ways, its 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
 
Back
Top