Connection Object - ADO

[nagabushane]

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

[Buzby]

In this example, I have a SP set up like this;

CREATE PROCEDURE sp_RunThisSP @Param1 VARCHAR(20), @Param2 VARCHAR(20) OUTPUT AS

Dim MyConn as New Adodb.Connection
Dim MyCmd as New Adodb.Command

'open the connection
MyConn.Open "Provider=SQLOLEDB;Server=MYSERVER;Database=MYDB;","SA",""

'start the command
MyCmd.ActiveConnection = MyConn 
MyCmd.CommandType = adCmdStoredProc
MyCmd.CommandText = "sp_RunThisSP"

'this next line is very handy - after setting the CommandText and
'Connection you can use .PArameters.Refresh to pull back the 
'required parameters for this SP from the SQL Server.
MyCmd.Parameters.Refresh

'note that Parameters(0) is the RETURN parameter of a SP
'so the parametes you pass to it start with 1 (or, as below, you can use the @variable name)
MyCmd.Parameters("@Param1")="PARAM1"

MyCmd.Execute

Msgbox "Output is " & MyCmd.Parameters("@Param2")