Whats Wrong with this Update Statement

[dvannoy]

I am trying to update records in an access database.

I get an error when ExecuteNonQuery fires off..

Dim DBCommand As OleDb.OleDbDataAdapter
Dim DBInsert As New OleDb.OleDbCommand()


Dim dbconn As New OleDb.OleDbConnection( _
"Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=" & Server.MapPath("DB.mdb;&quot)

DBInsert.CommandText = "UPDATE Table" & _
"SET Field1 = '" & txtField1.Text & "'" & _
"Field2 = '" & txtField2.Text & "'" & _
"Field3 = '" & txtField3.Text & "'" & _
"Field4 = '" & txtField4.Text & "'" & _
"WHERE KeyID = '" & txtKeyID.Text & "'"

DBInsert.Connection = dbconn
DBInsert.Connection.Open()
DBInsert.ExecuteNonQuery()
DBInsert.Connection.Close()

Thanks in advance

 

[bradlkm]

Looks like you might be missing the commas between your fields. See the edited statement below (commas are in red):

DBInsert.CommandText = "UPDATE Table " & _
"SET Field1 = '" & txtField1.Text & "', " & _
"Field2 = '" & txtField2.Text & "', " & _
"Field3 = '" & txtField3.Text & "', " & _
"Field4 = '" & txtField4.Text & "' " & _
"WHERE KeyID = '" & txtKeyID.Text & "'"

Also note the need for spaces after each line (i.e. "UPDATE Table" was changed to "UPDATE Table &quot. This will allow the SQL to be properly processed.


HTH!
Kevin B.
.Net Programmer

 

[dvannoy]

Thanks bradlkm,


Now i get an error message saying :

No value given for one or more required parameters.

All the fields have data in them..I also took the primary key anyway from the DB..

Any Ideas??

Thanks

 

[bradlkm]

Hmmm. Hard to say. Could you paste in the updated statement? Also, if possible, show the run-time SQL statement that is created (using debug mode, if possible).

Kevin B.
.Net Programmer

 

[Gamera99]

dvannoy
if the field values are from text boxes where forces unknown could type literally anything,
then make sure to do replace statements to get rid of single apostrophes:

DBInsert.CommandText = "UPDATE Table " & _
"SET Field1 = '" & replace(txtField1.Text,"'","''&quot & "', " & _
"Field2 = '" & replace(txtField2.Text,"'","''&quot & "', " & _
"Field3 = '" & replace(txtField3.Text,"'","''&quot & "', " & _
"Field4 = '" & replace(txtField4.Text,"'","''&quot & "' " & _
"WHERE KeyID = '" & txtKeyID.Text & "'"

I'm hoping that the KeyID field is not handtyped by anyone on the screen you are developing this sql from.