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.

Joan Pham · Accepted Answer · 2008-12-06 07:30:33Z

1

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

answered Dec 6, 2008 at 7:30

Joan Pham

485 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

alextansc · Accepted Answer · 2008-12-06 14:25:43Z

1

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.

answered Dec 6, 2008 at 14:25

alextansc

4,6826 gold badges33 silver badges47 bronze badges

Comments

shahkalpesh · Accepted Answer · 2008-12-06 07:25:34Z

0

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.

answered Dec 6, 2008 at 7:25

shahkalpesh

33.6k4 gold badges66 silver badges89 bronze badges

1 Comment

Gezim Over a year ago

Thanks a lot. Regarding proper design, do you have a URL? Or even what's a good book on the subject? Thanks.

Joan Pham · Accepted Answer · 2008-12-06 07:36:16Z

0

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

Link: https://web.archive.org/web/20210612110113/https://aspnet.4guysfromrolla.com/articles/030905-1.aspx

answered Dec 6, 2008 at 7:36

Joan Pham

485 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related