Database insert issue in classic asp

Question

Hello there. I'm getting a syntax error in this statement after I did a test submit on my form:

"Microsoft JET Database Engine error '80040e14'

Syntax error (missing operator) in query expression 'c_name='Golden Wattle' AND sc_name='Acacia pycnantha' AND url='http://anpsa.org.au/a-pyc.html' AND image='http://anpsa.org.au/jpg/029_2.jpg' AND price='$72' AND information='Golden Wattle is Australia's national floral emblem.''.

/courses/benv/2410/2013s2/3420384/exercises/ex05/insert-plant.asp, line 54"

I can't, for the life of me, understand what is wrong here.

  dim cn, sc, url, image, price, desc


  cn=Request.Form("new_cn")
   sc=Request.Form("new_sc")
    url=Request.Form("new_url")
     image=Request.Form("new_image")
      price=Request.Form("new_price")
       desc=Request.Form("new_desc")

  '--- check to see whether there already are items of that name...
  SQL="select ID from PlantTable where c_name='"& cn & "' AND sc_name='" & sc & "'"&_ 
        " AND url='"&url& "' AND image='"&image& "' AND price='"&price& "' AND information='"&desc& "' "
  set info = conn.execute(SQL)

  if info.eof then
    '--- there is no plant of that name at present, so do the insert
    SQL="insert into PlantTable (c_name, sc_name, url, image, price, information) values ('" & cn & "', "&sn&","&url&","&image&","&price&","&desc&")"
    conn.execute(SQL)
    response.write "Insertion completed."
  else
    '--- there is already a plant of that name...
    response.write "Sorry, that Plant Name is already in the database."
  end if

Debugging 101: add a print statement to print out the TSQL statement as sent to database engine... — Mitch Wheat
@MitchWheat thanks for your help. I printed out an SQL statement as sent to the database engine and found an error. The sc variable was written as sn. I changed that. I then got another error saying: Microsoft JET Database Engine error '80040e14' Syntax error in INSERT INTO statement. /courses/benv/2410/2013s2/3420384/exercises/ex05/insert-plant.asp, line 61 I tried printing out the statement for that but no luck so far in finding the error. — chap
Canonical solution to this kind of problem: use parameterized queries. — Ansgar Wiechers

Deepshikha Deepshikha · Accepted Answer · 2013-09-30T05:44:01

Quotation marks are missing in Insert into statement it should be:

SQL="insert into PlantTable (c_name, sc_name, url, image, price, information) values 
('" & cn & "', '"&sn&"', '"&url&"', '"&image&"', '"&price&"', '"&desc&"');"

Also in error message desc "Australia's" has single quotation mark that will delimit the string. To resolve this double the number of single quotation marks inside the variable. Following function can be used:

Public Function ReplaceSingleQuotes(varValue As Variant) As String
    Const SINGLEQUOTE = "'"

    ReplaceSingleQuotes = SINGLEQUOTE & _
                        Replace(varValue, SINGLEQUOTE, SINGLEQUOTE & SINGLEQUOTE) & _
                        SINGLEQUOTE 
End Function

and can be used as:

SQL="insert into PlantTable (c_name, sc_name, url, image, price, information) values 
('" & cn & "', '"&sn&"', '"&url&"', '"&image&"', '"&price&"', '"& ReplaceSingleQuotes(desc)   
&"');"

Database insert issue in classic asp

1 Answers