-
Junior Member
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
-
Expert Member
Re: Parameterization of a query which uses the Parameters collection of the “ADODB”
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
Last edited by NawabShaikh; 06-04-2008 at 10:23 PM.
Reason: alignment
-
Junior Member
Re: Parameterization of a query which uses the Parameters collection of the “ADODB”
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules