Re: Please help : System.Data.OleDb.OleDbException: Syntax error i

From: Tampa .NET Koder (TampaNETKoder_at_discussions.microsoft.com)
Date: 07/14/04 

Date: Wed, 14 Jul 2004 13:18:03 -0700

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" <Joe@discussions.microsoft.com> wrote in message
> news:46E04C27-2E28-4AF8-B67B-B4492B7F2298@microsoft.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()
> >
> > %>
> >
>
>
>

Relevant Pages

  • Re: multiplatform (pocketPC & desktopPC) (Daniel !!)
    ... Friend Versione As String ... Public Sub GetMyConnectionPalmare() ... Dim errorMessages As String ... Private Function GetDS_Desktop(ByVal SQL As String) As DataSet ...
    (microsoft.public.dotnet.framework.compactframework)
  • Re: multiplatform (pocketPC & desktopPC) (Daniel !!)
    ... Friend Versione As String ... Public Sub GetMyConnectionPalmare() ... Dim errorMessages As String ... Private Function GetDS_Desktop(ByVal SQL As String) As DataSet ...
    (microsoft.public.dotnet.framework.compactframework)
  • Re: Performance von SQL-Abfragen auf Exceltabellen
    ... Nun habe ich es mit SQL auf Exceldaten versucht und konnte das ganze ... Performance konstant bei rund 3 Sekunden. ... Dim iColumnCount As Long ... Dim FirstColumnforSQLexists_jn As String 'j if first column for SQL ...
    (microsoft.public.de.excel)
  • Re: SqlDataAdapter.Update()
    ... string-chewing algorithm wouldn't work well in SQL. ... looping over words in the string and parsing them off, ... >> Dim cn As New SqlConnection ... >> understood that the data adapter did something like this inside the ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: search by 1 of 3 combos
    ... I guess I should expand a bit on an example of what you can do with modifying SQL for a search combo... ... , mWhere as string ... Dim mRecordID As Long ... Private Sub cmd_search_Click ...
    (microsoft.public.access.formscoding)