By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,290 Members | 1,204 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,290 IT Pros & Developers. It's quick & easy.

Please help : System.Data.OleDb.OleDbException: Syntax error in IN

P: n/a
Joe
Hello All,

I am trying to insert a record in the MS Access DB and for some reason I cannot get rid of error message,

System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement.

And the line it shows in red is

cmd.ExecuteNonQuery()

I have pasted the entire code here. Can someone please give me some clue as what could be wrong. The SQL string looks fine because I pasted the resulting SQL in to MS Access. When I ran the Insert query, it properly added the record in the Access DB.

Thanks,

Joe

<%@ Page Language="VB" Debug="true" ContentType="text/html" ResponseEncoding="iso-8859-1" %>
<%@ Import Namespace="System.Data.OleDb" %>
<%@ Import Namespace="System.Data" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<%
'Open up a connection to Access database
'Using a DSN connection.
Dim bolfFound, strUsername, bolAlreadyExists
Dim objConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; data source='E:/Inetpub/databases/investors.mdb'")
objConn.Open()

'check state
If Session("strAdmin") <> "test" Then
objConn.Close()
objConn = Nothing
Response.Write("<A HREF=index.aspx'>")
Response.Write("Sorry, looks like your session timed out, please login again.")
Response.Write("</A>")
Response.End()
End If

bolAlreadyExists = False

Dim objDataReader as OledbDataReader
Dim objCommand as New OledbCommand("Select * From Results", objConn)
objDataReader = objCommand.ExecuteReader()

Do While Not (objDataReader.Read()= False OR bolAlreadyExists)
If (StrComp(objDataReader("Email"), Request.Form("Email"), vbTextCompare) = 0) Then
Response.Redirect("record_exists.aspx")
bolAlreadyExists = True
End If
Loop

objDataReader.Close()

If Not bolAlreadyExists Then

Dim Email, passwd, first_name, last_name, company, street_address, address2, city, prov, country, postal, phone, mobilePhone, AddDate,Investor, RemoteIP
Email = Request.Form("Email")
passwd = Request.Form("password")
first_name = Request.Form("first_name")
last_name = Request.Form("last_name")
company = Request.Form("company")
street_address = Request.Form("street_address")
address2 = Request.Form("address2")
city = Request.Form("city")
prov = Request.Form("state")
country = Request.Form("country")
postal = Request.Form("postal")
phone = Request.Form("phone")
mobilePhone = Request.Form("mobile")
AddDate = Now
Inv = "Yes"
RemoteIP = Request.ServerVariables("REMOTE_ADDR")

Dim MySQL as String

MySQL = "INSERT INTO Results(email, password, first_name, last_name, company, street_address, address2, city, state, country, postal, phone, mobile, AddDate, Inv, RemoteIP)" & _
" VALUES('" & Email & "', '" & passwd & "', '" & first_name & "', '" & last_name & "', '" & company & "', '" & street_address & "', '" & address2 & "', '" & city & "', '" & prov & "', '" & country & "', '" & postal & "', '" & phone & "', '" & mobilePhone & "', '" & AddDate & "', '" & Investor & "', '" & RemoteIP & "')"

Dim cmd as New OleDBCommand (MySQL, objConn)

cmd.ExecuteNonQuery ()
End if

objConn = Nothing
objConn.Close()

%>

Nov 18 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
I believe 'password' is a keyword - but it is also your column name. Try
putting [ and ] around the column name.

Alsok you shouldn't just concatenate strings together given to you by the
user. They could easily put in malicious SQL for one of those values. I
would recommend using parameters.

"Joe" <Jo*@discussions.microsoft.com> wrote in message
news:46**********************************@microsof t.com...
Hello All,

I am trying to insert a record in the MS Access DB and for some reason I cannot get rid of error message,
System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement.

And the line it shows in red is

cmd.ExecuteNonQuery()

I have pasted the entire code here. Can someone please give me some clue as what could be wrong. The SQL string looks fine because I pasted the
resulting SQL in to MS Access. When I ran the Insert query, it properly
added the record in the Access DB.
Thanks,

Joe

<%@ Page Language="VB" Debug="true" ContentType="text/html" ResponseEncoding="iso-8859-1" %> <%@ Import Namespace="System.Data.OleDb" %>
<%@ Import Namespace="System.Data" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<%
'Open up a connection to Access database
'Using a DSN connection.
Dim bolfFound, strUsername, bolAlreadyExists
Dim objConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; data source='E:/Inetpub/databases/investors.mdb'") objConn.Open()

'check state
If Session("strAdmin") <> "test" Then
objConn.Close()
objConn = Nothing
Response.Write("<A HREF=index.aspx'>")
Response.Write("Sorry, looks like your session timed out, please login again.") Response.Write("</A>")
Response.End()
End If

bolAlreadyExists = False

Dim objDataReader as OledbDataReader
Dim objCommand as New OledbCommand("Select * From Results", objConn)
objDataReader = objCommand.ExecuteReader()

Do While Not (objDataReader.Read()= False OR bolAlreadyExists)
If (StrComp(objDataReader("Email"), Request.Form("Email"), vbTextCompare) = 0) Then Response.Redirect("record_exists.aspx")
bolAlreadyExists = True
End If
Loop

objDataReader.Close()

If Not bolAlreadyExists Then

Dim Email, passwd, first_name, last_name, company, street_address, address2, city, prov, country, postal, phone, mobilePhone, AddDate,Investor,
RemoteIP Email = Request.Form("Email")
passwd = Request.Form("password")
first_name = Request.Form("first_name")
last_name = Request.Form("last_name")
company = Request.Form("company")
street_address = Request.Form("street_address")
address2 = Request.Form("address2")
city = Request.Form("city")
prov = Request.Form("state")
country = Request.Form("country")
postal = Request.Form("postal")
phone = Request.Form("phone")
mobilePhone = Request.Form("mobile")
AddDate = Now
Inv = "Yes"
RemoteIP = Request.ServerVariables("REMOTE_ADDR")

Dim MySQL as String

MySQL = "INSERT INTO Results(email, password, first_name, last_name, company, street_address, address2, city, state, country, postal, phone,
mobile, AddDate, Inv, RemoteIP)" & _ " VALUES('" & Email & "', '" & passwd & "', '" & first_name & "', '" & last_name & "', '" & company & "', '" & street_address & "', '" & address2 &
"', '" & city & "', '" & prov & "', '" & country & "', '" & postal & "', '"
& phone & "', '" & mobilePhone & "', '" & AddDate & "', '" & Investor & "',
'" & RemoteIP & "')"
Dim cmd as New OleDBCommand (MySQL, objConn)

cmd.ExecuteNonQuery ()
End if

objConn = Nothing
objConn.Close()

%>

Nov 18 '05 #2

P: n/a
Yep..that got me a few times. Also Joe, be careful of using Date as a column name as well, I know you didn't use it here but I had to find out the hard way. It will cause the same type of error. The Jet Provider is picky about those kinds of words

"Marina" wrote:
I believe 'password' is a keyword - but it is also your column name. Try
putting [ and ] around the column name.

Alsok you shouldn't just concatenate strings together given to you by the
user. They could easily put in malicious SQL for one of those values. I
would recommend using parameters.

"Joe" <Jo*@discussions.microsoft.com> wrote in message
news:46**********************************@microsof t.com...
Hello All,

I am trying to insert a record in the MS Access DB and for some reason I

cannot get rid of error message,

System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement.

And the line it shows in red is

cmd.ExecuteNonQuery()

I have pasted the entire code here. Can someone please give me some clue

as what could be wrong. The SQL string looks fine because I pasted the
resulting SQL in to MS Access. When I ran the Insert query, it properly
added the record in the Access DB.

Thanks,

Joe

<%@ Page Language="VB" Debug="true" ContentType="text/html"

ResponseEncoding="iso-8859-1" %>
<%@ Import Namespace="System.Data.OleDb" %>
<%@ Import Namespace="System.Data" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"

"http://www.w3.org/TR/html4/loose.dtd">
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<%
'Open up a connection to Access database
'Using a DSN connection.
Dim bolfFound, strUsername, bolAlreadyExists
Dim objConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; data

source='E:/Inetpub/databases/investors.mdb'")
objConn.Open()

'check state
If Session("strAdmin") <> "test" Then
objConn.Close()
objConn = Nothing
Response.Write("<A HREF=index.aspx'>")
Response.Write("Sorry, looks like your session timed out, please login

again.")
Response.Write("</A>")
Response.End()
End If

bolAlreadyExists = False

Dim objDataReader as OledbDataReader
Dim objCommand as New OledbCommand("Select * From Results", objConn)
objDataReader = objCommand.ExecuteReader()

Do While Not (objDataReader.Read()= False OR bolAlreadyExists)
If (StrComp(objDataReader("Email"), Request.Form("Email"), vbTextCompare)

= 0) Then
Response.Redirect("record_exists.aspx")
bolAlreadyExists = True
End If
Loop

objDataReader.Close()

If Not bolAlreadyExists Then

Dim Email, passwd, first_name, last_name, company, street_address,

address2, city, prov, country, postal, phone, mobilePhone, AddDate,Investor,
RemoteIP
Email = Request.Form("Email")
passwd = Request.Form("password")
first_name = Request.Form("first_name")
last_name = Request.Form("last_name")
company = Request.Form("company")
street_address = Request.Form("street_address")
address2 = Request.Form("address2")
city = Request.Form("city")
prov = Request.Form("state")
country = Request.Form("country")
postal = Request.Form("postal")
phone = Request.Form("phone")
mobilePhone = Request.Form("mobile")
AddDate = Now
Inv = "Yes"
RemoteIP = Request.ServerVariables("REMOTE_ADDR")

Dim MySQL as String

MySQL = "INSERT INTO Results(email, password, first_name, last_name,

company, street_address, address2, city, state, country, postal, phone,
mobile, AddDate, Inv, RemoteIP)" & _
" VALUES('" & Email & "', '" & passwd & "', '" & first_name & "', '" &

last_name & "', '" & company & "', '" & street_address & "', '" & address2 &
"', '" & city & "', '" & prov & "', '" & country & "', '" & postal & "', '"
& phone & "', '" & mobilePhone & "', '" & AddDate & "', '" & Investor & "',
'" & RemoteIP & "')"

Dim cmd as New OleDBCommand (MySQL, objConn)

cmd.ExecuteNonQuery ()
End if

objConn = Nothing
objConn.Close()

%>


Nov 18 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.