Re: Parameterized queries with where clause including 'in'
- From: "Daniel Crichton" <msnews@xxxxxxxxxxxxxxxxxx>
- Date: Wed, 30 Nov 2005 09:05:28 -0000
Mike.Surel@xxxxxxxx wrote on 29 Nov 2005 14:42:22 -0800:
> I've searched for a while now to see an example of using a
> parameterized query using the 'in' keyword in a SELECT query and can't
> find anything that works. I'm working with VBScript in an ASP page.
>
> If I have a recordset opened with a query like this:
> inClause = "'user1','user2','user3'"
> "SELECT * FROM myTable where USERID IN (" + inClause + ")"
>
> it works fine. However if I use an adodb command object because I would
> like to use parameterized queries and I do the following
>
> sqlStr = "SELECT * FROM myTable where USERID IN (?)"
> cmd.CommandText=sqlStr
>
> set param1=cmd.CreateParameter("USERID",200,1,255,inClause)
> cmd.Parameters.Append param1
> set rs = cmd.Execute
>
> the recordset always comes back with BOF and EOF true. No records are
> returned. Anybody out there have a clue what I'm doing wrong? I've been
> banging my head against this for a while. Any help would be greatly
> appreciated.
What's happening is that SQL Server is attempting to find a row that has a
USERID that matches the entire string, ie.
SELECT * FROM myTable where USERID IN ('''user1'',''user2'',''user3''')
Check out http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
for some examples of how you can achieve what you're trying to do.
Dan
.
- References:
- Parameterized queries with where clause including 'in'
- From: Mike . Surel
- Parameterized queries with where clause including 'in'
- Prev by Date: Parameterized queries with where clause including 'in'
- Previous by thread: Parameterized queries with where clause including 'in'
- Index(es):
Relevant Pages
|
