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/...UTF-8&oe=UTF-8 http://groups-beta.google.com/group/...d322b882a604bd
Using Command object to parameterize CommandText:
http://groups-beta.google.com/group/...e36562fee7804e
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"
