如何使用 ASP.NET OLEDB 将行添加到数据库?
我对 ASP.NET 和使用 OLEDB 有点陌生。我正在尝试向 ACCESS 数据库中的表添加一行。该代码首先读取用户已有的行数,如果未超过限制,则允许他们提交新行。我已经设置了一个数据集,看起来没问题。问题是,当我尝试将新行更新回数据库时,Insert 命令会生成一个空白命令,这不符合 Access-Database 中的要求。我对自己做错的事情感到有点迷失。
If Page.IsValid = True Then
objConnection = New OleDbConnection(data_vars.dbConnString())
objConnection.Open()
strSQL = "SELECT * FROM tickets WHERE Created_by = " + CStr(Session("ID")) + " AND Status = 0"
objAdapter = New OleDbDataAdapter(strSQL, objConnection)
objAdapter.Fill(objDataSet, "tickets")
If objDataSet.Tables("tickets").Rows.Count < CInt(ConfigurationManager.AppSettings("max_new_tickets")) And CInt(Session("access")) <= 1 Then
'things are good so make ticket
objTable = objDataSet.Tables("tickets")
objRow = objTable.NewRow()
objRow("name") = Server.HtmlEncode(txtTName.Text)
objRow("description") = Replace(Server.HtmlEncode(txtTDesc.Text), vbCrLf, "<br />")
objRow("contact_pref") = Replace(Server.HtmlEncode(txtContactPref.Text), vbCrLf, "<br />")
objRow("log") = "New ticket created on " + CStr(Now()) + " from " + Server.HtmlEncode(Request.ServerVariables("REMOTE_ADDR")) + " with " + Server.HtmlEncode(Request.ServerVariables("HTTP_USER_AGENT")) + "<br />"
objTable.Rows.Add(objRow)
'generate the update commands
Dim objBuilder As New OleDbCommandBuilder(objAdapter)
objAdapter.UpdateCommand = objBuilder.GetUpdateCommand()
objAdapter.InsertCommand = objBuilder.GetInsertCommand()
objAdapter.DeleteCommand = objBuilder.GetDeleteCommand()
'debug ---
lblError.Text = lblError.Text + "<br/>" + objBuilder.GetInsertCommand.CommandText
dgDebug.DataSource = objTable.DefaultView
dgDebug.DataBind()
'----
'update the database
objAdapter.Update(objDataSet, "tickets")
Else
'customer (access level) exceeded new ticket max
lblError.Text = "<b>Cannot create ticket.</b><br /> You currently have the maximum amount of newly opened tickets allowed. Please wait until a tech reviews some of your tickets before making new ones."
End If
objConnection.Close()
End If
我收到的错误是:
描述:执行当前 Web 请求期间发生未处理的异常。请查看堆栈跟踪以获取有关错误及其在代码中的来源的更多信息。
异常详细信息:System.Data.OleDb.OleDbException:INSERT INTO 语句中存在语法错误。
来源错误: 第 58 行:objAdapter.Update(objDataSet, "tickets")
I'm kind of new to ASP.NET and using OLEDB. I'm trying to add a row to a table in an ACCESS database. The code first reads how many rows a user already has and if they're not over the limit, it allows them to submit a new row. I've set up a dataset and that seems to be fine. The problem is when I try to update the new row back into the database the Insert command makes a blank command, which fails the requirements in Access-Database. I'm kind of lost at what I'm doing wrong.
If Page.IsValid = True Then
objConnection = New OleDbConnection(data_vars.dbConnString())
objConnection.Open()
strSQL = "SELECT * FROM tickets WHERE Created_by = " + CStr(Session("ID")) + " AND Status = 0"
objAdapter = New OleDbDataAdapter(strSQL, objConnection)
objAdapter.Fill(objDataSet, "tickets")
If objDataSet.Tables("tickets").Rows.Count < CInt(ConfigurationManager.AppSettings("max_new_tickets")) And CInt(Session("access")) <= 1 Then
'things are good so make ticket
objTable = objDataSet.Tables("tickets")
objRow = objTable.NewRow()
objRow("name") = Server.HtmlEncode(txtTName.Text)
objRow("description") = Replace(Server.HtmlEncode(txtTDesc.Text), vbCrLf, "<br />")
objRow("contact_pref") = Replace(Server.HtmlEncode(txtContactPref.Text), vbCrLf, "<br />")
objRow("log") = "New ticket created on " + CStr(Now()) + " from " + Server.HtmlEncode(Request.ServerVariables("REMOTE_ADDR")) + " with " + Server.HtmlEncode(Request.ServerVariables("HTTP_USER_AGENT")) + "<br />"
objTable.Rows.Add(objRow)
'generate the update commands
Dim objBuilder As New OleDbCommandBuilder(objAdapter)
objAdapter.UpdateCommand = objBuilder.GetUpdateCommand()
objAdapter.InsertCommand = objBuilder.GetInsertCommand()
objAdapter.DeleteCommand = objBuilder.GetDeleteCommand()
'debug ---
lblError.Text = lblError.Text + "<br/>" + objBuilder.GetInsertCommand.CommandText
dgDebug.DataSource = objTable.DefaultView
dgDebug.DataBind()
'----
'update the database
objAdapter.Update(objDataSet, "tickets")
Else
'customer (access level) exceeded new ticket max
lblError.Text = "<b>Cannot create ticket.</b><br /> You currently have the maximum amount of newly opened tickets allowed. Please wait until a tech reviews some of your tickets before making new ones."
End If
objConnection.Close()
End If
The error I'm getting is:
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement.
Source Error:
Line 58: objAdapter.Update(objDataSet, "tickets")
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
在我的代码中苦苦挣扎了 2 个小时后,我发现这个错误是由列名中的 Access Database 关键字引起的。
http://support.microsoft.com/kb/286335
重命名了列名称并且有效美好的。
After struggling with my code for 2 hours, I found this error was caused by Access Database keywords in the column names.
http://support.microsoft.com/kb/286335
Renamed the column names and it worked fine.