Record Check Error

I have the following ASP code, which is looking at an Access database and
table:

objRS.CursorLocation = adUseClient
strUser = Trim(Request.Form("Contributor"))
strPword = Trim(Request.Form("Password"))

strQuery = "SELECT [ProperName] FROM [AuthUser] WHERE [UserName] = '"
strUser &"' AND [Password] = '" & strPword & "'"
Response.Write strQuery

objRS.Open strQuery, objConn, , , adCmdTable

srUser and strPword are collected from a suitable form and the idea is to pull
out a matched record and pass on the ProperName field variable to the next
stage, which adds a record to another table.

However, I am getting the following error:

SELECT [ProperName] FROM [AuthUser] WHERE [UserName] = 'JohnFair' AND
[Password] = 'faramir'

Microsoft JET Database Engine error '80040e14'

Syntax error in FROM clause.

/updates/addnewsarticle.asp, line 31

Line 31 is the objRS.Open strQuery, objConn, , , adCmdTable line, but I'm
assuming that there's something wrong with the value in strQuery, a
Response.Write printout of which is the SELECT statement above. As far as I
can see, it looks OK but equally obviously, it isn't somewhere along the line.

I get the same error with or without the brackets and the field names are
correct (I've written a page that adds records to AuthUser and that works OK).

Any thoughts welcome!

TiA

--
John Fairhurst
www.johnfairhusrt.org

Re: Record Check Error

John Fairhurst wrote:
> I have the following ASP code, which is looking at an Access database
> and table:
>
> objRS.CursorLocation = adUseClient
> strUser = Trim(Request.Form("Contributor"))
> strPword = Trim(Request.Form("Password"))
>
> strQuery = "SELECT [ProperName] FROM [AuthUser] WHERE
> [UserName] = '" strUser &"' AND [Password] = '" & strPword &
> "'" Response.Write strQuery
>
> objRS.Open strQuery, objConn, , , adCmdTable
>
> srUser and strPword are collected from a suitable form and the idea
> is to pull out a matched record and pass on the ProperName field
> variable to the next stage, which adds a record to another table.
>
> However, I am getting the following error:
>
> SELECT [ProperName] FROM [AuthUser] WHERE [UserName] = 'JohnFair' AND
> [Password] = 'faramir'
>
> Microsoft JET Database Engine error '80040e14'
>
> Syntax error in FROM clause.
>
> /updates/addnewsarticle.asp, line 31
>
> Line 31 is the objRS.Open strQuery, objConn, , , adCmdTable line,
> but I'm assuming that there's something wrong with the value in
> strQuery,

No. The problem is the inappropriate adCmdTable, which tells ADO to expect
merely the name of a table in strQuery, not a sql statement with a WHERE
clause.
Anyways, adCmtTable is a CommandType which is never appropriate in ASP
applications. With very rare exceptions, you should be using adCmdText in
ASP.

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"