Parameterization of a query which uses the Parameters collection of the “ADODB”

Hi all,
I’ll be more than glad if somebody could provide a code sample of a SQL parameterization which uses the Parameters collection of the “ADODB” object model.

Thanks in advance
autotester

Private sub command()

dim objcon as adodb.connection
dim objcom as adodb.command
dim objpara as adodb.parameter
dim objpara2 as adodb.parameter
dim objrs as adodb.recordset
dim k as integer
set objcon = new adodb.connection set objcom = new adodb.command 'creating the db connection string 'please change the below connection string as per your server and database being used.
Objcon.connectionstring = "provider=sqloledb.1;password=;persist security info=true;user id=sa;initial catalog=testsql;data source=rockets" 'opening the connection
objcon.open objcon.connectionstring
msgbox "connection opened" 'assigning the command object parameters

With
objcom
.commandtext = "getrecords" 'name of the stored procedure
.commandtype = adcmdstoredproc 'type : stored procedure
.activeconnection = objcon.connectionstring
End With

[color="blue"]'create 2 output parameters
set objpara = objcom.createparameter("rows", adinteger, adparamoutput)
set objpara2 = objcom.createparameter("status", advarchar, adparamoutput, 50)

'append the output parameters to command object
objcom.parameters.append objpara
objcom.parameters.append objpara2[/color]

'store the result in a recordset
set objrs = objcom.execute 'open the recordset

do while not objrs.eof
for k = 0 to objrs.fields.count - 1
debug.print objrs(k).name & ": " & objrs(k).value
next
debug.print "_____"
objrs.movenext
loop 'close the recordset objrs.close [color="blue"] 'retrieve the output parameters values

msgbox "total records returned: " & objpara.value
msgbox objpara2.value[/color] 'close connection

objcon.close 'cleaning up
set objcom = nothing
set objcon = nothing
set objpara = nothing
set objpara2 = nothing
set objrs = nothing
end sub


regards,
Nawab

Hi Nawab,
Thanks a lot Nawab!!!
Is it exactly the same syntax for a query as for a stored procedure (your example)?
If I’m not wrong I think (according to a sample I have seen somewhere) that there is a little difference in case of a query, if I’m not wrong there should be a use of question mark to represent the parameter in the query.
If by any chance you could provide a code sample of a query parameterization
I’ll be very glad.
One more little thing, I’m a bit confused is your code in vb or vbscript?

Thanks in advance
autotester