Writing an update request in asp.net (access database) (visual basic)
I have this code:
Dim pathString As String = HttpContext.Current.Request.MapPath("Banking.mdb")
Dim odbconBanking As New OleDbConnection _
("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" + pathString)
Dim sql As String
sql = "UPDATE tblAccounts balance = " & CDbl(balance + value) & " WHERE(accountID = " & accountID & ")"
odbconBanking.Open()
Dim cmd As New OleDbCommand(sql, odbconBanking)
cmd.ExecuteNonQuery()
However, on startup, an exception is thrown: Syntax error in UPDATE statement.
I tried running a similar statement in Access and it works great.
I think SET is missing.
Try: table UPDATE SET field = new value WHERE criteria;
Just change:
sql = "UPDATE tblAccounts SET balance = " & CDbl(balance + value) & " WHERE(accountID = " & accountID & ")"
http://office.microsoft.com/en-us/access/HA100765271033.aspx
The SQL statement is definitely missing the SET keyword. Also, I suggest you set up a parameterized query :
Dim sql As String = "UPDATE tblAccounts " & _
"SET balance = ? " & _
"WHERE(accountID = ?)"
Dim cmd As New OleDbCommand(sql, odbconBanking)
cmd.Parameters.Add("Balance", CDbl(balance + value))
cmd.Parameters.Add("AccountId", accountID
cmd.ExecuteNonQuery()
In this way, not only the clarity of the SQL Statment, but also prevents possible SQL injection attacks.
You are missing SET as part of the UPDATE.
It should be UPDATE tablename SET fieldname = ... WHERE [criteria].
On the side of the note, you are using the classic asp style inside asp.net. I suggest reading some docs on ASP.net and how to design layered applications.
Good start here: Application Application Application Application Enterprise Library
Link: http://aspnet.4guysfromrolla.com/articles/030905-1.aspx