We help IT Professionals succeed at work.

syntax error in INSERTfor MS Access database

348 Views
Last Modified: 2012-05-11
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:
Comment
Watch Question

Paul JacksonSoftware Engineer
CERTIFIED EXPERT
Top Expert 2011
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Paul JacksonSoftware Engineer
CERTIFIED EXPERT
Top Expert 2011

Commented:
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.

Author

Commented:
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 JacksonSoftware Engineer
CERTIFIED EXPERT
Top Expert 2011

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

Use DropDownList1.SelectedValue rather than DropDownList1.Text

Author

Commented:
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 JacksonSoftware Engineer
CERTIFIED EXPERT
Top Expert 2011

Commented:
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)

Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.