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

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!
JoannieJeffersonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mikal613Commented:
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
Mikal613Commented:
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
JoannieJeffersonAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

JoannieJeffersonAuthor Commented:
I have more than one stored procedure with many different parameters to pass
0
Mikal613Commented:
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
JoannieJeffersonAuthor Commented:
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
JoannieJeffersonAuthor Commented:
I changed my stored procedure to :

CREATE PROCEDURE sp_Test @ID int
AS

select *  from cardholder where cardholderid =@ID

GO

0
Mikal613Commented:
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
JoannieJeffersonAuthor Commented:
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
Mikal613Commented:
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
JoannieJeffersonAuthor Commented:

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
Mikal613Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.