• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3950
  • Last Modified:

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

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
Mr_Peerapol
Asked:
Mr_Peerapol
  • 7
  • 4
  • 3
1 Solution
 
Anthony PerkinsCommented:
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
 
Steve SiricaSenior Web Application EngineerCommented:
Have you tried returning multiple recordsets and itterating thru them?

Steve
0
 
Steve SiricaSenior Web Application EngineerCommented:
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
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Steve SiricaSenior Web Application EngineerCommented:
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
 
Anthony PerkinsCommented:
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
 
Mr_PeerapolAuthor Commented:
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
 
Anthony PerkinsCommented:
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
 
Anthony PerkinsCommented:
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
 
Anthony PerkinsCommented:
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
 
Mr_PeerapolAuthor Commented:
Anthony,

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

0
 
Anthony PerkinsCommented:
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
 
Mr_PeerapolAuthor Commented:
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
 
Mr_PeerapolAuthor Commented:
There is one line still error
>> .Source = cmd
Should be Set .Source = ADOCmd

right? just kidding ...:p

pls take my apology,
C
0
 
Anthony PerkinsCommented:
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
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 7
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now