troubleshooting Question

syntax error in INSERTfor MS Access database

Avatar of aspnewbie09
aspnewbie09 asked on
Microsoft AccessMicrosoft IIS Web Server
7 Comments2 Solutions350 ViewsLast Modified:
Got syntax error on my INSERT statement.

here's my form:

<form id="form1" runat="server">
    <div>
   
       <br />
        Subject:&nbsp;&nbsp;&nbsp;&nbsp;         <asp:TextBox ID="txtSubject" runat="server" Width="408px"></asp:TextBox>
        <br />
        From:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
        <asp:TextBox ID="txtFrom" runat="server" Width="408px"></asp:TextBox>
        <br />
       
        Category:&nbsp;&nbsp;
   
       <asp:DropDownList ID="DropDownList1" runat="server">
            <asp:ListItem>Option A</asp:ListItem>
            <asp:ListItem>Option B</asp:ListItem>
            <asp:ListItem>Option D</asp:ListItem>
            <asp:ListItem>Option E</asp:ListItem>
        </asp:DropDownList>
        <br />
        Comments:
        <asp:TextBox ID="txtComments" runat="server" TextMode="MultiLine" Width="412px"></asp:TextBox>
        <br />
        <br />
        <asp:Button ID="btnPost" runat="server" Text="Post" Width="125px" />
&nbsp;
        <asp:Button ID="btnReset" runat="server" Text="Reset" Width="90px" />
   
    </div>
    </form>

My code in aspx:

Imports System.Data.OleDb
Imports System.Data
Imports System.Data.Odbc
Imports System.Text
Partial Class production_sourcing
    Inherits System.Web.UI.Page
    Dim dbConn As OleDbConnection   'connection
    Dim objCommand As OleDbCommand  'holding a command object
    Dim dbReader As OleDbDataReader 'reader
    Dim psSubject, psFrom, psCategory, psComments As String

 Protected Sub btnPost_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnPost.Click
        'grab session variable
        Session("psSubject") = txtSubject.Text
        Session("psFrom") = txtFrom.Text
        Session("psCategory") = DropDownList1.Text
        Session("psComments") = txtComments.Text

        Dim dbConn As System.Data.OleDb.OleDbConnection
        dbConn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\inetpub\wwwroot\MillData.mdb; User Id=admin; Password=;")
        dbConn.Open()

        objCommand = New OleDbCommand("INSERT INTO tblProduction(Subject, From, Category, Comments)VALUES (psSubject, psFrom, psCategory, psComments)", dbConn)
        objCommand.ExecuteNonQuery()
        dbConn.Close()

    End Sub

 Protected Sub btnReset_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnReset.Click
        txtSubject.Text = ""
        txtFrom.Text = ""
        DropDownList1.Text = ""
        txtComments.Text = ""
    End Sub


Error msg:

System.Data.OleDb.OleDbException was unhandled by user code
  ErrorCode=-2147217900
  Message="Syntax error in INSERT INTO statement."
  Source="Microsoft JET Database Engine"
  StackTrace:
       at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
       at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
       at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
       at production_sourcing.btnPost_Click(Object sender, EventArgs e) in C:\Users\Bai\Documents\Visual Studio 2008\Legsource\production-sourcing.aspx.vb:line 55
       at System.Web.UI.WebControls.Button.OnClick(EventArgs e)
       at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)
       at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
       at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
       at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
       at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
  InnerException:
ASKER CERTIFIED SOLUTION
peter57r

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros