Re: Error with SQL

Tech-Archive recommends: Fix windows errors by optimizing your registry

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

<snip>
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.inetserver.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&selm=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"


.

Relevant Pages

  • Re: problem with deleting object...?
    ... Mikael Hellström wrote: ... > Error Type: ... Don't use a recordset when you are not retrieving records!!!! ... This email account is my spam trap so I ...
    (microsoft.public.inetserver.asp.general)
  • RE: Displaying only 7 records at a time.
    ... whatever error trapping needed). ... ' rst is a Recordset object that will store ... | Content-Type: text/plain; ... Produced By Microsoft MimeOLE V5.50.4910.0300 ...
    (microsoft.public.access.forms)
  • RE: Displaying only 7 records at a time.
    ... | Content-Type: text/plain; ... Produced By Microsoft MimeOLE V5.50.4910.0300 ... | wondering if I did something wrong--it doesn't display ... |> ' rst is a Recordset object that will store ...
    (microsoft.public.access.forms)
  • Re: Edit/Update results in badly fragmented file
    ... Microsoft Jet objects when using DAO in *other* Microsoft Office programs. ... While the article discusses opening a recordset using DAO, ... Public Function ScrubValue (strMPN As String) As String ... Dim blnHasNum As Boolean ...
    (microsoft.public.access.modulesdaovba)
  • Re: Laufzeitfehler 13 beim Öffnen eines Recordsets
    ... Dim XYZ_RS As Recordset 2x Recodrset von IntelliSense angeboten wird. ... Microsoft Jet and Replication Objects 2.6 Library ... Public GL_wrkJet As Workspace ...
    (microsoft.public.de.vb.datenbank)