oleDataAdapter Error on Update (vb)

[Rich]

Hello,

I get an error message when I try to update an
oleDBdataAdapter: "Syntax error in Update statement"
Here is what I have:

for this project I am using all data components from the
toolbox (instead of just writing code for them - VB 2002),
an oleDBconnection, oleDBdataAdapter, Dataset. These all
appear to work fine for loading data and displaying on a
form. I am connecting to an Access mdb. 10 rows of data
5 fields from tbl1 in Access.

In the dataset component, ds1, I named the table dtTbl1
("tbl1" in Access). I then bind each textbox on the form
to a field in ds1. The data displays correctly on the
form and I can cycle through all the records. Then I have
an Update button on the form. The code where I get the
error is this in the button click event:
__________________________________________
Cursor.Current = Cursors.Default
Try
OleDbDataAdapter1.Update(ds1, "tbl1")
Catch ex As Exception
MsgBox(ex.Message)
End Try
___________________________________________

If I modify the data in a textbox and click the update
button there are no messages. But if I move to another
record and then click the update button, I get the error
message - syntax error in update statement. And the data
does not update in the Access mdb.

Here is my Update Commandtext (from the property sheet):

UPDATE tbl1 SET fld1 = ?, fld2 = ?, fld3 = ?, fld4 = ?,
fld5 = ?

I am guessing that I need to replace the ? with
something. Do I put the ds1 field names here or the
textbox names? or do I leave this alone?

Thanks,
Rich

 

[William Ryan eMVP]

Rich:

At first glance, everything looks kosher, I'd just make sure that the
commandtype isn't set to stored proc (it should be set correctly by default
so if you didn't specify this, that's not the problem) and that the
parameters collection is being populated correctly. What does the code
populating the Parameters look like?

 

[Rich]

Good Question. I believe I am not populating the params -
probably the problem (I hope). My params are

fld1, fld2, fld3, fld4, fld5

The textboxes are txt1, txt2, txt3,txt4, txt5

So since the textboxes are bound to the fields, the data
shows up automatically. And I cycle through the rows with
the currency manager position property. What I noticed
was that the dataadapter seems to call an update method to
update the table with either an insert, update or delete
from the dataset. So how do I populate the params? and
how do I specify if I am updating, inserting or deleting a
row with the data adapter?

I know how to populate the items in a datarow var

Dim dRow = ds1.Tables("tbl1").Rows(i)
dRow(0) = txt1.Text
dRow(1) = txt2.Text
....
This seems to work.

Or
dRow.Delete()
Adapter1.Update(ds1,"tbl1")

But I keep getting the syntax error.

What am I missing?

Thanks for getting back to me.

Rich

 

[William Ryan eMVP]

Rich, looks like the command's parameters collection isn't being populated.
See what Command.Parameters.Count.ToSTring gives you. You can bind to the
data in the table, but for the update to work, the parameters collection
needs populated . Te verify this, check the count (B/c your update
statement is specifying parameters with the ?) and if it's greater than 0
make sure the number matches the number of ?'s. Just to help diagnose the
problem, use a writeline for each of the params values and see what we get.
Let me know and we'll be able to figure it out from there.

Bill

 

[Rich]

Thank you for this suggestion. Turns out that

Adapter.UpdateCommand.Parameters.count

is returning 5 on the console.WriteLine

Update tbl1 Set fld1=?,fld2=?,fld3=?,fld4=?,fld5=?

I was hoping to cycle through these params to see what
their values are. Unfortunately, I am a little weak on
VB.net right now. I was hoping to do something like

dim p as Paramter
For Each p in Adapter.UpdateCommand.Parameters
console.writeline(p.value)
Next

I found Dim p as ParameterArrayAttribut but had a casting
error problem in the for each loop. I will guess that my
params are not getting populated. How can I populate them?

Thanks,
Rich

 

[Rich]

My last post was a little lame. I figured out how to
iterate through the parameter collection of my adapter.

For i = 0 To
OleDbDataAdapter1.UpdateCommand.Parameters.Count - 1
Console.WriteLine(Adapter1.UpdateCommand.Parameters
(i).Value)
Next

I was able to iterate through all the params and sure
enough, they did not contain any values. How to
populate? But even if they didn't have any values would
that set tbl1 to all empty strings? Why do I get a syntax
error on the update statement?

I may be a little weak on VB.net, but I have been writing
classic VB(6) for several years (and also been writing
java for about 1 1/2 years). I use com ADO daily with Sql
Server2K. I am just trying to migrate to ADO.net.

 

[Ron Allen]

Rich,
How are your parameters defined? They should each be attached to a
field in your DataRow. Also I don't see a where clause on your update
statement. This should be checking for the primary key field to locate the
record to update in the database.

Ron Allen

My last post was a little lame. I figured out how to
iterate through the parameter collection of my adapter.

For i = 0 To
OleDbDataAdapter1.UpdateCommand.Parameters.Count - 1
Console.WriteLine(Adapter1.UpdateCommand.Parameters
(i).Value)
Next

I was able to iterate through all the params and sure
enough, they did not contain any values. How to
populate? But even if they didn't have any values would
that set tbl1 to all empty strings? Why do I get a syntax
error on the update statement?

I may be a little weak on VB.net, but I have been writing
classic VB(6) for several years (and also been writing
java for about 1 1/2 years). I use com ADO daily with Sql
Server2K. I am just trying to migrate to ADO.net.

----------------snip------------------