Article ID: 228800
Article Last Modified on 6/29/2004
APPLIES TO
- Microsoft Visual Basic 5.0 Professional Edition
- Microsoft Visual Basic 6.0 Professional Edition
- Microsoft Visual Basic 5.0 Enterprise Edition
- Microsoft Visual Basic 6.0 Enterprise Edition
This article was previously published under Q228800
SUMMARY
This article shows how the rdoQuery object can be used to call a parameterized SQL Server stored procedure.
MORE INFORMATION
The sample in this article uses the Pubs database in SQL Server to demonstrate the use of the RDO query object to call parameterized queries. The Pubs database contains a stored procedure called byroyalty that accepts an integer parameter and returns all authors from the Titleauthor table whose royalty amount matches the passed value. Although this stored procedure does not return any values to the caller, the example reserves space for a return value in the Parameters collection for illustrative purposes.
Create and execute the following program. It calls the byroyalty stored procedure in the SQL Server Pubs sample database. Then, it prints values from the returned recordset in the debug window. Substitute a valid Server, User ID and Password in the connection string.
Sample Code
Private Sub Command1_Click()
Dim rs As rdoResultset
Dim cn As New rdoConnection
Dim qd As New rdoQuery
Dim cl As rdoColumn
cn.Connect = "uid=<username>;pwd=<strong password>;server=MyServer;" _
& "driver={SQL Server};database=pubs;" _
& "DSN='';"
cn.CursorDriver = rdUseOdbc
cn.EstablishConnection rdDriverNoPrompt
Set qd.ActiveConnection = cn
qd.SQL = "{ ? = call dbo.ByRoyalty (?) }"
qd(0).Direction = rdParamReturnValue
qd(1).Direction = rdParamInput
qd.rdoParameters(1) = 100
Set rs = qd.OpenResultset(rdOpenForwardOnly, rdConcurReadOnly)
For Each cl In rs.rdoColumns
Debug.Print cl.Name,
Next
Debug.Print
Do Until rs.EOF
For Each cl In rs.rdoColumns
Debug.Print cl.Value,
Next
rs.MoveNext
Debug.Print
Loop
rs.Close
qd.Close
cn.Close
End Sub
REFERENCES
For additional information, please see the following articles in the Microsoft Knowledge Base:
Keywords: kbhowto kbrdo kbstoredproc kbdatabase KB228800
