Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Visual Basic (Classic) FAQ


Using Parameters in ADO Stored Procedures by Norris68
Posted: 20 Jun 02

I have seen the same question about 3 times in a day, so here is a way of using parameters in stored procedures. The example has one input parameter and one output parameter and uses a stored procedure. Change the .CommandType to use it for queries. By the way, a Long variable is usually type adInteger.

Public Function GetCompanyName(InCID As Long) As String
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command

Dim prmInput As ADODB.Parameter
Dim prmOutput As ADODB.Parameter
' Set up a command object for the stored procedure.
With cmd
    Set .ActiveConnection = Conn1 'Conn1 is previously defined & open
    .CommandText = "get_company_name_from_id"
    .CommandType = adCmdStoredProc
    .CommandTimeout = 15
    Set prmInput = .CreateParameter("@cid", adInteger, adParamInput, , InCID)
    Set prmOutput = .CreateParameter("@cname", adVarWChar, adParamOutput, 255)
    .Parameters.Append prmInput
    .Parameters.Append prmOutput
    GetCompanyName = .Parameters(1).Value 'Zero-based array
End With
Set cmd = Nothing
End Function

Back to Visual Basic (Classic) FAQ Index
Back to Visual Basic (Classic) Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close