Connection Object
Please post the VB code required to call a Stored Procedure in SQL Server database with parameters using Connection Object of ADO.
Thanks in advance.
Regards,
Nag
[201 byte] By [nagabushane] at [2007-11-15 16:16:49] 
Hi,
Please post the VB code required to call a Stored Procedure in SQL Server database with parameters using Connection Object of ADO.
Thanks in advance.
Regards,
Nag
# 1 Re: Connection Object
You can pass parameters just by typinf them after the name of the stored procedure (sproc).' if the sproc returns a recordset
set rst = cnn.execute("SomeStoredProcedure 'SomeParameter'")
' if not returning anything
cnn.execute "SomeStoredProcedure 'SomeParameter'"
Note that when passing strings to a sproc, you need to enclose it in single '. If the string itself contains a ', you need to replace it by 2 '
If the parameter is numeric, you don't enclose it in '.
Tom Cannaerts
slisse@planetinternet.be
Programming today is a race between software engineers striving to build bigger and better idot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook
Cakkie at 2007-11-10 0:59:07 >
# 2 Re: Connection Object
Nice and swift. Too bad I am already out of votes...Special thanks to Lothar "the Great" Haensler, Tom Archer, Chris Eastwood, Bruno Paris and all the other wonderful people who made and make dev-archive a great place. Come back soon, you Gurus.
Cimperiali at 2007-11-10 1:00:05 >
# 3 Re: Connection Object
Any idea how to handle OUTPUT parameters ?nagabushane at 2007-11-10 1:01:15 >
# 4 Re: Connection Object
Hi Nag,This code may help u.
Happy programming
---------------
Dim objCmd As New ADODB.Command
Dim objParam As Object
With objCmd
.ActiveConnection = conn 'Connection Object
.CommandType = 4
.CommandText = "sp_Name"
Set objParam = objCmd.CreateParameter("PrimaryKey", adInteger, adParamInput)
objCmd.Parameters.Append objParam
Set objParam = objCmd.CreateParameter("DateTime", adDBTimeStamp, adParamInput)
objCmd.Parameters.Append objParam
'And continue for the rest of the parameter in the same way
objCmd("PrimaryKey").value=iPrimaryKey
objCmd("DateTime").Value = sDateTime
.Execute
End With
'bpp
praveen b p at 2007-11-10 1:02:12 >
# 5 Re: Connection Object
Sure, use a command objectdim cnn as new adodb.connection
dim cmd as new adodb.command
dim par as new adodb.parameter
cnn.open "provide.........."
par.direction = adParamOutput
par.name = "someparameter"
par.type = advarchar
par.size = 20
cmd.commandtext = "somestoredprocedure"
set cmd.activeconnection = cnn
cmd.parameters.append par
cmd.execute
msgbox par.value
Tom Cannaerts
slisse@planetinternet.be
Programming today is a race between software engineers striving to build bigger and better idot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook
Cakkie at 2007-11-10 1:03:10 >
# 6 Re: Connection Object
But I want to execute the Stored Procedure using Connection object not Command Object.nagabushane at 2007-11-10 1:04:15 >
# 7 Re: Connection Object
You cannot retrieve an output parameter using a connection object. The closest you can get is to let the stored procedure return a recordset which contains the value.-- somewhere in the stored procedure
SELECT @someparameter as OUTPUTPARAMETER
' in VB
set rst = cnn.execute("somestoredprocedure")
msgbox rst("outputparameter")
Tom Cannaerts
slisse@planetinternet.be
Programming today is a race between software engineers striving to build bigger and better idot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook
Cakkie at 2007-11-10 1:05:19 >
# 8 Re: Connection Object
Cakkie, Thank you Very much. It works.Regards,
Naga
nagabushane at 2007-11-10 1:06:19 >
