Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2004-11-02
12
Medium Priority
?
2,403 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
Comment
Question by:JoannieJefferson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
12 Comments
 
LVL 48

Expert Comment

by:Mikal613
ID: 12472946
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
ID: 12472965
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
ID: 12473071
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

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

Expert Comment

by:Mikal613
ID: 12473112
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
ID: 12473600
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
ID: 12473620
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
ID: 12473899
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
ID: 12474062
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
ID: 12474100
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
ID: 12474143

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:
Mikal613 earned 800 total points
ID: 12474250
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

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

636 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