Error Inserting Data in Access Database [OleDbException (0x80040e14)

[seanya]

I am trying to simply insert a row into an Access Database. I can query data fine but inserting doesn't work. I get the following error:
-----------------------------------------------------
Syntax error in INSERT INTO statement.
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 15: cmdInsert.ExecuteNonQuery()

Stack Trace:

[OleDbException (0x80040e14): Syntax error in INSERT INTO statement.]
System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr) +41
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) +154
System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) +92
System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) +65
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +112
System.Data.OleDb.OleDbCommand.ExecuteNonQuery() +54
ASP.Register_aspx.btnInsert_OnClick(Object Sender, EventArgs E) in \\genfs3\

http://www26\seanya\bin\register.aspx:15

System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain() +1263
-----------------------------------------------------

Below is my code:
-----------------------------------------------------


<%@ Page Language=&quot;VB&quot; Debug=&quot;true&quot; %>
<%@ Import Namespace=&quot;System.Data&quot; %>
<%@ Import NameSpace=&quot;System.Data.OleDb&quot; %>

<script language=&quot;VB&quot; runat=&quot;server&quot;>
Sub btnInsert_OnClick(Sender As Object, E As EventArgs)
Dim myConnection As OleDbConnection
Dim cmdInsert as OleDbCommand
Dim strInsert as String

myConnection = New OleDbConnection(&quot;Provider=Microsoft.Jet.OLEDB.4.0; Data Source=&quot; & Server.MapPath(&quot;database.mdb&quot & &quot;;&quot
strInsert = &quot;INSERT INTO USERS (USER_NAME, PASSWORD) VALUES ('bob', 'bobby')&quot;
cmdInsert = New OleDbCommand(strInsert, myConnection)
myConnection.Open()
cmdInsert.ExecuteNonQuery()
myConnection.Close()
End Sub
</script>

-----------------------------------------------------

Any help with this would be GREATLY appreciated. I'm completely stuck here.

Thank you!
-Sean

 

[jfrost10]

Hey Sean,

I whipped up a dummy access db with a users table, and the insert statement itself isn't bunk (bob and bobby showed up fine).

Have you double checked your table/column syntax to make sure the names in the db are the same in the insert statement?

Are you sure that its looking in the right spot for the database?

Are you sure you wouldn't rather be using SQL Server instead of LackSense...er, Access?
(sorry, SQL mark here)

Seriously though, I'd dig around your db a bit and see if the problem doesn't pop out. Your syntax looks fine to me.

jack

 

[seanya]

It seems that the insert works as long as I'm not inserting a field with the name &quot;password&quot;. It will let me insert any other field. This is definately weird. Thanks for your help Jack!

-Sean

 

[jfrost10]

OH, of course!

I forgot that Access and SQL Server both have reserved words that can't be used!

Silly me...

Good job trouble shooting it Sean


Jack