Writing an update query in asp.net (access database) (visual basic)

Question

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, an exception is thrown, when I run it: Syntax error in UPDATE statement.

I tried to run a similar statement in Access and it works fine.

Answer 1

You are missing SET as part of UPDATE.

It should be UPDATE tablename SET fieldname = ... WHERE [criteria].

On a side note, you are using classic asp style code inside asp.net. I will suggest reading some docs on ASP.net and how to design applications in a layered manner.

Answer 2

I think the missing is SET.

Try: UPDATE table SET field = newvalue WHERE criteria;

Just modify:

sql = "UPDATE tblAccounts SET balance = " & CDbl(balance + value) & " WHERE(accountID = " & accountID & ")"

http://office.microsoft.com/en-us/access/HA100765271033.aspx

Answer 3

A good start is here: Enterprise Library's Data Access Application Block

Link: http://aspnet.4guysfromrolla.com/articles/030905-1.aspx

Answer 4

The SQL Statement definitely is missing the SET keyword. Also, I suggest you to brush up on 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()

This way, not only is the SQL Statment is clearer, it help prevents possible SQL injection attacks.

Answer 5

How do update visual basic programe with acsess? Please show me codes. Thank you

Answer 6

Hi people! I have a similar problem, my query is fine, It runs fine in Access but keeps giving exception (update syntax is wrong!) from within C# ... can anyone pls check this link out? I have asked my question here :) thanks in adv

http://stackoverflow.com/questions/371246/update-query-works-fine-when-run-from-access-but-raises-sql-syntax-error-from-w