[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

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

Posted on 2004-11-02
12
Medium Priority
?
2,405 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
  • 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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 

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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
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…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month7 days, 17 hours left to enroll

607 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