da.update (syntax error in UPDATE statement)

[Stephen Plotnick]

I have three forms and update one table in an Access DB with sucess in one
form.

In the other two forms I'm trying to do an update to a different table in
the Access DB.

The error I'm getting in syntax error on Update on the statement
da.update(mydataset, "DATABASENAME")

I do have a primary key on both and copied the code from the working update
for the other two.

Anyone have any ideas?

 

[Cor Ligthert [MVP]]

Stephen,

I think that there is something wrong in your code, however we cannot see
what, you have that before your eyes and you cannot even find it with that
code, how do you think we could do that without that code?

Cor

 

[Oenone]

The error I'm getting in syntax error on Update on the statement
da.update(mydataset, "DATABASENAME")

Are you using a CommandBuilder object? And do your tables, or any of the
fields within them, contain spaces or characters other than A-Z, 0-9 or
underscore? If so, it's possible that you need to provide delimiters around
the table/field names. For example, this is not a valid Access UPDATE
statement:

\\\
update my table
set my field = 'some value'
///

....but this is:

\\\
update [my table]
set [my field] = 'some value'
///

To fix this up, set properties of the CommandBuilder object as follows when
you create it:

.QuotePrefix = "["
.QuoteSuffix = "]"

I've no idea if that'll help but it was worth a shot.

 

[jeff]

Stephen...

i have a design question for you ... you mentioned you ...copied the code
from the working update for the other two...

Does you design approach include 'cut/paste - find and replacement' ? If
so, you may want to reconsider and move this code / logic into a class of
its own to isolate it or else you will be in for a maintenance nightmare ...
what happenes when you have 12 forms that hit the same table, and use same
'update' code ... are you going to cut/paste the code into the other 8
forms? If so, what happens when you need to add 6 more fields to the table
and decide to 'rename' 2 fields? Code maintenance nightmare...

Jeff

 

[Stephen Plotnick]

here is the code. There are only two fields changing and both are currency
types. In a field that is not changing there are symbold like "%" and
quotes, etc.

In the table that is working I do not have that type of data in a text
field.

I'll experiment.



THanks,

Steve

If myDS.HasChanges Then

Dim conn As New
System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
source='C:\Program Files\MyArea\Pricing\Pricing.mdb';Persist Security
Info=False")

Dim uSQL = "select * from Load_Tape WHERE StoreSelected = '" & "X" & "'"

da.SelectCommand = New OleDb.OleDbCommand(uSQL, conn)

Dim cb As New OleDb.OleDbCommandBuilder(da)

Try

myDS.Tables("Load_Tape").GetChanges()

da.Update(myDS.Tables("Load_Tape"))

Catch ex As Exception

MessageBox.Show(ex.ToString)

End Try

End If

The error I'm getting in syntax error on Update on the statement
da.update(mydataset, "DATABASENAME")

Are you using a CommandBuilder object? And do your tables, or any of the
fields within them, contain spaces or characters other than A-Z, 0-9 or
underscore? If so, it's possible that you need to provide delimiters
around the table/field names. For example, this is not a valid Access
UPDATE statement:

\\\
update my table
set my field = 'some value'
///

...but this is:

\\\
update [my table]
set [my field] = 'some value'
///

To fix this up, set properties of the CommandBuilder object as follows
when you create it:

.QuotePrefix = "["
.QuoteSuffix = "]"

I've no idea if that'll help but it was worth a shot.

 

[Stephen Plotnick]

I ended up changing all the Field Names in my data base to remove any spaces
or special characters. I could not find the delimiter option in VB.NET 2003.

THanks for everything,
Steve

The error I'm getting in syntax error on Update on the statement
da.update(mydataset, "DATABASENAME")

Are you using a CommandBuilder object? And do your tables, or any of the
fields within them, contain spaces or characters other than A-Z, 0-9 or
underscore? If so, it's possible that you need to provide delimiters
around the table/field names. For example, this is not a valid Access
UPDATE statement:

\\\
update my table
set my field = 'some value'
///

...but this is:

\\\
update [my table]
set [my field] = 'some value'
///

To fix this up, set properties of the CommandBuilder object as follows
when you create it:

.QuotePrefix = "["
.QuoteSuffix = "]"

I've no idea if that'll help but it was worth a shot.

 

[Cor Ligthert [MVP]]

Stephen,

I know nothing more of it, than that I have the idea that this event is what
you need.

http://msdn2.microsoft.com/en-us/library/system.windows.forms.control.invalidated.aspx

I hope this helps,

Cor

 

[Stephen Plotnick]

Thanks for the help.

It turned out the problem was with the the names of some of my fields in the
Access DB; For exmple one was "$ Change"; when I changed it to
"DollarChanged" everything worked.

Steve

 

[Oenone]

I ended up changing all the Field Names in my data base to remove any
spaces or special characters. I could not find the delimiter option
in VB.NET 2003.

From the code you posted, you need to put them here:

\\\
[...]
da.SelectCommand = New OleDb.OleDbCommand(uSQL, conn)
Dim cb As New OleDb.OleDbCommandBuilder(da)
cb.QuotePrefix = "["
cb.QuoteSuffix = "]"
[...]
///

That should get your SQL working regardless of spaces in table or field
names.