使用自动编号 PK 插入 Access DB 并收到插入语法错误
我正在尝试插入访问数据库，并且 PK 是由数据库生成的自动编号。我收到插入语句的语法错误，但不知道为什么。如果可能的话，我想在同一语句中返回自动编号值。
这是我到目前为止所得到的。
Dim cn As OleDbConnection
Dim cmd As OleDbCommand
Dim str As String
Try
cn = New OleDbConnection("Provider=microsoft.Jet.OLEDB.4.0;Data Source=G:\Sean\BMSBonder3_0.mdb;")
cn.Open()
str = String.Format("Insert into Bonder(BonderName, BonderConfig, Date, User) values ('{0}', '{1}', '{2}', '{3}')", addBonder.BonderName _
, xmlString, Date.Now.ToString, Environment.UserName)
MsgBox(str)
cmd = New OleDbCommand(str, cn)
cmd.ExecuteNonQuery()
cn.Close()
Catch ex As Exception
MsgBox(ex.Message)
Return False
End Try
Return Nothing
显然是在 VB.Net 中编码。在查询中，除自动编号字段外，所有字段值均已设置。
I am trying to insert into a access db and the PK is an autonumber that is generated by the DB. I am getting a syntax error for the insert statement and dont know why. If possible I would like to return the autonumber value in the same statement.
Here is what I got so far.
Dim cn As OleDbConnection
Dim cmd As OleDbCommand
Dim str As String
Try
cn = New OleDbConnection("Provider=microsoft.Jet.OLEDB.4.0;Data Source=G:\Sean\BMSBonder3_0.mdb;")
cn.Open()
str = String.Format("Insert into Bonder(BonderName, BonderConfig, Date, User) values ('{0}', '{1}', '{2}', '{3}')", addBonder.BonderName _
, xmlString, Date.Now.ToString, Environment.UserName)
MsgBox(str)
cmd = New OleDbCommand(str, cn)
cmd.ExecuteNonQuery()
cn.Close()
Catch ex As Exception
MsgBox(ex.Message)
Return False
End Try
Return Nothing
Coding in VB.Net obviously. In the query all the field values are set except the autonumber field.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我敢打赌，问题是因为 Date 和 User 是保留字。将它们括在方括号中：
更好的是，将数据库中的字段重命名为非保留字，以防止进一步的麻烦。
I'd bet dollars to donuts that the problem is because Date and User are reserved words. Enclose them in square brackets:
Better yet, rename the fields in the database to non-reserved words to prevent further headaches.
在该行上放置一个断点
，然后将 str 的值复制到 Access 查询窗口，然后从那里运行查询。 Access 查询窗口可能会查明问题。
如果将日期插入 Access，则需要在日期的两侧添加哈希值。所以值：
将是：
Put a breakpoint on the line
and copy the value of str out to the Access query window and run the query from there. The Access query window may pinpoint the problem.
You will need to put a hash either side of your date if inserting dates into Access. So the value:
will be:
除了硬编码的注释之外，Access 中日期的分隔符是哈希（我现在看到 hawbsl 提到了这一点）：
要获取数字，您需要在同一连接上使用第二条语句：
In addition to hardcode's comments, the delimiter for dates in Access is hash (I see now that this was mentioned by hawbsl):
To get the number, you need a second statement on the same connection: