Link to home
Start Free TrialLog in
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:
SOLUTION
Avatar of Paul Jackson
Paul Jackson
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Avatar of aspnewbie09
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

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

Use DropDownList1.SelectedValue rather than DropDownList1.Text
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.
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)