How To Call System-Stored Procedures on SQL Server from RDO

MORE INFORMATION

1. Start a new project in Visual Basic. Form1 is created by default.
2. Add a Command button, Command1, to Form1.
3. Paste the following code into the General Declarations section of Form1.
   
   Note You must change the database and UID =<username> and PWD =<strong password> to the correct values before you run this code. Make sure that UID has the appropriate permissions to perform this operation on the database.

      Private Sub Command1_Click()
        Dim en As rdoEnvironment
        Dim cn As rdoConnection
        Dim rs As rdoResultset
   
        Dim strConnect As String
        Dim strSQL As String
   
        Set en = rdoEnvironments(0)
        en.CursorDriver = rdUseOdbc
   
        strConnect = "Driver={SQL Server}; Server=MyServer; " & _
          "Database=pubs; Uid=<username>; Pwd=<strong password>"
        Set cn = en.OpenConnection(dsName:="", Prompt:=rdDriverNoPrompt, _
          ReadOnly:=False, Connect:=strConnect)
   
        Dim ps As rdoPreparedStatement
   
        'Note: If you don't specify master, you will get this following error:
        '"An invalid parameter was passed."
   
        strSQL = "{ ? = call master.dbo.sp_addlogin(?,?) }"
        Set ps = cn.CreatePreparedStatement("", strSQL)
        ps.BindThreshold = 1024 'largest column that will be bound under ODBC.
        Debug.Print ps.rdoParameters.Count
        ps.rdoParameters(0).Direction = rdParamOutput
        ps.rdoParameters(1).Direction = rdParamInput
        ps.rdoParameters(2).Direction = rdParamInput
   
        \* Note to KB editor: don't change this to "John Doe"
        ps.rdoParameters(1) = "Edward"
        ps.rdoParameters(2) = "Scissorhands"
   
        ps.Execute
        Debug.Print ps.rdoParameters(0).Value
      End Sub
   
   						

4. Start the program or press the F5 key.
5. Click the Command1 button to execute the stored procedure and display the parameter count and the output parameter in the debug window.