Solved

ADO: Getting return value and result set from MS SQL Server's stored procedure

Posted on 2002-05-22
14
3,864 Views
Last Modified: 2008-03-17
I'm using ADO in VB 6, and trying to get return value and resultset from stored procedure. I can get the resultset returned from sp, but something somehow unfortunately, I've always got Empty for the return value.

Here is my stored procedure:

CREATE PROCEDURE [dbo].[sp_ListCommonType] @type int AS
select * from tb_Type where TypeID=@type
return @@rowcount
GO


Here is a piece of my VB code:

Dim ADOCmd As New ADODB.Command
      Dim ADOPrm As New ADODB.Parameter
      Dim ADOCon As ADODB.Connection
      Dim ADORs As ADODB.Recordset
      Dim sParmName As String
      Dim strConnect As String
      Dim rStr As String

      On Error GoTo ErrHandler

      strConnect = "driver={SQL Server};server=(local);uid=sa;pwd=;database=pubs"

      Set ADOCon = New ADODB.Connection
      With ADOCon
          .Provider = "MSDASQL"
          .CursorLocation = adUseServer
          .ConnectionString = strConnect
          .Open
      End With

      Set ADOCmd.ActiveConnection = ADOCon
      With ADOCmd
          .CommandType = adCmdStoredProc
          .CommandText = "sp_ListCommonType"
      End With

      sParmName = "Return"
      Set ADOPrm = ADOCmd.CreateParameter(sParmName, adInteger, _
        adParamReturnValue, , 0)
      ADOCmd.Parameters.Append ADOPrm
      ADOCmd.Parameters(sParmName).Value = -1

      Set ADORs = ADOCmd.Execute

      Debug.Print "Return: " & ADOCmd.Parameters("Return").Value
      While Not ADORs.EOF
        Debug.Print ADORs.Fields(0)
      Wend


      GoTo Shutdown

ErrHandler:
          Call ErrHandler(ADOCon)
          Resume Next

Shutdown:
          Set ADOCmd = Nothing
          Set ADOPrm = Nothing
          Set ADORs = Nothing
          Set ADOCon = Nothing


What should I do ???
0
Comment
Question by:Mr_Peerapol
  • 7
  • 4
  • 3
14 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7027731
If you want the brutal and short answer, it cannot be done. There are some workarounds, but none very palatable.
Either Resultset or outbound parameters, not both.

I ran across this some months ago and I will try and find my notes on the subject.

Anthony
0
 
LVL 4

Expert Comment

by:Steve Sirica
ID: 7028031
Have you tried returning multiple recordsets and itterating thru them?

Steve
0
 
LVL 4

Expert Comment

by:Steve Sirica
ID: 7028109
If you want to go the multiple recordset route try something like this:
- change your cursor to adUseClient
- keep your SP the same
- after your done processing the 1st recordset execute the following:
   set ADORs = ADORs.NextRecordset
to get to @@Rowcount

If you want my sample code just let me know and I can post it.  It worked for me, but I'm not sure if this is what you're looking for.

Goood luck!
Steve

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Expert Comment

by:Steve Sirica
ID: 7028132
Here's the code I used anyway:
    Dim adoCN       As New ADODB.Connection
    Dim adoRS       As New ADODB.Recordset
    Dim strConn     As String
   
   
    strConn = "Provider=sqloledb;Integrated Security=SSPI;" & _
                 "Persist Security Info=False;" & _
                 "Data Source=winxp0237;" & _
                 "Initial Catalog=pubs;"
    adoCN.ConnectionString = strConn
    adoCN.Open
   
    Set adoRS = New ADODB.Recordset
   
    adoRS.CursorLocation = adUseClient
    adoRS.Open "reptq1", adoCN, adOpenStatic, adLockBatchOptimistic, adCmdText
    'Set adoRS.ActiveConnection = Nothing
   
    Debug.Print adoRS(0).Name & " = "; adoRS(0)
    Set adoRS = adoRS.NextRecordset
    Debug.Print adoRS(0).Name & " = "; adoRS(0)

the SP in pubs looks like this:
CREATE PROCEDURE reptq1 AS
select *
  from titles

select @@RowCount as ResRowCount
GO


Anyway...Good luck!
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7028330
This article from MSDN, may give you the workaround you are looking for:
HOWTO: Process Multiple Recordsets and Messages in ADO (Q245179)
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q245179&SD=MSKB&

Anthony
0
 
LVL 25

Author Comment

by:Mr_Peerapol
ID: 7028705
Actually, What I want is to store some resultset data into VB's array. To do so, I want the number of records returned from SP first and then I can redim the array. Somtething like this;

Dim lRecordCount As Long
Dim aCommonType() As Integer

lRecordCount = cmd("Return").Value
Redim aCommonType(lRecordCount-1)
While Not rs.EOF
  aCommonType(i) = rs("Type").Value
  i = i + 1
WEnd

Any workaround?

Thanks
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7028847
There are a couple of ways you can do that.  Here is one way:

Change your Stored Procedure to the following:

CREATE PROCEDURE [dbo].[sp_ListCommonType] @type int
AS

Select * from tb_Type where TypeID=@type

-- return @@rowcount

Select @@ROWCOUNT As RecordCount

GO

Then change your code VB code as follows:
Dim ADOCmd As New ADODB.Command
Dim ADOPrm As New ADODB.Parameter
Dim ADOCon As ADODB.Connection
Dim ADORs As ADODB.Recordset
Dim ADORsCount As ADODB.Recordset    '<-- Add this line

Dim sParmName As String
Dim strConnect As String
Dim rStr As String

On Error GoTo ErrHandler

strConnect = "driver={SQL Server};server=(local);uid=sa;pwd=;database=pubs"

Set ADOCon = New ADODB.Connection
With ADOCon
  .Provider = "MSDASQL"
  .CursorLocation = adUseServer
  .ConnectionString = strConnect
  .Open
End With

Set ADOCmd.ActiveConnection = ADOCon
With ADOCmd
  .CommandType = adCmdStoredProc
  .CommandText = "sp_ListCommonType"
End With

sParmName = "Return"
Set ADOPrm = ADOCmd.CreateParameter(sParmName, adInteger, _
       adParamReturnValue, , 0)
ADOCmd.Parameters.Append ADOPrm
ADOCmd.Parameters(sParmName).Value = -1

Set ADORs = ADOCmd.Execute
Set ADORsCount = ADORs.NextRecordset  '<-- Add this line

'Debug.Print "Return: " & ADOCmd.Parameters("Return").Value 'Comment this line
Debug.Print "Record Count: " & ADORsCount.Fields("RecordCount").Value'   '<-- Add this line
While Not ADORs.EOF
   Debug.Print ADORs.Fields(0)
Wend

GoTo Shutdown

ErrHandler:
   Call ErrHandler(ADOCon)
   Resume Next

Shutdown:
   Set ADOCmd = Nothing
   Set ADOPrm = Nothing
   'Don't forget to close all connections '<-- Add the following lines
   If Not ADORs Is Nothing Then    
      If ADORs.State = adStateOpen Then
         ADORs.Close
      End If
      Set ADORs = Nothing
   End If
   If Not ADORsCount Is Nothing Then
      If ADORsCount.State = adStateOpen Then
         ADORsCount.Close
      End If
      Set ADORsCount = Nothing
   End If
   If Not ADOCon Is Nothing Then
      If ADOCon.State = adStateOpen Then
         ADOCon.Close
      End If
      Set ADOCon = Nothing
   End If

Anthony
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 123 total points
ID: 7028857
Another option is to use a client cursor and use the RecordCount property of the recordset.  This is somewaht more complicated as you need to set the recordset properties and use the Command object as the Source argument in the OPen command of the recordset.

In other words you would change the stored procedure as follows:
CREATE PROCEDURE [dbo].[sp_ListCommonType] @type int
AS

Select * from tb_Type where TypeID=@type

-- return @@rowcount

GO

And your VB code would be as follows:

Dim ADOCmd As New ADODB.Command
Dim ADOPrm As New ADODB.Parameter
Dim ADOCon As ADODB.Connection
Dim ADORs As ADODB.Recordset

Dim sParmName As String
Dim strConnect As String
Dim rStr As String

On Error GoTo ErrHandler

strConnect = "driver={SQL Server};server=(local);uid=sa;pwd=;database=pubs"

Set ADOCon = New ADODB.Connection
With ADOCon
 .Provider = "MSDASQL"
 .CursorLocation = adUseServer
 .ConnectionString = strConnect
 .Open
End With

Set ADOCmd.ActiveConnection = ADOCon
With ADOCmd
 .CommandType = adCmdStoredProc
 .CommandText = "sp_ListCommonType"
End With

sParmName = "Return"
Set ADOPrm = ADOCmd.CreateParameter(sParmName, adInteger, _
      adParamReturnValue, , 0)
ADOCmd.Parameters.Append ADOPrm
ADOCmd.Parameters(sParmName).Value = -1

'Set ADORs = ADOCmd.Execute   '<-- Comment this line
Set ADORs = New ADODB.Recordset
With ADORs
   .Source = cmd
   .ActiveConnection = ADOCon
   .CursorLocation = adUseClient
   .CursorType = adOpenForwardOnly
   .LockType = adLockReadOnly
   .Open Options:=adCmdStoredProc
End With

'Debug.Print "Return: " & ADOCmd.Parameters("Return").Value 'Comment this line
Debug.Print "Record Count: " & ADORs. RecordCount '<-- Add this line
While Not ADORs.EOF
  Debug.Print ADORs.Fields(0)
Wend

GoTo Shutdown

ErrHandler:
  Call ErrHandler(ADOCon)
  Resume Next

Shutdown:
  Set ADOCmd = Nothing
  Set ADOPrm = Nothing
  'Don't forget to close all connections '<-- Add the following lines
  If Not ADORs Is Nothing Then    
     If ADORs.State = adStateOpen Then
        ADORs.Close
     End If
     Set ADORs = Nothing
  End If
  If Not ADOCon Is Nothing Then
     If ADOCon.State = adStateOpen Then
        ADOCon.Close
     End If
     Set ADOCon = Nothing
  End If

Anthony
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7028865
But (I believe) the best way to accomplish this is to use the GetRows method of the Recordset object.

So using the same Stored Procedure as the last example, you would change the VB code as follows:

Dim ADOCmd As New ADODB.Command
Dim ADOPrm As New ADODB.Parameter
Dim ADOCon As ADODB.Connection
Dim ADORs As ADODB.Recordset

Dim sParmName As String
Dim strConnect As String
Dim rStr As String
Dim aRows as Variant        ' <-- Add this line

On Error GoTo ErrHandler

strConnect = "driver={SQL Server};server=(local);uid=sa;pwd=;database=pubs"

Set ADOCon = New ADODB.Connection
With ADOCon
.Provider = "MSDASQL"
.CursorLocation = adUseServer
.ConnectionString = strConnect
.Open
End With

Set ADOCmd.ActiveConnection = ADOCon
With ADOCmd
.CommandType = adCmdStoredProc
.CommandText = "sp_ListCommonType"
End With

sParmName = "Return"
Set ADOPrm = ADOCmd.CreateParameter(sParmName, adInteger, _
     adParamReturnValue, , 0)
ADOCmd.Parameters.Append ADOPrm
ADOCmd.Parameters(sParmName).Value = -1

Set ADORs = ADOCmd.Execute   '<-- Comment this line
aRows = ADORs.GetRows

'Debug.Print "Return: " & ADOCmd.Parameters("Return").Value 'Comment this line
Debug.Print "Record Count: " & UBound(aRows, 2) '<-- Add this line
While Not ADORs.EOF
 Debug.Print ADORs.Fields(0)
Wend

GoTo Shutdown

ErrHandler:
 Call ErrHandler(ADOCon)
 Resume Next

Shutdown:
 Set ADOCmd = Nothing
 Set ADOPrm = Nothing
 'Don't forget to close all connections '<-- Add the following lines
 If Not ADORs Is Nothing Then    
    If ADORs.State = adStateOpen Then
       ADORs.Close
    End If
    Set ADORs = Nothing
 End If
 If Not ADOCon Is Nothing Then
    If ADOCon.State = adStateOpen Then
       ADOCon.Close
    End If
    Set ADOCon = Nothing
 End If

Anthony

P.S.  I wrote all of this code from memory and is therefore totally untested, but in principal should work.  Just let me know if you haave any problems with any of it and I will test it.
0
 
LVL 25

Author Comment

by:Mr_Peerapol
ID: 7028879
Anthony,

Thanks a lot, the second approach is seem to be what I wanted.

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7028891
Regarding the "B" grade: Was there something in my solution I did not explain well enough, you did not understand, or did not like, perhaps?

Just curious,
Anthony
0
 
LVL 25

Author Comment

by:Mr_Peerapol
ID: 7028909
About grading, if you look at my purposed question I want to get both resultset and return value from stored procedure but there is no way to do so (you said). However, I changed the requirement from return value to be the number of records in resultset and finally you gave me the right and perfect solution. So I'm not sure whether I should give you A or B because it does not meet my original requirement.

If this case (requiremnet change), I should give A. Next time, I will do or is there any way to change the grade of this question ?

So sorry and thanks again,
C
0
 
LVL 25

Author Comment

by:Mr_Peerapol
ID: 7028914
There is one line still error
>> .Source = cmd
Should be Set .Source = ADOCmd

right? just kidding ...:p

pls take my apology,
C
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7028916
I was only curious, it was not important.  If you feel strongly about it, post a message in Community Support (http://www.experts-exchange.com/commspt/) to this effect and they can change it.

Anthony
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Spell Check in VB6 13 135
converting visio 2010 to powerpoint 2010  - formatting issues 5 108
RUNRMTCMD from AS/400 12 86
VB6 - Scroll Mouse wheel on Picturebox 13 94
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

713 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