Updating record using MS Access

[manasi]

Hi !

I am working on VB.NET using MS Access database...
After creating table in MS Access, and successfully inserting some records in it, I have been facing problem while updating the records., it is showing 2 errors as "ABC" is not declared, and End of statement expected.

The code is as follows :-

Public con As OleDbConnection
Public cmd As OleDbCommand
Public adp As OleDbDataAdapter
Public drd As OleDbDataReader
Public dset As New DataSet
'*******************
Try
con = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\mydsn.mdb;")
con.Open()
query = "Update ObjectSel SET userName= '" & ABC &"' where userName = '" & XYZ & "'"
cmd = New OleDb.OleDbCommand(query, con)
b =cmd.ExecuteNonQuery()
MsgBox("Record updated successfully")
con.close()

Catch ex As Exception
MsgBox(ex.ToString)
End Try


I would really appreciate if anyone can sort out my problem..

Thanks,
Manasi

 

[vis781]

query = "Update ObjectSel SET userName= '" & ABC &"' where userName = '" & XYZ & "'"

String concatenation in SQL in not advised and regularly causes errors that can be otherwise avoided. The problem with that string is trivial but seeing as i'm going to show you the other way to do it there's no point in saying wahts wrong with it...

query = "Update ObjectSel SET userName= ? where userName = ?"
cmd = [SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDb.OleDbCommand(query, con)
cmd.Parameters.AddWithValue("@userName", "ABC")[/SIZE]
cmd.Parameters.AddWithValue("@userName", "XYZ")

 

[manasi]

Thanks vis781 !

It has worked out man !!

Really appreciate !