在asp.net（访问数据库）中编写更新查询（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.

但是,当我运行它时会抛出异常:UPDATE语句中的语法错误。

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

我尝试在Access中运行类似的语句,它工作正常。

4 个解决方案

#1

I think the missing is SET.

我认为缺少的是SET。

Try: UPDATE table SET field = newvalue WHERE criteria;

尝试:UPDATE表SET字段= newvalue WHERE条件;

Just modify:

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

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

#2

The SQL Statement definitely is missing the SET keyword. Also, I suggest you to brush up on parameterized query:

SQL语句肯定缺少SET关键字。另外,我建议你刷新参数化查询:

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.

这样,不仅SQL Statment更清晰,它还有助于防止可能的SQL注入攻击。

#3

You are missing SET as part of UPDATE.

作为UPDATE的一部分,您缺少SET。

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

它应该是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.

另外,您在asp.net中使用经典的asp样式代码。我建议在ASP.net上阅读一些文档以及如何以分层方式设计应用程序。

#4

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

一个好的开始是:企业库的数据访问应用程序块

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

#1