SQL works in Access but not ASP

Discussion in 'ASP General' started by Penny, May 14, 2005.

  1. Penny

    Penny Guest

    Hi all,

    Thanks again guys for the help re Select Case. I might trouble you with
    something else:

    I use this SQL in the Access query builder to return a single AirRate:

    SELECT AirRate FROM tblZoneShippingRates WHERE ZoneName = "Australia"
    AND WeightCategory = "250"

    Works fine but I've tried this in an ASP page:

    rsShipping.Source = "SELECT AirRate FROM tblZoneShippingRates WHERE
    ZoneName = "Australia" AND WeightCategory = "250""

    But always get error messages such as the following:

    Error Type:
    Microsoft VBScript compilation (0x800A0401)
    Expected end of statement
    /PPdemosite/shop_ccard_TEST.asp, line 214, column 82
    rsShipping.Source = "SELECT AirRate FROM tblZoneShippingRates WHERE ZoneName
    = "Australia" AND WeightCategory = "250""
    ----------------------------------------------------------------------------
    -----^
    Error Type:
    Microsoft OLE DB Provider for ODBC Drivers (0x80040E10)
    [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
    /PPdemosite/shop_ccard_TEST.asp, line 218

    B.T.W, the connection and other properties are tested and okay.

    Any ideas,

    Regards

    Penny.
     
    Penny, May 14, 2005
    #1
    1. Advertising

  2. Penny

    MDW Guest

    The problem is that in ASP, string variables are delimited by quotes. The
    parameter values in your SQL statement are surrounded by quotes, and ASP is
    getting confused. Try this:

    1) In the SQL string, use single quotes instead of souble (that's what I
    would do).
    "Penny" wrote:

    rsShipping.Source = "SELECT AirRate FROM tblZoneShippingRates WHERE
    ZoneName = 'Australia"'AND WeightCategory = '250'"

    2) Also, I don't know if you typed it that way in your post, but ASP would
    not tolerate a hard line break in the middle of your string (Access is OK
    with line breaks in SQL statements - ASP is not that way). You do have
    everything on one line in your app, yes?



    > Hi all,
    >
    > Thanks again guys for the help re Select Case. I might trouble you with
    > something else:
    >
    > I use this SQL in the Access query builder to return a single AirRate:
    >
    > SELECT AirRate FROM tblZoneShippingRates WHERE ZoneName = "Australia"
    > AND WeightCategory = "250"
    >
    > Works fine but I've tried this in an ASP page:
    >
    > rsShipping.Source = "SELECT AirRate FROM tblZoneShippingRates WHERE
    > ZoneName = "Australia" AND WeightCategory = "250""
    >
    > But always get error messages such as the following:
    >
    > Error Type:
    > Microsoft VBScript compilation (0x800A0401)
    > Expected end of statement
    > /PPdemosite/shop_ccard_TEST.asp, line 214, column 82
    > rsShipping.Source = "SELECT AirRate FROM tblZoneShippingRates WHERE ZoneName
    > = "Australia" AND WeightCategory = "250""
    > ----------------------------------------------------------------------------
    > -----^
    > Error Type:
    > Microsoft OLE DB Provider for ODBC Drivers (0x80040E10)
    > [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
    > /PPdemosite/shop_ccard_TEST.asp, line 218
    >
    > B.T.W, the connection and other properties are tested and okay.
    >
    > Any ideas,
    >
    > Regards
    >
    > Penny.
    >
    >
    >
     
    MDW, May 14, 2005
    #2
    1. Advertising

  3. Penny

    Nick Guest

    you can also double quotes to obtain the same result. Sometimes you need to
    triple quotes.
    If you use an asp-capable editor (like dreamweaver or html-kit) you can see
    in the code if you are doing it well or not.

    Nick



    "MDW" <> ha scritto nel messaggio
    news:...
    > The problem is that in ASP, string variables are delimited by quotes. The
    > parameter values in your SQL statement are surrounded by quotes, and ASP
    > is
    > getting confused. Try this:
    >
    > 1) In the SQL string, use single quotes instead of souble (that's what I
    > would do).
    > "Penny" wrote:
    >
    > rsShipping.Source = "SELECT AirRate FROM tblZoneShippingRates WHERE
    > ZoneName = 'Australia"'AND WeightCategory = '250'"
    >
    > 2) Also, I don't know if you typed it that way in your post, but ASP would
    > not tolerate a hard line break in the middle of your string (Access is OK
    > with line breaks in SQL statements - ASP is not that way). You do have
    > everything on one line in your app, yes?
    >
    >
    >
    >> Hi all,
    >>
    >> Thanks again guys for the help re Select Case. I might trouble you with
    >> something else:
    >>
    >> I use this SQL in the Access query builder to return a single AirRate:
    >>
    >> SELECT AirRate FROM tblZoneShippingRates WHERE ZoneName = "Australia"
    >> AND WeightCategory = "250"
    >>
    >> Works fine but I've tried this in an ASP page:
    >>
    >> rsShipping.Source = "SELECT AirRate FROM tblZoneShippingRates WHERE
    >> ZoneName = "Australia" AND WeightCategory = "250""
    >>
    >> But always get error messages such as the following:
    >>
    >> Error Type:
    >> Microsoft VBScript compilation (0x800A0401)
    >> Expected end of statement
    >> /PPdemosite/shop_ccard_TEST.asp, line 214, column 82
    >> rsShipping.Source = "SELECT AirRate FROM tblZoneShippingRates WHERE
    >> ZoneName
    >> = "Australia" AND WeightCategory = "250""
    >> ----------------------------------------------------------------------------
    >> -----^
    >> Error Type:
    >> Microsoft OLE DB Provider for ODBC Drivers (0x80040E10)
    >> [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
    >> /PPdemosite/shop_ccard_TEST.asp, line 218
    >>
    >> B.T.W, the connection and other properties are tested and okay.
    >>
    >> Any ideas,
    >>
    >> Regards
    >>
    >> Penny.
    >>
    >>
    >>
     
    Nick, May 14, 2005
    #3
  4. Penny wrote:
    > Hi all,
    >
    > Thanks again guys for the help re Select Case. I might trouble you
    > with something else:
    >
    > I use this SQL in the Access query builder to return a single AirRate:
    >
    > SELECT AirRate FROM tblZoneShippingRates WHERE ZoneName =
    > "Australia" AND WeightCategory = "250"
    >
    > Works fine but I've tried this in an ASP page:
    >
    > rsShipping.Source = "SELECT AirRate FROM tblZoneShippingRates WHERE
    > ZoneName = "Australia" AND WeightCategory = "250""
    >
    > But always get error messages such as the following:
    >
    > Error Type:
    > Microsoft VBScript compilation (0x800A0401)
    > Expected end of statement
    > /PPdemosite/shop_ccard_TEST.asp, line 214, column 82
    > rsShipping.Source = "SELECT AirRate FROM tblZoneShippingRates WHERE
    > ZoneName = "Australia" AND WeightCategory = "250""


    Never set the source property directly like this. it makes debugging very
    difficult. Always assign your strings to string variables and use the
    variables where needed.



    > ----------------------------------------------------------------------------
    > -----^
    > Error Type:
    > Microsoft OLE DB Provider for ODBC Drivers (0x80040E10)
    > [Microsoft][ODBC Microsoft Access Driver] Too few parameters.
    > Expected 1. /PPdemosite/shop_ccard_TEST.asp, line 218
    >


    You must have changed the code to result in this message, right? You really
    should have shown us what you changed it to.


    The first error message has nothing to do with you connection, etc., which
    you could determine by creating a simple test page with the following lines
    of code:

    <%
    dim sSQL
    sSQL = "SELECT AirRate FROM tblZoneShippingRates WHERE
    ZoneName = "Australia" AND WeightCategory = "250""
    Response.Write sSQL
    %>

    VBScript uses line breaks to determine where lines of code end. You have a
    line break after the word "WHERE" so the compiler is expecting the line to
    be finished at this point. So it parses the first line, which is:

    sSQL = "SELECT AirRate FROM tblZoneShippingRates WHERE

    The problem is, you used a delimiter (the quote) to begin the creation of a
    string literal, but you never closed the string with a closing delimiter.
    This forces an error to be raised.

    So, let's fix that problem:
    <%
    dim sSQL
    sSQL="SELECT AirRate FROM tblZoneShippingRates WHERE" & _
    " ZoneName = "Australia" AND WeightCategory = "250""
    Response.Write sSQL
    %>


    The underscore character (_) is called a "line continuation character". It
    tells the compiler and parser that it should look at the following line to
    get the rest of the statement. If this new code does not raise an error (it
    should), look at the result of Response.Write after you run the page. Does
    it look anything like the sql statement you were trying to create? I suspect
    it will look like this:

    SELECT AirRate FROM tblZoneShippingRates WHERE ZoneName =

    Can you figure out what is happening? Look at the statement from the
    compiler's point of view:

    sSQL is the name of a variable.
    Since the statement did not start with "if" or some other keyword requiring
    a comparison, = is an assignment operator; specifically, we are about to
    assign a value to the sSQL variable.
    The quote is a delimiter signalling te start of a string literal to be
    assigned to the variable.
    The next quote is another delimiter, signalling the end of the string
    literal.
    The & is the contatenation operator, signalling that more data is about to
    be added to the string being created.
    The _ says to look at the next line for the rest of the statement.
    Another quote says to begin a new string literal (which will be added to the
    string already in memory)
    The next quote (after the = character ... before Australia) ends the string
    literal - it delimits it.
    The "A" character should raise an error, but if it doesn't, the rest of the
    characters on this line will be ignored.

    So, your problem is that you are attempting to use a character (the quote)
    that is normally interpreted as a delimiter in your string and you want it
    to be interpreted as a literal part of the string. Forcing a character that
    normally has a function to be treated literally is called "escaping" the
    character. The method for escaping a charater varies from language to
    language. For example, in jscript, you would use a backslash (\) to escape a
    character. In vbscript, you double up the character that needs to be
    escaped. This technique is also used in most SQL language variants as well.

    So, to force the quote to be treated literally, you escape it by doubling it
    (""). So, option one is to do this:

    <%
    dim sSQL
    sSQL="SELECT AirRate FROM tblZoneShippingRates WHERE" & _
    " ZoneName = ""Australia"" AND WeightCategory = ""250"""
    Response.Write sSQL
    %>

    Now, when you run this test page, you should see the result you expect.

    Another option is to use apostrophes for your delimiters within the sql
    statement:

    SELECT AirRate FROM tblZoneShippingRates WHERE ZoneName = 'Australia'
    AND WeightCategory = '250'

    will work as well as

    SELECT AirRate FROM tblZoneShippingRates WHERE ZoneName = "Australia"
    AND WeightCategory = "250"

    Jet (Access) is unusual in that it allows quotes to be used for delimiters.
    Most other sql variants only allow apostrophes to be used. So you will
    probably be doing yourself a favor if you bite the bullet now and start
    getting used to using apostrophes in your sql statements.

    This will solve your immediate problem, but you will likely encounter more
    problems. Read these posts for better alternatives to using dynamic sql:

    Saved parameter queries (aka stored procedures or views):
    http://groups-beta.google.com/group.../713f592513bf333c?hl=en&lr=&ie=UTF-8&oe=UTF-8

    http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/b3d322b882a604bd


    Using Command object to parameterize CommandText:
    http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e


    HTH,
    Bob Barrows

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], May 14, 2005
    #4
  5. Penny

    Penny Guest

    Thanks for your tips fellas and your detailed directions Bob.

    May take a while for me to digest it all!

    Regards

    Penny.


    "Bob Barrows [MVP]" <> wrote in message
    news:...
    > Penny wrote:
    > > Hi all,
    > >
    > > Thanks again guys for the help re Select Case. I might trouble you
    > > with something else:
    > >
    > > I use this SQL in the Access query builder to return a single AirRate:
    > >
    > > SELECT AirRate FROM tblZoneShippingRates WHERE ZoneName =
    > > "Australia" AND WeightCategory = "250"
    > >
    > > Works fine but I've tried this in an ASP page:
    > >
    > > rsShipping.Source = "SELECT AirRate FROM tblZoneShippingRates WHERE
    > > ZoneName = "Australia" AND WeightCategory = "250""
    > >
    > > But always get error messages such as the following:
    > >
    > > Error Type:
    > > Microsoft VBScript compilation (0x800A0401)
    > > Expected end of statement
    > > /PPdemosite/shop_ccard_TEST.asp, line 214, column 82
    > > rsShipping.Source = "SELECT AirRate FROM tblZoneShippingRates WHERE
    > > ZoneName = "Australia" AND WeightCategory = "250""

    >
    > Never set the source property directly like this. it makes debugging very
    > difficult. Always assign your strings to string variables and use the
    > variables where needed.
    >
    >
    >
    >
    > --------------------------------------------------------------------------

    --
    > > -----^
    > > Error Type:
    > > Microsoft OLE DB Provider for ODBC Drivers (0x80040E10)
    > > [Microsoft][ODBC Microsoft Access Driver] Too few parameters.
    > > Expected 1. /PPdemosite/shop_ccard_TEST.asp, line 218
    > >

    >
    > You must have changed the code to result in this message, right? You

    really
    > should have shown us what you changed it to.
    >
    >
    > The first error message has nothing to do with you connection, etc., which
    > you could determine by creating a simple test page with the following

    lines
    > of code:
    >
    > <%
    > dim sSQL
    > sSQL = "SELECT AirRate FROM tblZoneShippingRates WHERE
    > ZoneName = "Australia" AND WeightCategory = "250""
    > Response.Write sSQL
    > %>
    >
    > VBScript uses line breaks to determine where lines of code end. You have

    a
    > line break after the word "WHERE" so the compiler is expecting the line to
    > be finished at this point. So it parses the first line, which is:
    >
    > sSQL = "SELECT AirRate FROM tblZoneShippingRates WHERE
    >
    > The problem is, you used a delimiter (the quote) to begin the creation of

    a
    > string literal, but you never closed the string with a closing delimiter.
    > This forces an error to be raised.
    >
    > So, let's fix that problem:
    > <%
    > dim sSQL
    > sSQL="SELECT AirRate FROM tblZoneShippingRates WHERE" & _
    > " ZoneName = "Australia" AND WeightCategory = "250""
    > Response.Write sSQL
    > %>
    >
    >
    > The underscore character (_) is called a "line continuation character". It
    > tells the compiler and parser that it should look at the following line to
    > get the rest of the statement. If this new code does not raise an error

    (it
    > should), look at the result of Response.Write after you run the page. Does
    > it look anything like the sql statement you were trying to create? I

    suspect
    > it will look like this:
    >
    > SELECT AirRate FROM tblZoneShippingRates WHERE ZoneName =
    >
    > Can you figure out what is happening? Look at the statement from the
    > compiler's point of view:
    >
    > sSQL is the name of a variable.
    > Since the statement did not start with "if" or some other keyword

    requiring
    > a comparison, = is an assignment operator; specifically, we are about to
    > assign a value to the sSQL variable.
    > The quote is a delimiter signalling te start of a string literal to be
    > assigned to the variable.
    > The next quote is another delimiter, signalling the end of the string
    > literal.
    > The & is the contatenation operator, signalling that more data is about to
    > be added to the string being created.
    > The _ says to look at the next line for the rest of the statement.
    > Another quote says to begin a new string literal (which will be added to

    the
    > string already in memory)
    > The next quote (after the = character ... before Australia) ends the

    string
    > literal - it delimits it.
    > The "A" character should raise an error, but if it doesn't, the rest of

    the
    > characters on this line will be ignored.
    >
    > So, your problem is that you are attempting to use a character (the quote)
    > that is normally interpreted as a delimiter in your string and you want it
    > to be interpreted as a literal part of the string. Forcing a character

    that
    > normally has a function to be treated literally is called "escaping" the
    > character. The method for escaping a charater varies from language to
    > language. For example, in jscript, you would use a backslash (\) to escape

    a
    > character. In vbscript, you double up the character that needs to be
    > escaped. This technique is also used in most SQL language variants as

    well.
    >
    > So, to force the quote to be treated literally, you escape it by doubling

    it
    > (""). So, option one is to do this:
    >
    > <%
    > dim sSQL
    > sSQL="SELECT AirRate FROM tblZoneShippingRates WHERE" & _
    > " ZoneName = ""Australia"" AND WeightCategory = ""250"""
    > Response.Write sSQL
    > %>
    >
    > Now, when you run this test page, you should see the result you expect.
    >
    > Another option is to use apostrophes for your delimiters within the sql
    > statement:
    >
    > SELECT AirRate FROM tblZoneShippingRates WHERE ZoneName = 'Australia'
    > AND WeightCategory = '250'
    >
    > will work as well as
    >
    > SELECT AirRate FROM tblZoneShippingRates WHERE ZoneName = "Australia"
    > AND WeightCategory = "250"
    >
    > Jet (Access) is unusual in that it allows quotes to be used for

    delimiters.
    > Most other sql variants only allow apostrophes to be used. So you will
    > probably be doing yourself a favor if you bite the bullet now and start
    > getting used to using apostrophes in your sql statements.
    >
    > This will solve your immediate problem, but you will likely encounter more
    > problems. Read these posts for better alternatives to using dynamic sql:
    >
    > Saved parameter queries (aka stored procedures or views):
    >

    http://groups-beta.google.com/group/microsoft.public.inetserver.asp.general/
    msg/713f592513bf333c?hl=en&lr=&ie=UTF-8&oe=UTF-8
    >
    >

    http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/b
    3d322b882a604bd
    >
    >
    > Using Command object to parameterize CommandText:
    >

    http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/7
    2e36562fee7804e
    >
    >
    > HTH,
    > Bob Barrows
    >
    > --
    > Microsoft MVP - ASP/ASP.NET
    > Please reply to the newsgroup. This email account is my spam trap so I
    > don't check it very often. If you must reply off-line, then remove the
    > "NO SPAM"
    >
    >
     
    Penny, May 15, 2005
    #5
    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. =?Utf-8?B?VGltIFp5Y2g=?=

    SQL connection works in forms application, but not asp.net

    =?Utf-8?B?VGltIFp5Y2g=?=, Nov 22, 2005, in forum: ASP .Net
    Replies:
    4
    Views:
    3,182
    =?Utf-8?B?VGltIFp5Y2g=?=
    Nov 22, 2005
  2. Tim Zych
    Replies:
    0
    Views:
    342
    Tim Zych
    Nov 23, 2005
  3. Tim Zych
    Replies:
    2
    Views:
    512
    Tim Zych
    Nov 23, 2005
  4. F. GEIGER
    Replies:
    3
    Views:
    776
    F. GEIGER
    Aug 6, 2004
  5. David Wang
    Replies:
    0
    Views:
    736
    David Wang
    Dec 1, 2006
Loading...

Share This Page