Help a newbie! How do I SELECT using a string as a WHERE clause?

[Pablo Contreras]

Hi,

I'm having a problem.

I've found that running a recordset with the query:

SELECT * FROM user;

works fine, but as soon as I add a WHERE clause, I get a Jet error.
(too few parameters).

I suspect the syntax may be faulty, but I don't know. This is what
I've got:

1 .strSQL = "SELECT * FROM user WHERE username=" &
Request.QueryString("username") & ";"

2. objRecordSet.Open strSQL,, DB_CONNECTION_STRING, adOpenKeyset,
adLockPessimistic, adCmdText

so the string in 1. is being used as the parameter for the command in
2.

Only problem is this returns a Too Few Paramters Jet error. As soon
as I change this to a simple query, as I said before, this works.

Do I need speech marks in the query?

Help!

Pablo.

[Kris Eiben]

Unless your usernames are numeric, you'll need to put single quotes
around the variable. Like so:

strUserName = request.querystring("username")
' insert code to check that this actually is a username and not an empty
string or whatever
strSQL = "Select field1, field2 from user where username = '" &
strUserName & "'"

A couple of other things:
1) You probably don't need those terminating semicolons. Have you tried
without?
2) Select * wastes resources on both ends -- the database needs to
translate that into columns before retrieving them, and the server
(probably) stores columns you don't need. So you may want to just list
the columns you need.

"Pablo Contreras" <pablo@pabs2003.plus.com> wrote in message
news:d48ohvsbt24cm073rhcj354c3m2imfdput@4ax.com...

> Hi,
>
> I'm having a problem.
>
> I've found that running a recordset with the query:
>
> SELECT * FROM user;
>
> works fine, but as soon as I add a WHERE clause, I get a Jet error.
> (too few parameters).
>
> I suspect the syntax may be faulty, but I don't know. This is what
> I've got:
>
> 1 .strSQL = "SELECT * FROM user WHERE username=" &
> Request.QueryString("username") & ";"
>
> 2. objRecordSet.Open strSQL,, DB_CONNECTION_STRING, adOpenKeyset,
> adLockPessimistic, adCmdText
>
> so the string in 1. is being used as the parameter for the command in
> 2.
>
> Only problem is this returns a Too Few Paramters Jet error. As soon
> as I change this to a simple query, as I said before, this works.
>
> Do I need speech marks in the query?
>
> Help!
>
> Pablo.