Update Set Values

  • Thread starter Thread starter RoadKyng
  • Start date Start date
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')"
 
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.
 
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')"
Click to expand...


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# ...
 
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')"
Click to expand...
Click to expand...
 
Back
Top