Error with SQL

Hi,

I have:

strsql = "SELECT * FROM tblTheCustomers WHERE CustomerID=" &
session("CustomerID") & ";"
response.Write(strsql)
Response.End()

The browser shows:

SELECT * FROM tblTheCustomers WHERE CustomerID=1;

Great!

When I remove the two response. 's I get:

Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in WHERE clause.
C.asp, line 62

The whole code is below, any ideas? Thanks

Jon

------------------------------------------------------------ ----------------------
Code:

dim conn, strsql, rsuser, strMDBPath
set conn=server.createobject("ADODB.Connection")
set rsuser=server.createobject("ADODB.Recordset")
strMDBpath = Server.MapPath("Files.mdb")

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

strsql = "SELECT * FROM tblTheCustomers WHERE CustomerID=" &
session("CustomerID") & ";"
'response.Write(strsql)
'Response.End()
rsuser.open strsql,conn,1,2

CustomerName = rsuser("CustomerName")
CustomerLogo = rsuser("CustomerLogo")

rsuser.close
set rsuser=nothing
------------------------------------------------------------ ----------------------

Re: Error with SQL

I've tried:

strsql = "SELECT CustomerName, CustomerLogo, CustomerID FROM
tblTheCustomers WHERE CustomerID=" & session("CustomerID") & ";"

Also!

Jon

Re: Error with SQL

J-P-W wrote:
> Hi,
>
> I have:
>
> strsql = "SELECT * FROM tblTheCustomers WHERE CustomerID=" &

http://www.aspfaq.com/show.asp?id=2096

> session("CustomerID") & ";"
> response.Write(strsql)
> Response.End()
>
> The browser shows:
>
> SELECT * FROM tblTheCustomers WHERE CustomerID=1;
>
> Great!
>
> When I remove the two response. 's I get:
>
> Error Type:
> Microsoft JET Database Engine (0x80040E14)
> Syntax error in WHERE clause.
> C.asp, line 62
>
> The whole code is below, any ideas? Thanks
>

The statement looks correct to me as well. I assume that you've opened your
database in Access and used the Query Builder to test the statement ...

I also assume that CustomerID is a numeric field ...


> rsuser.open strsql,conn,1,2

Why are you opening such an expensive cursor? The default forward-only
cuursor should surely suffice in this situaltion ... you are retrieving a
single record from what I can see. Even if you were retrieving multiple
records, there is rarely a need to use more than the default forward-only
cursor in ASP. Your goal in ASP should be to not have the recordset open
long enough to care what other users do to the data. Get rid of the "set
rsuser=server.createobject ..." line and let ADO create the recordset for
you by:

Set rsuser = conn.execute(sql,,1)

Further points to consider:
Your use of dynamic sql is leaving you vulnerable to hackers using sql
injection:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

See here for a better, more secure way to execute your queries by using
parameter markers:
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e

Personally, I prefer using stored procedures, or saved parameter queries
as
they are known in Access:

Access:
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&sel m=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl



--
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"

Re: Error with SQL

On 20 Jun, 01:15, "Bob Barrows [MVP]"
wrote:



Bob, you've given me lots to consider, thank you.

I'll admit to not understanding the cursor options, but I understand
you points to show me that I'd better learn!

I'll read up and try your suggestions.

Thank you for your time.

Oh and yes I've tried the query in access, and yes it's numeric.

Jon