Syntax error in INSERT INTO statement

Discussion in 'ASP General' started by Gérard Leclercq, Feb 1, 2005.

  1. ACCESS
    First fields are TEXT, last 2 are Numbers
    The name of the fields are correct.


    Dim MyConn
    Set MyConn=Server.CreateObject("ADODB.Connection")
    MyConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath
    ("/xdata/sc.mdb")

    Sql = "INSERT INTO sc (" & _
    "session,sessionLanguage,sessionProductReferentie," & _
    "sessionProductGroep,sessionProductSubGroep," & _
    "sessionProductTaal1,sessionProductTaal2,sessionProductTaal3," & _
    "sessionProductAantal,sessionProductWKP)" & _
    " VALUES (" & _
    Chr(34) & Application("id") & Chr(34) & "," & _
    Chr(34) & Request.form("taal") & Chr(34) & "," & _
    Chr(34) & Request.form("referentie") & Chr(34) & "," & _
    Chr(34) & Request.form("groep") & Chr(34) & "," & _
    Chr(34) & Request.form("subgroep") & Chr(34) & "," & _
    Chr(34) & Replace(Request.form("taal1"),Chr(34)," ") & Chr(34) & "," & _
    Chr(34) & Replace(Request.form("taal2"),Chr(34)," ") & Chr(34) & "," & _
    Chr(34) & Replace(Request.form("taal3"),Chr(34)," ") & Chr(34) & "," & _
    Request.form("aantal") & "," & _
    Replace(Request.form("wkp"),",",".") & ")"

    MyConn.Execute sql
    Set MyConn=Nothing

    If i print the SQL string it looks like:

    INSERT INTO sc
    (session,sessionLanguage,sessionProductReferentie,sessionProductGroep,sessionProductSubGroep,sessionProductTaal1,sessionProductTaal2,sessionProductTaal3,sessionProductAantal,sessionProductWKP)
    VALUES
    ("81.165.236.141-1/02/2005-20:00:34-804105034","N","22102","22","10HA","GEKLOVEN
    RIET","ROSEAU PAR 1/2 KG","",1,9.8)

    Microsoft JET Database Engine error '80040e14'
    Syntax error in INSERT INTO statement.

    Can somebody point me what i'm doing wrong?

    Thx, Gérard.
     
    Gérard Leclercq, Feb 1, 2005
    #1
    1. Advertising

  2. Gérard Leclercq

    Paxton Guest

    Gérard Leclercq wrote:
    > ACCESS
    > First fields are TEXT, last 2 are Numbers
    > The name of the fields are correct.
    >
    >
    > Dim MyConn
    > Set MyConn=Server.CreateObject("ADODB.Connection")
    > MyConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &

    Server.MapPath
    > ("/xdata/sc.mdb")
    >
    > Sql = "INSERT INTO sc (" & _
    > "session,sessionLanguage,sessionProductReferentie," & _
    > "sessionProductGroep,sessionProductSubGroep," & _
    > "sessionProductTaal1,sessionProductTaal2,sessionProductTaal3," & _
    > "sessionProductAantal,sessionProductWKP)" & _
    > " VALUES (" & _
    > Chr(34) & Application("id") & Chr(34) & "," & _
    > Chr(34) & Request.form("taal") & Chr(34) & "," & _
    > Chr(34) & Request.form("referentie") & Chr(34) & "," & _
    > Chr(34) & Request.form("groep") & Chr(34) & "," & _
    > Chr(34) & Request.form("subgroep") & Chr(34) & "," & _
    > Chr(34) & Replace(Request.form("taal1"),Chr(34)," ") & Chr(34) &

    "," & _
    > Chr(34) & Replace(Request.form("taal2"),Chr(34)," ") & Chr(34) &

    "," & _
    > Chr(34) & Replace(Request.form("taal3"),Chr(34)," ") & Chr(34) &

    "," & _
    > Request.form("aantal") & "," & _
    > Replace(Request.form("wkp"),",",".") & ")"
    >
    > MyConn.Execute sql
    > Set MyConn=Nothing
    >
    > If i print the SQL string it looks like:
    >
    > INSERT INTO sc
    >

    (session,sessionLanguage,sessionProductReferentie,sessionProductGroep,sessionProductSubGroep,sessionProductTaal1,sessionProductTaal2,sessionProductTaal3,sessionProductAantal,sessionProductWKP)

    > VALUES
    >

    ("81.165.236.141-1/02/2005-20:00:34-804105034","N","22102","22","10HA","GEKLOVEN

    > RIET","ROSEAU PAR 1/2 KG","",1,9.8)
    >
    > Microsoft JET Database Engine error '80040e14'
    > Syntax error in INSERT INTO statement.
    >
    > Can somebody point me what i'm doing wrong?
    >
    > Thx, Gérard.



    Text fields in Access should be delimited by single quotes, not double
    quotes.

    Paxton
     
    Paxton, Feb 1, 2005
    #2
    1. Advertising

  3. Gérard Leclercq

    Mark Schupp Guest

    Use single quotes to delimit your character strings.

    ('81.165.236.141-1/02/2005-20:00:34-804105034',

    If that doesn't help try removing columns from the statement to see which
    one the system doesn't like.
    --
    --Mark Schupp
    Head of Development
    Integrity eLearning
    www.ielearning.com

    "Gérard Leclercq" <> wrote in message
    news:7vQLd.699$-ops.be...
    > ACCESS
    > First fields are TEXT, last 2 are Numbers
    > The name of the fields are correct.
    >
    >
    > Dim MyConn
    > Set MyConn=Server.CreateObject("ADODB.Connection")
    > MyConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
    > Server.MapPath ("/xdata/sc.mdb")
    >
    > Sql = "INSERT INTO sc (" & _
    > "session,sessionLanguage,sessionProductReferentie," & _
    > "sessionProductGroep,sessionProductSubGroep," & _
    > "sessionProductTaal1,sessionProductTaal2,sessionProductTaal3," & _
    > "sessionProductAantal,sessionProductWKP)" & _
    > " VALUES (" & _
    > Chr(34) & Application("id") & Chr(34) & "," & _
    > Chr(34) & Request.form("taal") & Chr(34) & "," & _
    > Chr(34) & Request.form("referentie") & Chr(34) & "," & _
    > Chr(34) & Request.form("groep") & Chr(34) & "," & _
    > Chr(34) & Request.form("subgroep") & Chr(34) & "," & _
    > Chr(34) & Replace(Request.form("taal1"),Chr(34)," ") & Chr(34) & "," & _
    > Chr(34) & Replace(Request.form("taal2"),Chr(34)," ") & Chr(34) & "," & _
    > Chr(34) & Replace(Request.form("taal3"),Chr(34)," ") & Chr(34) & "," & _
    > Request.form("aantal") & "," & _
    > Replace(Request.form("wkp"),",",".") & ")"
    >
    > MyConn.Execute sql
    > Set MyConn=Nothing
    >
    > If i print the SQL string it looks like:
    >
    > INSERT INTO sc
    > (session,sessionLanguage,sessionProductReferentie,sessionProductGroep,sessionProductSubGroep,sessionProductTaal1,sessionProductTaal2,sessionProductTaal3,sessionProductAantal,sessionProductWKP)
    > VALUES
    > ("81.165.236.141-1/02/2005-20:00:34-804105034","N","22102","22","10HA","GEKLOVEN
    > RIET","ROSEAU PAR 1/2 KG","",1,9.8)
    >
    > Microsoft JET Database Engine error '80040e14'
    > Syntax error in INSERT INTO statement.
    >
    > Can somebody point me what i'm doing wrong?
    >
    > Thx, Gérard.
    >
    >
    >
    >
    >
     
    Mark Schupp, Feb 1, 2005
    #3
  4. Double Quotes are not the reason, if have a lot of INSERTs with "". If i
    alter the string without the fields it works fine. So i think, have not find
    time this morning to test, that maybe the field 'session' is a reservate
    word?? Gérard.

    PS: Double quotes are for the namy Last Names with ' in my region, like
    D'Haese or D'Hondt.

    >> Sql = "INSERT INTO sc VALUES (" & _
    >> Chr(34) & Application("id") & Chr(34) & "," & _
    >> Chr(34) & Request.form("taal") & Chr(34) & "," & _
    >> Chr(34) & Request.form("referentie") & Chr(34) & "," & _
    >> Chr(34) & Request.form("groep") & Chr(34) & "," & _
    >> Chr(34) & Request.form("subgroep") & Chr(34) & "," & _
    >> Chr(34) & Replace(Request.form("taal1"),Chr(34)," ") & Chr(34) & "," & _
    >> Chr(34) & Replace(Request.form("taal2"),Chr(34)," ") & Chr(34) & "," & _
    >> Chr(34) & Replace(Request.form("taal3"),Chr(34)," ") & Chr(34) & "," & _
    >> Request.form("aantal") & "," & _
    >> Replace(Request.form("wkp"),",",".") & ")"
    >>
     
    Gérard Leclercq, Feb 2, 2005
    #4
  5. Gérard Leclercq wrote:
    > Double Quotes are not the reason, if have a lot of INSERTs with "". If i
    > alter the string without the fields it works fine. So i think,
    > have not find time this morning to test, that maybe the field
    > 'session' is a reservate word?? Gérard.


    http://aspfaq.com/show.asp?id=2080

    A good test is to open your database in Access, create a new query in design
    view without choosing a table from the dialog, switch to SQL View, paste
    your sql statement in and try to run it. If it runs with no problem, then
    you probably have a reserved keyword issue.

    You should consider using a saved parameter query so you don't have to worry
    about delimiters and apostrophes:
    http://www.google.com/groups?hl=en&...selm= Barrows--Microsoft MVP - ASP/ASP.NETPlease reply to the newsgroup. This email account is my spam trap so Idon't check it very often. If you must reply off-line, then remove the"NO SPAM"
     
    Bob Barrows [MVP], Feb 2, 2005
    #5
  6. Thx Bob, as i suspected 'session'.
    The other link gives me and error to the page for the saved parameters.
    Gérard.

    "Bob Barrows [MVP]" <> schreef in bericht
    news:...
    > Gérard Leclercq wrote:
    >> Double Quotes are not the reason, if have a lot of INSERTs with "". If i
    >> alter the string without the fields it works fine. So i think,
    >> have not find time this morning to test, that maybe the field
    >> 'session' is a reservate word?? Gérard.

    >
    > http://aspfaq.com/show.asp?id=2080
    >
    > A good test is to open your database in Access, create a new query in
    > design view without choosing a table from the dialog, switch to SQL View,
    > paste your sql statement in and try to run it. If it runs with no problem,
    > then you probably have a reserved keyword issue.
    >
    > You should consider using a saved parameter query so you don't have to
    > worry about delimiters and apostrophes:
    > http://www.google.com/groups?hl=en&...selm=
    > Barrows--Microsoft MVP - ASP/ASP.NETPlease reply to the newsgroup. This
    > email account is my spam trap so Idon't check it very often. If you must
    > reply off-line, then remove the"NO SPAM"
    >
     
    Gérard Leclercq, Feb 2, 2005
    #6
  7. Try this:
    http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=

    Bob

    Gérard Leclercq wrote:
    > Thx Bob, as i suspected 'session'.
    > The other link gives me and error to the page for the saved
    > parameters. Gérard.
    >


    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows [MVP], Feb 2, 2005
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. compuglobalhypermeganetz0r

    adapter update problem Syntax error in INSERT INTO statement.

    compuglobalhypermeganetz0r, Dec 8, 2003, in forum: ASP .Net
    Replies:
    0
    Views:
    854
    compuglobalhypermeganetz0r
    Dec 8, 2003
  2. Saber
    Replies:
    5
    Views:
    547
    Jo Inferis
    Jul 20, 2004
  3. Neil Zanella
    Replies:
    3
    Views:
    681
    MWells
    Jan 25, 2005
  4. Nathan Sokalski

    SQL syntax error in INSERT INTO statement

    Nathan Sokalski, Jul 1, 2005, in forum: ASP .Net
    Replies:
    5
    Views:
    14,723
    ahmed_samir
    Sep 3, 2010
  5. solomon_13000

    -2147217900:Syntax error in INSERT INTO statement.

    solomon_13000, Oct 2, 2006, in forum: ASP General
    Replies:
    1
    Views:
    286
    Bob Barrows [MVP]
    Oct 2, 2006
Loading...

Share This Page