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

[autotester]

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

[NawabShaikh]

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

'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

'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 'retrieve the output parameters values

msgbox "total records returned: " & objpara.value
msgbox objpara2.value '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

[autotester]

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