Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Posts
    4

    Unanswered: Help with sql statement in asp.

    I have a very simple sql statement that is being created with a text variable transferred in via the URL.

    Here is the error:

    Microsoft OLE DB Provider for ODBC Drivers error '80040e10'

    [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

    /admin/DBMaint/DBMod2Test.asp, line 19

    Line 19 is rs.open sqlstmt, connectme

    Here is a URL with domain removed:

    http://www.xxxx.com/admin/DBMaint/DB...asp?ID=Amadeus

    Here is the code that creates it:

    <%
    ' use request.querystring to pass the ID
    ' number from the previous page to the
    ' sql statement so we get the right record
    Cultivar = request.querystring ("ID")
    response.write Cultivar
    set rs=Server.CreateObject("adodb.Recordset")

    connectme="DSN=flowerdata"
    ' note how the Cultivar name is added to the sql
    'sqlstmt = "SELECT * from Flowers WHERE Cultivar = 'Alaqua'"
    sqlstmt = "SELECT * from Flowers WHERE Cultivar =" &[Cultivar]
    response.write sqlstmt
    rs.open sqlstmt, connectme
    ' now get all the variables from the database

    The commented out sql statement works, the second one that attempts to use a variable does not.

    The output of the response.writes are as follows:
    response.write Cultivar should display the value in URL

    Amadeus

    And it does...

    response.write sqlstmt should display the sql statement, and it does

    SELECT * from Flowers WHERE Cultivar =Amadeus


    I used the brackets since some of the Cultivar variables will contain a space. I had this working fine passing a number variable, but I can't make it work with text.

    Any help would be great

    Thanks Matt
    Last edited by mattw; 02-06-04 at 20:18.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    make sure there are single quotes around the value
    Code:
    sqlstmt = "SELECT * from Flowers WHERE Cultivar = '" &[Cultivar]&"'"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Posts
    4
    Would you tell me the purpose of both &'s? And all of the " and '.

    I am not a newbie, but I have not had any formal training in this stuff and this is the first time I have had to deal with this situation.

    BTW, it did work fine.

    Thanks matt

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the &s are used for concatenating strings in asp

    the doublequotes are used to delimit strings in asp

    the single quotes are part of the sql statement itself, and are used to delimit a string value in sql

    as far as asp is concerned, the single quotes are characters inside the strings it is concatenating

    the single quotes are needed in sql so that whatever is in the Cultivar variable is considered to be a string

    ... WHERE Cultivar = 'Amadeus' -- 'Amadeus' is a string

    ... WHERE Cultivar = Amadeus -- Amadeus has to be a column

    in the latter example, since Amadeus is not a column, you get an error
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Posts
    4
    Once again.

    A great big thanks.

    Matt

  6. #6
    Join Date
    Feb 2002
    Posts
    2,232
    Matt,

    Check out a couple of these books:

    Beginning ASP Databases
    Beginning Active Server Pages 3.0.

    These are well written and will assist you in your development.
    If you do not have SQL Server Books Online (BOL) installed - please do so. The majority of questions asked in the SQL Server forum could be eliminated if people had access to bol. BOL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •