Update Set Values

R

RoadKyng

Quesiton on using parameters in an Update SQL statement.

Trying to update a table in Access 2003 from an ASP.NET form using a SQL
statement. Example as follows


"UPDATE [My tbl] SET (Field1, Field2, Field3, Field4) VALUES ( 'data1',
'data2', 'data3', 'data4') WHERE RecordID = " & txtRecordID.Text

I get a Syntax error with no idea on where it is. I have tried plugging this
in to the Access database as a Query and get the same - Syntax Error.
Interestingly, this method works fine as an

"INSERT INTO (Field1, Field2, Field3, Field4) VALUES ( 'data1', 'data2',
'data3', 'data4')"
 
D

Danny J. Lesandrini

GmH

You really haven't posted any information of substance. What exactly is the
error message? Just "Syntax Error"?

What data-types are the fields? The table name contains a space in your
example. Do the field names too? If so, they'll need square brackets. Is the
RecordID a number or text field? Is your text box populated? This SQL will
completely break if it's NULL.
 
D

Dirk Goldgar

RoadKyng said:
Quesiton on using parameters in an Update SQL statement.

Trying to update a table in Access 2003 from an ASP.NET form using a SQL
statement. Example as follows


"UPDATE [My tbl] SET (Field1, Field2, Field3, Field4) VALUES ( 'data1',
'data2', 'data3', 'data4') WHERE RecordID = " & txtRecordID.Text

I get a Syntax error with no idea on where it is. I have tried plugging
this
in to the Access database as a Query and get the same - Syntax Error.
Interestingly, this method works fine as an

"INSERT INTO (Field1, Field2, Field3, Field4) VALUES ( 'data1', 'data2',
'data3', 'data4')"


The UPDATE statement has a completely different syntax from the INSERT
statement. For an update, you would use this:

"UPDATE [My tbl] SET Field1 = 'data1', Field2 = 'data2', Field3 =
'data3', Field4 = 'data4' WHERE RecordID = " & txtRecordID.Text

I note that your quoting of the values implies that all the fields are text
or memo fields. If a field is numeric, then you don't quote the value:


... SET Field1 = 1234 ...

and if it is a date field, you need to specify a date literal:

.... SET Field2 = #3/26/2009# ...
 
R

RoadKyng

My Apologies.

The Syntax error is vague "Syntax error in SQL Expression"

The RecordID field must be populated for the code to run. (this code is
connected to an If/Then upstream.

My real question should have been one of syntax for the Update statement.
Mr. Goldgar has pointed out the difference between the Insert Into and Update
methods. I was hoping to use parameters set in a Values stream but it appears
I must set individually the field followed by the value.

Thank You for your help
--
GmH


Danny J. Lesandrini said:
GmH

You really haven't posted any information of substance. What exactly is the
error message? Just "Syntax Error"?

What data-types are the fields? The table name contains a space in your
example. Do the field names too? If so, they'll need square brackets. Is the
RecordID a number or text field? Is your text box populated? This SQL will
completely break if it's NULL.
--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


RoadKyng said:
Quesiton on using parameters in an Update SQL statement.

Trying to update a table in Access 2003 from an ASP.NET form using a SQL
statement. Example as follows


"UPDATE [My tbl] SET (Field1, Field2, Field3, Field4) VALUES ( 'data1',
'data2', 'data3', 'data4') WHERE RecordID = " & txtRecordID.Text

I get a Syntax error with no idea on where it is. I have tried plugging this
in to the Access database as a Query and get the same - Syntax Error.
Interestingly, this method works fine as an

"INSERT INTO (Field1, Field2, Field3, Field4) VALUES ( 'data1', 'data2',
'data3', 'data4')"
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top