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? 8 8179
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" <sp*******@groupcbf.comschreef in bericht
news:V8******************************@giganews.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(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?
Stephen Plotnick wrote:
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. :)
--
(O)enone
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" <sp*******@groupcbf.comwrote in message
news:V8******************************@giganews.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(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?
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=Micros oft.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
"Oenone" <oe****@nowhere.comwrote in message
news:uW****************@TK2MSFTNGP04.phx.gbl...
Stephen Plotnick wrote:
>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. :)
--
(O)enone
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" <oe****@nowhere.comwrote in message
news:uW****************@TK2MSFTNGP04.phx.gbl...
Stephen Plotnick wrote:
>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. :)
--
(O)enone
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/lib...validated.aspx
I hope this helps,
Cor
"Cor Ligthert [MVP]" <no************@planet.nlschreef in bericht
news:uS**************@TK2MSFTNGP06.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" <sp*******@groupcbf.comschreef in bericht
news:V8******************************@giganews.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(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?
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
"Cor Ligthert [MVP]" <no************@planet.nlwrote in message
news:OW**************@TK2MSFTNGP02.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/lib...validated.aspx
I hope this helps,
Cor
"Cor Ligthert [MVP]" <no************@planet.nlschreef in bericht
news:uS**************@TK2MSFTNGP06.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" <sp*******@groupcbf.comschreef in bericht news:V8******************************@giganews.co m...
>>>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?
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.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.
--
(O)enone This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Robert Mark Bram |
last post by:
Hi All!
I have the following two methods in an asp/jscript page - my problem is that
without the update statement there is no error, but with the update
statement I get the following error:
...
|
by: Dave |
last post by:
I have 2 tables, one with names, and another with addresses, joined by their
CIVICID number (unique to the ADDRESSINFO table) in Oracle.
I need to update a field in the NAMEINFO table for a...
|
by: S.Patten |
last post by:
Hi,
I have a problem with updating a datetime column,
When I try to change the Column from VB I get "Incorrect syntax near
'942'" returned from
'942' is the unique key column value
...
|
by: Lauren Quantrell |
last post by:
In VBA, I constructed the following to update all records in
tblmyTable with each records in tblmyTableTEMP having the same
UniqueID:
UPDATE
tblMyTable RIGHT JOIN tblMyTableTEMP ON...
|
by: Falco Vermeer |
last post by:
Hi,
I tried to use the following query to update a value in one table with
a value from another table:
UPDATE tbl1
SET col1 = tbl2.col2
FROM tbl1, tbl2
WHERE tbl1. = tbl2.
| |
by: kalamos |
last post by:
This statement fails
update ded_temp a
set a.balance = (select sum(b.ln_amt)
from ded_temp b
where a.cust_no = b.cust_no
and a.ded_type_cd = b.ded_type_cd
and a.chk_no = b.chk_no
group by...
|
by: HeadScratcher |
last post by:
I am trying to speed up my update statements by removing inner select
statements.
Example:
update orders set shipname = (select contactName from
customers where customerid = orders.customerID)...
|
by: amitbadgi |
last post by:
HI i am getting the foll error while conv an asp application to
asp.net
Exception Details: System.Runtime.InteropServices.COMException: Syntax
error in UPDATE statement.
Source Error:
Line...
|
by: Al |
last post by:
Hi,
I need to update tables in access 97. The table names have spaces (not my
choice). My update fails even though I use the OleDbCommandBuilder.
Here is a code I am using
myDataAdapter = New...
|
by: FayeC |
last post by:
I really need help figuring this out.
i have a db with mostly text fields but 2.
The user_id field is an autonumber (key) and the user_newsletter is a
number (1 and 0) field meaning 1 yes the ...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |