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(mydat aset, "DATABASENA ME")

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]]

Re: da.update (syntax error in UPDATE statement)

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

"Stephen Plotnick" <splotnick@grou pcbf.comschreef in bericht
news:V8Sdnc1ixZ 1IfbzYnZ2dnUVZ_ rCdnZ2d@giganew s.com...

>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(mydat aset, "DATABASENA ME")
>
I do have a primary key on both and copied the code from the working
update for the other two.
>
Anyone have any ideas?
>

[Oenone]

Re: da.update (syntax error in UPDATE statement)

Stephen Plotnick wrote:

The error I'm getting in syntax error on Update on the statement
da.update(mydat aset, "DATABASENA ME")

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. :)

--

(O)enone

[jeff]

Re: da.update (syntax error in UPDATE statement)

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" <splotnick@grou pcbf.comwrote in message
news:V8Sdnc1ixZ 1IfbzYnZ2dnUVZ_ rCdnZ2d@giganew s.com...

>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(mydat aset, "DATABASENA ME")
>
I do have a primary key on both and copied the code from the working
update for the other two.
>
Anyone have any ideas?
>

[Stephen Plotnick]

Re: da.update (syntax error in UPDATE statement)

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.Ole Db.OleDbConnect ion("Provider=M icrosoft.Jet.OL EDB.4.0;Data
source='C:\Prog ram Files\MyArea\Pr icing\Pricing.m db';Persist Security
Info=False")

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

da.SelectComman d = New OleDb.OleDbComm and(uSQL, conn)

Dim cb As New OleDb.OleDbComm andBuilder(da)

Try

myDS.Tables("Lo ad_Tape").GetCh anges()

da.Update(myDS. Tables("Load_Ta pe"))

Catch ex As Exception

MessageBox.Show (ex.ToString)

End Try

End If

"Oenone" <oenone@nowhere .comwrote in message
news:uW%23UwXu5 GHA.4276@TK2MSF TNGP04.phx.gbl. ..

Stephen Plotnick wrote:

>The error I'm getting in syntax error on Update on the statement
>da.update(myda taset, "DATABASENA ME")

>
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. :)
>
--
>
(O)enone
>

[Stephen Plotnick]

Re: da.update (syntax error in UPDATE statement)

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
"Oenone" <oenone@nowhere .comwrote in message
news:uW%23UwXu5 GHA.4276@TK2MSF TNGP04.phx.gbl. ..

Stephen Plotnick wrote:

>The error I'm getting in syntax error on Update on the statement
>da.update(myda taset, "DATABASENA ME")

>
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. :)
>
--
>
(O)enone
>

[Cor Ligthert [MVP]]

Re: da.update (syntax error in UPDATE statement)

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

"Cor Ligthert [MVP]" <notmyfirstname @planet.nlschre ef in bericht
news:uSVvgTq5GH A.3808@TK2MSFTN GP06.phx.gbl...

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
>
"Stephen Plotnick" <splotnick@grou pcbf.comschreef in bericht
news:V8Sdnc1ixZ 1IfbzYnZ2dnUVZ_ rCdnZ2d@giganew s.com...

>>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(myda taset, "DATABASENA ME")
>>
>I do have a primary key on both and copied the code from the working
>update for the other two.
>>
>Anyone have any ideas?
>>

>
>

[Stephen Plotnick]

Re: da.update (syntax error in UPDATE statement)

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
"DollarChan ged" everything worked.

Steve
"Cor Ligthert [MVP]" <notmyfirstname @planet.nlwrote in message
news:OWas1rw5GH A.3736@TK2MSFTN GP02.phx.gbl...

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
>
"Cor Ligthert [MVP]" <notmyfirstname @planet.nlschre ef in bericht
news:uSVvgTq5GH A.3808@TK2MSFTN GP06.phx.gbl...

>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
>>
>"Stephen Plotnick" <splotnick@grou pcbf.comschreef in bericht
>news:V8Sdnc1ix Z1IfbzYnZ2dnUVZ _rCdnZ2d@gigane ws.com...

>>>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(myd ataset, "DATABASENA ME")
>>>
>>I do have a primary key on both and copied the code from the working
>>update for the other two.
>>>
>>Anyone have any ideas?
>>>

>>
>>

>
>

[Oenone]

Re: da.update (syntax error in UPDATE statement)

Stephen Plotnick wrote:

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.SelectComman d = New OleDb.OleDbComm and(uSQL, conn)
Dim cb As New OleDb.OleDbComm andBuilder(da)
cb.QuotePrefix = "["
cb.QuoteSuffix = "]"
[...]
///

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

--

(O)enone