Adapting Access SQL to ASP SQL

P

Penny

Hi all,

I know this is not a pure Access question but many of you are so good with
SQL and Recordsets I'm going impose.

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

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.
 
V

Van T. Dinh

I don't know about ASP but you are trying to use the String delimiter
DoubleQuote " in a DoubleQuote-delimited String. How would ASP / ? know
which one should be in the String and which one is the delimiter

In VB / JET , if you want to include a DoubleQuote in a
DoubleQuote-delimited String, you need to use 2 consecutive DoubleQuotes
like

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

(assuming, from your code/SQL String that WeightCategory is actually a Text
Field and you want to select CategoryWeight = "250", not the numeric 250).
 
A

Alex White MCDBA MCSE

Personally I use the following

rsShipping.Source = "SELECT AirRate FROM tblZoneShippingRates WHERE ZoneName
= " & chr(34) & "Australia" & chr(34) & " AND WeightCategory = " & chr(34) &
"250" & chr(34)

I find it more readable, just my personal view.

the chr(34) is "

--
Regards

Alex White MCDBA MCSE
http://www.intralan.co.uk

Van T. Dinh said:
I don't know about ASP but you are trying to use the String delimiter
DoubleQuote " in a DoubleQuote-delimited String. How would ASP / ? know
which one should be in the String and which one is the delimiter

In VB / JET , if you want to include a DoubleQuote in a
DoubleQuote-delimited String, you need to use 2 consecutive DoubleQuotes
like

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

(assuming, from your code/SQL String that WeightCategory is actually a
Text
Field and you want to select CategoryWeight = "250", not the numeric 250).

--
HTH
Van T. Dinh
MVP (Access)



Penny said:
Hi all,

I know this is not a pure Access question but many of you are so good
with
SQL and Recordsets I'm going impose.

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

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.
Click to expand...
Click to expand...
 
V

Van T. Dinh

In some of my older databases, I used to have a global constant:

Const DQ As String = """"

and then used DQ in the (SQL) String construction. I actually think the
global constant in the clearest one. However, I am so used to the 3 methods
so I use them interchangeably.
 
A

adsl

Penny said:
Hi all,

I know this is not a pure Access question but many of you are so good with
SQL and Recordsets I'm going impose.

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

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.
Click to expand...
 
A

adsl

Van T. Dinh said:
I don't know about ASP but you are trying to use the String delimiter
DoubleQuote " in a DoubleQuote-delimited String. How would ASP / ? know
which one should be in the String and which one is the delimiter

In VB / JET , if you want to include a DoubleQuote in a
DoubleQuote-delimited String, you need to use 2 consecutive DoubleQuotes
like

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

(assuming, from your code/SQL String that WeightCategory is actually a
Text
Field and you want to select CategoryWeight = "250", not the numeric 250).

--
HTH
Van T. Dinh
MVP (Access)



Penny said:
Hi all,

I know this is not a pure Access question but many of you are so good
with
SQL and Recordsets I'm going impose.

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

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.
Click to expand...
Click to expand...
 
A

adsl

Alex White MCDBA MCSE said:
Personally I use the following

rsShipping.Source = "SELECT AirRate FROM tblZoneShippingRates WHERE
ZoneName = " & chr(34) & "Australia" & chr(34) & " AND WeightCategory = "
& chr(34) & "250" & chr(34)

I find it more readable, just my personal view.

the chr(34) is "

--
Regards

Alex White MCDBA MCSE
http://www.intralan.co.uk

Van T. Dinh said:
I don't know about ASP but you are trying to use the String delimiter
DoubleQuote " in a DoubleQuote-delimited String. How would ASP / ? know
which one should be in the String and which one is the delimiter

In VB / JET , if you want to include a DoubleQuote in a
DoubleQuote-delimited String, you need to use 2 consecutive DoubleQuotes
like

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

(assuming, from your code/SQL String that WeightCategory is actually a
Text
Field and you want to select CategoryWeight = "250", not the numeric
250).

--
HTH
Van T. Dinh
MVP (Access)



Penny said:
Hi all,

I know this is not a pure Access question but many of you are so good
with
SQL and Recordsets I'm going impose.

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

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.
Click to expand...
Click to expand...
Click to expand...
 
A

adsl

Van T. Dinh said:
In some of my older databases, I used to have a global constant:

Const DQ As String = """"

and then used DQ in the (SQL) String construction. I actually think the
global constant in the clearest one. However, I am so used to the 3
methods
so I use them interchangeably.
Click to expand...
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Create Stored Procedure in SQL server from Access VBA? 4
Conecting Access to SQL in Windows 7 4
ASP to Linked Access MSSQL2005 table (And ASP) 7
SQL code works in Access SQL window but not in VBA SQL code 61
How to execute SQL stored procedure from Access 8
From Access how to create stored procedure in SQL server 4
Access 2007 / ODBC linked tables to SQL SERVER / Vista 64 bit erro 6
linked table from ACCESS 3

Top