Dynamic SQL Using Parameters in Command Object 
Author Message
 Dynamic SQL Using Parameters in Command Object

Is it possible to create a dynamic SQL with parameters and assign this SQL
to a command object?
Right now, I have to use sp_executesql to do this and this only works for
SQL server because sp_executesql is a SQL Server stored procedure.

Thanks in Advance.



Fri, 25 Oct 2002 03:00:00 GMT  
 Dynamic SQL Using Parameters in Command Object
Yes Anthony it is possible. If you are using ADO, declare a variable of type
ADODB.command, then
assign a commandtext property to it as the SQL string, then execute.

example.

Dim mycommand as new adodb.command
Dim strSLQ as string
Dim myRS as adodb.recordset

strSQL = "select * from table"            'The where caluses can be built up
from VB control values

mycommand.activeconnection = connectionName    ' The connection can by via a
DSN or connectionstring
mycommand.commandtext = strSQL
mycommand.commandtype = adcmdtext

set myRS = mycommand.execute    'This will assign the recordset to myRS

Let me know if you need any further assistance.


Quote:
> Is it possible to create a dynamic SQL with parameters and assign this SQL
> to a command object?
> Right now, I have to use sp_executesql to do this and this only works for
> SQL server because sp_executesql is a SQL Server stored procedure.

> Thanks in Advance.



Sat, 26 Oct 2002 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Create dynamic recordset from command object with parameters

2. Using the Command Object w/Parameters

3. ? Executing a Stored Procedure With Output Parameters and A Resul tset using a Command Object

4. Using the IN keyword in a SQL string for a command object

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

6. Using the ADO Command Object to send an Execute command

7. ADO Command Object using Multiple Connection Objects

8. Err 429 ActiveXCan't Create Object VBA Dynamics SQL Server

9. Parameter with ADODB.Command against SQL-server

10. command sql with parameter

11. Problem passing parameters to DataReport/command object

12. How to use DE command object with a parameter

 

 
Powered by phpBB® Forum Software