CODEWITHSUNDEEP
asp.netvb.netms-access
Gezim
Gezim

Reputation: 7328

Writing an update query 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, 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.

Upvotes: 1

Views: 19866

Answers (4)

alextansc
alextansc

Reputation: 4672

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.

Upvotes: 1

Joan Pham
Joan Pham

Reputation: 48

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

Upvotes: 0

Joan Pham
Joan Pham

Reputation: 48

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

Upvotes: 1

shahkalpesh
shahkalpesh

Reputation: 33484

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.

Upvotes: 0

Related Questions