Avatar of aspnewbie09
aspnewbie09
 asked on

syntax error in INSERTfor MS Access database

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:
Microsoft AccessMicrosoft IIS Web Server

Avatar of undefined
Last Comment
Paul Jackson

8/22/2022 - Mon
SOLUTION
Paul Jackson

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
peter57r

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Paul Jackson

You don't appear to be populating the variables you use in the values part of the statement either you are putting them in session variables instead.
aspnewbie09

ASKER
none of the above work.  i change my database field name and modify Savant's code as follow:

objCommand = New OleDbCommand("INSERT INTO tblProduction(pSubject, pFrom, pCategory, pComments) VALUES ('" & txtSubject.Text & "', '" & txtFrom.Text & "', '" & DropDownList1.Text & "','" & txtComments.Text & "')", dbConn)

It now INSERT into my database.  How come tthe code does not work, it simply store the variable into a session variable so my code won't look so messy:

'grab session variable
        Session("psSubject") = txtSubject.Text
        Session("psFrom") = txtFrom.Text
        Session("psCategory") = DropDownList1.Text
        Session("psComments") = txtComments.Text

Thanks

Paul Jackson

Does it now insert into your database?
What code does not work ?

Use DropDownList1.SelectedValue rather than DropDownList1.Text
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
aspnewbie09

ASKER
yes, it does INSERT into my database.

i simply wanted to know why this:  Session("psSubject") = txtSubject.Text

the session ("psSubject") does not store the txtsubject.text so that i can use psSubject instead of txtsubject.text when inserting into the database.
Paul Jackson

If you want to use the values stored in session then you have to refer to them like :

Session("psSubject")

so :

objCommand = New OleDbCommand("INSERT INTO tblProduction(pSubject, pFrom, pCategory, pComments) VALUES ('" & Session("psSession") & "', '" & Session("psFrom") & "', '" & Session("psCategory") & "','" & Session("Comments") & "')", dbConn)