Calling Stored Procedures with ADO

[JayWms]

I am trying to call a stored procedure using ADO.

The procedure takes in a few parameters, calculates a number than passes it back out, how do I receive this value?

Jason

[Serge]

Ok, here's an example. I've created a DSN called Pubs (which is in SQL server database)
I also created stored procedure called "AuthorsByCity". This Stored procedure takes 1 parameter, but you can use any stored procedure to take any number of parameters.
This Stored procedure just getting Athors namees and Tiles. Criteria(parameter) is a CityName. So here's the code:

    Dim cn As New ADODB.Connection
    Dim cm As New ADODB.Command
    Dim pr As ADODB.Parameter
    Dim rs As New ADODB.Recordset
    Dim intRow As Integer
    Dim i As Integer
    
    cn.Open "DSN=pubs;UID=sa;PWD=;"
    cm.ActiveConnection = cn
    cm.CommandType = adCmdStoredProc
    cm.CommandText = "AuthorsByCity"
    cm.CommandTimeout = 15
    Set pr = New ADODB.Parameter
    Set pr = cm.CreateParameter("CityName", adChar, , 20, "Boston")
    cm.Parameters.Append pr
    rs.CursorType = adOpenStatic
    Set rs = cm.Execute()
    grd.Cols = rs.Fields.Count
    
    intRow = 1
    Do Until rs.EOF
        grd.Rows = grd.Rows + 1
        For i = 0 To rs.Fields.Count - 1
            grd.TextMatrix(0, i) = rs(i).Name
            grd.TextMatrix(intRow, i) = rs(i)
        Next
        intRow = intRow + 1
        rs.MoveNext
    Loop
        
    rs.Close
    cn.Close

If you have any questions, just email me or leave them here.

Regards,

------------------

Serge

Software Developer
[email protected]
[email protected]