Using where IN with a ADODB.Command object... 
Author Message
 Using where IN with a ADODB.Command object...

I am trying to use the ADODB.Command object to populate a recordset.
The query in the CommandText property of the Command object has a
query that uses the "where IN" clause. How do I pass multiple
parameters to this type of a query. Here is my code :

sql = "select * from employee_table where employee_id in (?)"
...
...
Objcmd.Parameters.append ObjCmd.CreateParameter =
"",adVarChar,adParamInput,255,"'200202', '200293'")
Set RS = Objcmd.Execute

I have tried sending the parameters in a variety of ways (with one
single quote, with none.....with two etc). None seem to work and no
records are returned in the recordset.

Is there a special way that these parameters are passed?

Thanks.



Sat, 17 Sep 2005 19:45:49 GMT  
 Using where IN with a ADODB.Command object...

I am trying to use the ADODB.Command object to populate a recordset.
The query in the CommandText property of the Command object has a
query that uses the "where IN" clause. How do I pass multiple
parameters to this type of a query. Here is my code :

sql = "select * from employee_table where employee_id in (?)"
...
...
Objcmd.Parameters.append ObjCmd.CreateParameter =
"",adVarChar,adParamInput,255,"'200202', '200293'")
Set RS = Objcmd.Execute

I have tried sending the parameters in a variety of ways (with one
single quote, with none.....with two etc). None seem to work and no
records are returned in the recordset.

Is there a special way that these parameters are passed?

You're on the right track, you just need to create a Parameter object for each parameter, add it to
the Parameters collection and a specify a placeholder for each one in the IN clause of your SQL
statemement:

sql = "select * from employee_table where employee_id in (?,?)"


Microsoft MVP (Visual Basic)



Sun, 18 Sep 2005 00:20:02 GMT  
 Using where IN with a ADODB.Command object...
Now that you use SQL statement in CommandText and you know the value for IN
clause, you do not have to use INPUT parameter, simply compose your SQL
statement, like this:

sql=SELECT * FROM employee_Table WHERE Employee_ID IN ('" & IDValue1 & "','"
& IDValue2 & "')"


Quote:
> I am trying to use the ADODB.Command object to populate a recordset.
> The query in the CommandText property of the Command object has a
> query that uses the "where IN" clause. How do I pass multiple
> parameters to this type of a query. Here is my code :

> sql = "select * from employee_table where employee_id in (?)"
> ...
> ...
> Objcmd.Parameters.append ObjCmd.CreateParameter =
> "",adVarChar,adParamInput,255,"'200202', '200293'")
> Set RS = Objcmd.Execute

> I have tried sending the parameters in a variety of ways (with one
> single quote, with none.....with two etc). None seem to work and no
> records are returned in the recordset.

> Is there a special way that these parameters are passed?

> Thanks.



Sun, 18 Sep 2005 02:23:44 GMT  
 Using where IN with a ADODB.Command object...
Thanks a million.....works like a charm!
Quote:


> I am trying to use the ADODB.Command object to populate a recordset.
> The query in the CommandText property of the Command object has a
> query that uses the "where IN" clause. How do I pass multiple
> parameters to this type of a query. Here is my code :
>
> sql = "select * from employee_table where employee_id in (?)"
> ...
> ...
> Objcmd.Parameters.append ObjCmd.CreateParameter =
> "",adVarChar,adParamInput,255,"'200202', '200293'")
> Set RS = Objcmd.Execute
>
> I have tried sending the parameters in a variety of ways (with one
> single quote, with none.....with two etc). None seem to work and no
> records are returned in the recordset.
>
> Is there a special way that these parameters are passed?

> You're on the right track, you just need to create a Parameter object for each parameter, add it to
> the Parameters collection and a specify a placeholder for each one in the IN clause of your SQL
> statemement:

> sql = "select * from employee_table where employee_id in (?,?)"


> Microsoft MVP (Visual Basic)



Sun, 18 Sep 2005 14:42:05 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Sending command to a FoxPro database using ADODB.Command Object

2. Using adodb command object with transferspreadsheet

3. Using browser-side ADODB.Command object?

4. Using browser-side ADODB.Command object?

5. Err in using ADODB Command Object in VB.Net

6. Using browser-side ADODB.Command object?

7. Problem creating ADODB.command object in asp.

8. ADODB command object

9. When to use ADODB.Command Object in ADO Programming

10. ADODB.Command Connection Error in MTS object

11. When to use ADODB.Command Object in ADO Programming

12. ADODB.command Object and RecordCount

 

 
Powered by phpBB® Forum Software