Solved

passing parameter to stored procedure in sql server 2000 through Visual Basic 6

Posted on 2004-11-02
2,367 Views
Last Modified: 2008-01-09
Hey,

I need to know:
1. What is the syntax in VB for calling and passing parameters to a stored procedure.  using example procedure (sp_Test) below:
2. I will need to be able to use the sp recordset
3. I will need to know all the VB declarations

CREATE PROCEDURE sp_Test (@ID varchar(25))
AS

set nocount on

select CardHolderLastName from cardholder where cardholderid =@ID

set nocount off
GO



Thank you!
0
Question by:JoannieJefferson
    12 Comments
     
    LVL 48

    Expert Comment

    by:Mikal613
    Public Function CallStoredProc(sProcName As String, sParameter As String) As Integer
        'TH 22/01/01 call a stored procedure from vb
        Dim cmd As New Command
        Dim prm As New ADODB.Parameter
        Dim m_rs As New ADODB.recordset
        'create new stored proc object
        Set cmd = New ADODB.Command
        'add a paramter with value of "sParameter"
        Set prm = cmd.CreateParameter("param1", adBSTR, adParamInput, 1, sParameter)

        With cmd
            .Parameters.Append prm
            .ActiveConnection = cn
            .CommandType = adCmdStoredProc
            .CommandText = sProcName
        End With
        m_rs.Open cmd
        'Set frmMain.list1.DataSource = m_rs
    End Function

    0
     
    LVL 48

    Expert Comment

    by:Mikal613
    or

    You can excute a stored proceudure using connection object. Sample code is as follows.

    Dim cnn As New ADODB.Connection
    'Your stored procedure name and any parameters.

    cnn.sp_yourStoredProcedureName "param1"
    0
     

    Author Comment

    by:JoannieJefferson
    How do i get the recordset if execute the stored procedure using connection object?

    Dim cnn As New ADODB.Connection
    'Your stored procedure name and any parameters.

    cnn.sp_yourStoredProcedureName "param1"
    0
     

    Author Comment

    by:JoannieJefferson
    I have more than one stored procedure with many different parameters to pass
    0
     
    LVL 48

    Expert Comment

    by:Mikal613
    so use this instead

    Public Function CallStoredProc(sProcName As String, sParameter As String) As Integer
        'TH 22/01/01 call a stored procedure from vb
        Dim cmd As New Command
        Dim prm As New ADODB.Parameter
        Dim m_rs As New ADODB.recordset
        'create new stored proc object
        Set cmd = New ADODB.Command
        'add a paramter with value of "sParameter"
        Set prm = cmd.CreateParameter("param1", adBSTR, adParamInput, 1, sParameter)

        With cmd
            .Parameters.Append prm
            .ActiveConnection = cn
            .CommandType = adCmdStoredProc
            .CommandText = sProcName
        End With
        m_rs.Open cmd
        'Set frmMain.list1.DataSource = m_rs
    End Function

    the m_rs is the recordset you use
    0
     

    Author Comment

    by:JoannieJefferson
    OK...below is how I am using the function CallStoredProc.
    1. i get a runtime error '3021' that states either BOF and EOF is ture or the current record has been deleted.
    I know that the record exist why am I getting this error?  

    Option Explicit
     Dim cmd As New Command
     Dim prm As New ADODB.Parameter
     Dim m_rs As New ADODB.Recordset

    Public Function CallStoredProc(sProcName As String, sParameter As Integer) As String
        Set cmd = New ADODB.Command
        Set prm = cmd.CreateParameter("param1", adBSTR, adParamInput, 1, sParameter)

        With cmd
            .Parameters.Append prm
            .ActiveConnection = conSQL
            .CommandType = adCmdStoredProc
            .CommandText = sProcName
        End With
       
        m_rs.Open cmd

    Debug.Print m_rs.RecordCount            'Returns  -1
    Debug.Print m_rs.BOF                         'Returns TRUE
    Debug.Print m_rs.EOF                         'Returns TRUE
    Debug.Print m_rs(0)

    End Function

    Private Sub MDIForm_Load()
      Call ConnectSQL
      Call CallStoredProc("sp_test", "20226")        

    End Sub
    0
     

    Author Comment

    by:JoannieJefferson
    I changed my stored procedure to :

    CREATE PROCEDURE sp_Test @ID int
    AS

    select *  from cardholder where cardholderid =@ID

    GO

    0
     
    LVL 48

    Expert Comment

    by:Mikal613
    OK...below is how I am using the function CallStoredProc.
    1. i get a runtime error '3021' that states either BOF and EOF is ture or the current record has been deleted.
    I know that the record exist why am I getting this error?  

    Option Explicit
     Dim cmd As New Command
     Dim prm As New ADODB.Parameter
     Dim m_rs As New ADODB.Recordset

    Public Function CallStoredProc(sProcName As String, sParameter As Integer) As String
        Set cmd = New ADODB.Command
        Set prm = cmd.CreateParameter("param1", adBSTR, adParamInput, 1, sParameter)

        With cmd
            .Parameters.Append prm
            .ActiveConnection = conSQL
            .CommandType = adCmdStoredProc
            .CommandText = sProcName
        End With
       
        m_rs.Open cmd
    do until  m_rs.EOF
             Debug.Print m_rs.RecordCount            'Returns  -1
            Debug.Print m_rs(0)
        m_rs.movenext
    loop

    End Function

    Private Sub MDIForm_Load()
      Call ConnectSQL
      Call CallStoredProc("sp_test", "20226")        

    End Sub
    0
     

    Author Comment

    by:JoannieJefferson
    this section of code doesnt solve the EOF/BOF runtime error  . movenext returns  the same runtime error 3021.
    eof.                
    do until  m_rs.EOF
             Debug.Print m_rs.RecordCount            'Returns  -1
            Debug.Print m_rs(0)
        m_rs.movenext
    loop



       '====================================
    m_rs.Open cmd
    m_rs.movenext                                  'Returns runtime error 3021
    Debug.Print m_rs.RecordCount            'Returns  -1
    Debug.Print m_rs.BOF                         'Returns TRUE
    Debug.Print m_rs.EOF                         'Returns TRUE
    Debug.Print m_rs(0)

    0
     
    LVL 48

    Expert Comment

    by:Mikal613
    Option Explicit
     Dim cmd As New Command
     Dim prm As New ADODB.Parameter
     Dim m_rs As New ADODB.Recordset

    Public Function CallStoredProc(sProcName As String, sParameter As Integer) As String
        Set cmd = New ADODB.Command
        Set prm = cmd.CreateParameter("param1", adBSTR, adParamInput, 1, sParameter)

        With cmd
            .Parameters.Append prm
            .ActiveConnection = conSQL
            .CommandType = adCmdStoredProc
            .CommandText = sProcName
        End With
       
        m_rs.Open cmd
    do until  m_rs.EOF
                    Debug.Print m_rs(0)
        m_rs.movenext
    loop

    End Function

    Private Sub MDIForm_Load()
      Call ConnectSQL
      Call CallStoredProc("sp_test", 20226)        

    End Sub

    What line are yo getting the error?
    0
     

    Author Comment

    by:JoannieJefferson

    well if i use the do until m_rs.EOF loop , it never executes because it is at the EOF
    if i try to do a record count etc...... see below
    m_rs.movenext                            'Returns runtime error 3021
    Debug.Print m_rs.RecordCount      'Returns  -1
    Debug.Print m_rs.BOF                   'Returns TRUE
    Debug.Print m_rs.EOF                   'Returns TRUE
    Debug.Print m_rs(0)                      'Returns runtime error 3021

    THE RECORD EXIST BECAUSE I RUN THE SAME QUERY IN ANALIZER AND GET A RESULT.
    0
     
    LVL 48

    Accepted Solution

    by:
    Set adoCmd = New ADODB.Command

          With adoCmd
          .ActiveConnection = adoCnn
          .CommandText = "sp_test"
          .CommandType = adCmdStoredProc
          .Parameters.Refresh
          .Parameters(1)="20226"
          .Execute
          msgbox "Output Param is " & .Parameters(2)
       End With
    try this
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Course: Foundations of Front-End Development

    Jump-start a lucrative career in front-end web development, with zero previous coding experience required. This course covers the basic programming concepts and languages required for creating engaging websites from scratch.

    Introduction This article makes the case for using two modules in your VBA/VB6 applications to provide both case-sensitive and case-insensitive text comparison operations.  Recently, I solved an EE question using the LIKE function.  In order for th…
    I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

    884 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now