SQL seems reported as wrong.

[JW]

Hi, I'm new to asp coding database, but have been working with Web Design
and MS Access for quite a few years.

I'm access a db and am useing the following code (1) and all is well. When
I replace the sql string with the code at (2) I get an "error '80004005'
Unspecified error /quotes/quote3.asp, line 48 " error message - not very
useful!

I've pasted the sql back into a query in access and it workds fine. So I'm
stumped. Any help would, of course, be appreciated.

(1) lines that work:
=====================
<%
'initialise objects and variables
dim conn, strsql, rsuser, strMDBPath
set conn=server.createobject("ADODB.Connection")
set rsuser=server.createobject("ADODB.Recordset")
strMDBpath = Server.MapPath("quote.mdb")

'direct connection
conn.open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & strMDBPath

strsql = "SELECT tblZones.PostCode, tblZones.Area FROM tblZones WHERE
(((tblZones.PostCode)=" & Chr(34) & txtRiskPostCodePart & Chr(34) & "));"

'print the sql out to check it
response.Write strsql & "<br>"

rsuser.open strsql,conn,1,2
'code here to use the data!!

'close resources used
rsuser.close
conn.close

'clean up
set rsuser=nothing
set conn=nothing
%>
=====================
(2) The sql string that causes there error, but appears OK:
-------------------------------
strsql = "SELECT tblRatesMatrix.Zone, tblRatesMatrix.C1 FROM tblRatesMatrix
WHERE (((tblRatesMatrix.Zone)=" & Chr(34) & txtZone & Chr(34) & "));"
-------------------------------

Thanks

Jon
jon_webb ^at^ yahoo ^dot^ com

[Bob Barrows]

JW wrote:

> Hi, I'm new to asp coding database, but have been working with Web
> Design and MS Access for quite a few years.
>
> I'm access a db and am useing the following code (1) and all is well.
> When I replace the sql string with the code at (2) I get an "error
> '80004005' Unspecified error /quotes/quote3.asp, line 48 " error
> message - not very useful!

<snip>

> =====================
> (2) The sql string that causes there error, but appears OK:
> -------------------------------
> strsql = "SELECT tblRatesMatrix.Zone, tblRatesMatrix.C1 FROM
> tblRatesMatrix WHERE (((tblRatesMatrix.Zone)=" & Chr(34) & txtZone &
> Chr(34) & "));" -------------------------------
>

What does this look like after it's response.written? This is not helpful at
all. We need to see the actual query that's being sent to the database, not
the vbscript statement that concatenates some string characters and
variables and assigns them to another variable.

My guess:
"Zone" is an ODBC reserved word ([url]http://www.aspfaq.com/show.asp?id=2080[/url]). If
you can't change the name of the field, you will need to delimit it with
brackets [zone] in order to use it in a sql statement passed via ADO.

HTH,
Bob Barrows

[JW]

Bob,
Thanks for the reply.

The sql becomes, for example:
strsql =3D "SELECT tblRatesMatrix.Zone, tblRatesMatrix.C1 FROM =
tblRatesMatrix WHERE (((tblRatesMatrix.Zone)=3D"Z2"));"

However, you were spot on I changed Zone to txtZone and it worked!

Thank you

Jon

"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:uT$t6SQhDHA.884@TK2MSFTNGP12.phx.gbl...

> JW wrote:

> > Hi, I'm new to asp coding database, but have been working with Web
> > Design and MS Access for quite a few years.
> >
> > I'm access a db and am useing the following code (1) and all is well.
> > When I replace the sql string with the code at (2) I get an "error
> > '80004005' Unspecified error /quotes/quote3.asp, line 48 " error
> > message - not very useful!

>
> <snip>

> > =====================
> > (2) The sql string that causes there error, but appears OK:
> > -------------------------------
> > strsql = "SELECT tblRatesMatrix.Zone, tblRatesMatrix.C1 FROM
> > tblRatesMatrix WHERE (((tblRatesMatrix.Zone)=" & Chr(34) & txtZone &
> > Chr(34) & "));" -------------------------------
> >

> What does this look like after it's response.written? This is not helpful

at

> all. We need to see the actual query that's being sent to the database,

not

> the vbscript statement that concatenates some string characters and
> variables and assigns them to another variable.
>
> My guess:
> "Zone" is an ODBC reserved word ([url]http://www.aspfaq.com/show.asp?id=2080[/url]).

If

> you can't change the name of the field, you will need to delimit it with
> brackets [zone] in order to use it in a sql statement passed via ADO.
>
> HTH,
> Bob Barrows
>
>

[Aaron Bertrand [MVP]]

> strsql = "SELECT tblZones.PostCode, tblZones.Area FROM tblZones WHERE

> (((tblZones.PostCode)=" & Chr(34) & txtRiskPostCodePart & Chr(34) & "));"

Why are you sending CHR(34)? Send ' not the ASCII conversion of "