Solved

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

Posted on 2002-05-22
14
3,845 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
 
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

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…
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…
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…

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now