NEW TO ADO

[Lafor]

Does any 1 have an existing working example of invoking
a stored procedure with parameters using ADO?

For some reason, the 2 methods I have tried just do not
work...

Would sincerely appreciate it!

[WadeD]

Here's one way of doing it. It assumes you've selected MS ADO 2.0 thru Tools-References. The module shown for connecting to the database assumes you've setup a corresponding ODBC entry. If you're connecting ok, disregard the module.:

'In module 
Public cmd As New ADODB.Command          'Command object used to pass SQL or calls to SP
Public cnn As New ADODB.Connection       'Connection object

Public Sub ConnectDB(sDSNName as String)
    sDSNValue = "dsn=" & sDSNName
    With cnn
        .ConnectionString = sDSNValue
        .Open
        Set cmd.ActiveConnection = cnn
    End With
End Sub

'In Form
Public Sub Form_Load()
Dim rstStrdProc as Recordset


    'Make Initial Connection to SQL Server
    Call ConnectDB("KM Systems")

    'Indicate that Command Object passed contains a SP Call
    cmd.CommandType = adCmdStoredProc

    'Create SP Call
    'the USE statement is needed in SQL to indicate which 
    'database you're using if you're switching between   
    'databases and your ODBC entry doesn't list the database
    cmd.CommandText = "USE KMCORPAPPP999" _
                    & " EXEC S_KM_ScrCtrlLoad_SPXRef " & sScrNbr

    'Place Returned records in a Recordset
    Set rstStrdProc = cmd.Execute(, , vbReadOnly)

    'Display the first record's first column value
    If Not IsNull(rstStrdProc![Field1]) Then
        Msgbox rstStrdProc![Field1]
    End If
End Sub

Here, sScrNbr is a screen number parameter that the SP looks for. If there are multiple parameters, you'd put a comma in between them. There's at least one other way where you can set the parameters through the command object's properties -- this way is my personal preference.

[Lafor]

Wade,
This would be my preferred method as well...
But I could not get it to work...

I will try yours after lunch and let you know and will
possibly see what I was missing...


Thanks in advance!!!

[Lafor]

Tried but in vain...
Must be missing something crucial...
But I think it has to do with the the formatting the arguments being passed... but why??

stored_proc_name arg1, 'arg2', arg3 where arg 2 is a string

For procedures with no arguments, invocation of stored proc
seems fine...

[WadeD]

Go into SQL Server Query Analyzer and call your SP from there with the arguments you're trying to provide. Once you know it works there, go back to VB, put a breakpoint in your code on the final line that completes the string that you're using, and tweak it from the debug window. Since you'll have a working model from Query Analyzer, it should be relatively easy at that point.

Good luck

[Edited by WadeD on 08-21-2000 at 05:59 PM]