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: <asp:TextBox ID="txtSubject" runat="server" Width="408px"></asp:TextBo x>
<br />
From: &nb sp; & nbsp;  ;
<asp:TextBox ID="txtFrom" runat="server" Width="408px"></asp:TextBo x>
<br />
Category:
<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:TextBo x>
<br />
<br />
<asp:Button ID="btnPost" runat="server" Text="Post" Width="125px" />
<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.OleDbCon nection
dbConn = New OleDbConnection("Provider= Microsoft. Jet.OLEDB. 4.0; Data Source=c:\inetpub\wwwroot\ MillData.m db; 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.OleDbExc eption was unhandled by user code
ErrorCode=-2147217900
Message="Syntax error in INSERT INTO statement."
Source="Microsoft JET Database Engine"
StackTrace:
at System.Data.OleDb.OleDbCom mand.Execu teCommandT extErrorHa ndling(Ole DbHResult hr)
at System.Data.OleDb.OleDbCom mand.Execu teCommandT extForSing leResult(t agDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCom mand.Execu teCommandT ext(Object & executeResult)
at System.Data.OleDb.OleDbCom mand.Execu teCommand( CommandBeh avior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCom mand.Execu teReaderIn ternal(Com mandBehavi or behavior, String method)
at System.Data.OleDb.OleDbCom mand.Execu teNonQuery ()
at production_sourcing.btnPos t_Click(Ob ject sender, EventArgs e) in C:\Users\Bai\Documents\Vis ual Studio 2008\Legsource\production- sourcing.a spx.vb:lin e 55
at System.Web.UI.WebControls. Button.OnC lick(Event Args e)
at System.Web.UI.WebControls. Button.Rai sePostBack Event(Stri ng eventArgument)
at System.Web.UI.WebControls. Button.Sys tem.Web.UI .IPostBack EventHandl er.RaisePo stBackEven t(String eventArgument)
at System.Web.UI.Page.RaisePo stBackEven t(IPostBac kEventHand ler sourceControl, String eventArgument)
at System.Web.UI.Page.RaisePo stBackEven t(NameValu eCollectio n postData)
at System.Web.UI.Page.Process RequestMai n(Boolean includeStagesBeforeAsyncPo int, Boolean includeStagesAfterAsyncPoi nt)
InnerException:
here's my form:
<form id="form1" runat="server">
<div>
<br />
Subject:
<br />
From: &nb
<asp:TextBox ID="txtFrom" runat="server" Width="408px"></asp:TextBo
<br />
Category:
<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:TextBo
<br />
<br />
<asp:Button ID="btnPost" runat="server" Text="Post" Width="125px" />
<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.OleDbCon
dbConn = New OleDbConnection("Provider=
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.OleDbExc
ErrorCode=-2147217900
Message="Syntax error in INSERT INTO statement."
Source="Microsoft JET Database Engine"
StackTrace:
at System.Data.OleDb.OleDbCom
at System.Data.OleDb.OleDbCom
at System.Data.OleDb.OleDbCom
at System.Data.OleDb.OleDbCom
at System.Data.OleDb.OleDbCom
at System.Data.OleDb.OleDbCom
at production_sourcing.btnPos
at System.Web.UI.WebControls.
at System.Web.UI.WebControls.
at System.Web.UI.WebControls.
at System.Web.UI.Page.RaisePo
at System.Web.UI.Page.RaisePo
at System.Web.UI.Page.Process
InnerException:
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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
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.SelectedValu e rather than DropDownList1.Text
What code does not work ?
Use DropDownList1.SelectedValu
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.
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)
Session("psSubject")
so :
objCommand = New OleDbCommand("INSERT INTO tblProduction(pSubject, pFrom, pCategory, pComments) VALUES ('" & Session("psSession") & "', '" & Session("psFrom") & "', '" & Session("psCategory") & "','" & Session("Comments") & "')", dbConn)