Problem with ADO Output Parameter returning value from SQL Sproc

I hate spending hours looking for a solution, then giving up and posting on here only to find the answer immediately after I post my question!!


Here is the answer

SYMPTOMS
When using ADO and the default server side cursor is out, parameters return NULL values.
CAUSE
ADO doesn't fill in the out parameters for a recordset created using adUseServer (server side cursor) until the returned recordset is closed.


I close the recordset and I get the right values now.


Please do not answer this and return my points

Thanks

Sean

Sean, you need to raise this request in community support I think.

Hi Keyman

If you SET @RowCnt = @@ROWCOUNT
instead of
  SELECT @RowCnt = @@ROWCOUNT

you would only be returning one recordset instead of two, and the outputparam would be available to you.

may also be able to get the  outparam using the ADO NextRecordset method

Example from ADO help:

'BeginNextRecordsetVB
Public Sub NextRecordsetX()

   Dim rstCompound As ADODB.Recordset
   Dim strCnn As String
   Dim intCount As Integer

   ' Open compound recordset.
      strCnn = "Provider=sqloledb;" & _
      "Data Source=MyServer;Initial Catalog=Pubs;User Id=sa;Password=; "
   
   Set rstCompound = New ADODB.Recordset
   rstCompound.Open "SELECT * FROM Authors; " & _
      "SELECT * FROM stores; " & _
      "SELECT * FROM jobs", strCnn, , , adCmdText

   ' Display results from each SELECT statement.
   intCount = 1
   Do While Not (rstCompound Is Nothing)
      Debug.Print "Contents of recordset #" & intCount
      Do While Not rstCompound.EOF
         Debug.Print , rstCompound.fields(0), _
            rstCompound.fields(1)
         rstCompound.MoveNext
      Loop
   
      Set rstCompound = rstCompound.NextRecordset
      intCount = intCount + 1
   Loop
   
End Sub
'EndNextRecordsetVB



Alan


 

The SELECT @RowCnt = @@ROWCOUNT does not return a second record set.

It sets the value of the variable.

Here try this in you QA

Declare @V as int
SELECT @V = 1 <- This will not return a record set

SELECT @V <- Add this to the query and it will return a single row rs with 1 as the value.

Hi sean,

hmm... certainly seems to be returning only one recordset, I apologise for being wrong.
However I know that you can get the output param while the recordset is still open.
I think you are actually getting it but you have no defined size for the output in your append item, therefor you are returning the first 0 bytes of the output param, hence the empty msgbox.

This works, tested...

Function testit() As Integer

  Dim rs As ADODB.Recordset
  Dim mSalesOrderNumber As Integer
  Dim intRowCnt As Integer
 
  mSalesOrderNumber = 1
 
  Set rs = New ADODB.Recordset
   
  Dim cmd As ADODB.Command
 

  Set cmd = New ADODB.Command
  With cmd
    .CommandText = "pGetChangeOrderNumbers"
    .CommandType = adCmdStoredProc
    .Parameters.Append .CreateParameter("@BaanSalesOrderNum", adInteger, adParamInput, 4, mSalesOrderNumber)
    .Parameters.Append .CreateParameter("@RowCnt", adInteger, adParamOutput, 4)
    Set .ActiveConnection = CurrentProject.Connection
    Set rs = .Execute
    intRowCnt = .Parameters.Item("@RowCnt")
   
    With rs
      MsgBox intRowCnt
      .Close                     ' rs is still open
    End With
    Set rs = Nothing
  End With
  Set cmd = Nothing
  testit = intRowCnt

End Function



Take care


Alan

Still more than happy to refund your points, no big deal
Just give me the word..

":0)

it's a weird thing:

I can use the .nextrecordset method without error once but not twice, sorta indicating that there are two instantiated recordsets, even though query analyser doesnt show two results grids.

    With rs
      MsgBox intRowCnt
      Set rs = rs.NextRecordset           ' no error here
      Debug.Print .Fields(0)
      Set rs = rs.NextRecordset           ' error here
      Debug.Print .Fields(0)
      Set rs = rs.NextRecordset
      Debug.Print .Fields(0)
      .Close                    
    End With


go figure ...

Take care Sean


Alan

I cannot get it to work before the recordset close.

Here is my new working code.

With mcmd
        .ActiveConnection = mcnn
        .CommandType = adCmdStoredProc
        .CommandText = "pGetChangeOrderNumbers"
        .Parameters.Append mcmd.CreateParameter("@BaanSalesOrderNum", adInteger, adParamInput, , mSalesOrderNumber)
        .Parameters.Append mcmd.CreateParameter("RowCnt", adInteger, adParamOutput, 4)
        Set mrs = mcmd.Execute()
        'intRowCnt = .Parameters.Item("RowCnt")
        'MsgBox intRowCnt
    End With
   
    If Not mrs.EOF Then
        mChangeOrdersExist = True
        mChangeOrderNumbers = mrs.GetString(adClipString, ColumnDelimeter:=";", RowDelimeter:=";")
    Else
        mChangeOrdersExist = False
    End If

    mrs.Close
   
    'We must close the recordset before we can get the output parameter sent from the sproc
    If mChangeOrdersExist = True Then
        mNumberOfChangeOrders = mcmd.Parameters.Item("RowCnt")
    Else
        mNumberOfChangeOrders = 0
    End If
   
    mcnn.Close

If I unremark the lines that show the output paramater before the close of the recordset I get NULL

Hi sean,

it's sometning to do with the connection object because if I set the command.activeconnection to currentproject.connection it works, but If I instantiate a separate connection based on the  currentproject.baseconnectionstring it returns zero.

  ' This works....
With mcmd
  .ActiveConnection = CurrentProject.Connection

  .CommandType = adCmdStoredProc
  .CommandText = "pGetChangeOrderNumbers"
  .Parameters.Append mcmd.CreateParameter("@BaanSalesOrderNum", adInteger, adParamInput, 4, mSalesOrderNumber)
  .Parameters.Append mcmd.CreateParameter("RowCnt", adInteger, adParamOutput, 4)
  Set mrs = mcmd.Execute
  intRowCnt = .Parameters.Item("RowCnt")
  MsgBox intRowCnt
End With



' this fails...
  mcnn.Open CurrentProject.BaseConnectionString
 
  mSalesOrderNumber = 1
 
With mcmd
  .ActiveConnection = mcnn

  .CommandType = adCmdStoredProc
  .CommandText = "pGetChangeOrderNumbers"
  .Parameters.Append mcmd.CreateParameter("@BaanSalesOrderNum", adInteger, adParamInput, 4, mSalesOrderNumber)
  .Parameters.Append mcmd.CreateParameter("RowCnt", adInteger, adParamOutput, 4)
  Set mrs = mcmd.Execute
  intRowCnt = .Parameters.Item("RowCnt")
  MsgBox intRowCnt
End With

' this works...

  mcnn.Open CurrentProject.Connection
 
  mSalesOrderNumber = 1
 
With mcmd
  .ActiveConnection = mcnn
  .CommandType = adCmdStoredProc
  .CommandText = "pGetChangeOrderNumbers"
  .Parameters.Append mcmd.CreateParameter("@BaanSalesOrderNum", adInteger, adParamInput, 4, mSalesOrderNumber)
  .Parameters.Append mcmd.CreateParameter("RowCnt", adInteger, adParamOutput, 4)
  Set mrs = mcmd.Execute
  intRowCnt = .Parameters.Item("RowCnt")
  MsgBox intRowCnt
End With


maybe debug.? CurrentProject.connection    and compare it with your mcnn connection string.

Alan

 

Hi Sean,

gotta take a break for a little while here, I know you have it solved with closing the recordset, don't know about you but it irks me somwhat, when I know something should work, but it doesn't.

check the diff:
?CurrentProject.BaseConnectionString
PROVIDER=SQLOLEDB.1;INTEGRATED SECURITY=SSPI;PERSIST SECURITY INFO=FALSE;INITIAL CATALOG=MyData;DATA SOURCE=GONDOR

?CurrentProject.Connection
Provider=MSDataShape.1;Persist Security Info=False;Data Source=GONDOR;Integrated Security=SSPI;Initial Catalog=MyData;Data Provider=SQLOLEDB.1

One has a dataprovider and a provider, while the other only has a provider, the one that works has both.


Alan

Hmm.. well all my connections are to a remote MS SQL server.

I am not working on any linked tables through ODBC or tables withing the access MDB file itself.

Ohhh, for some reason I was under the impression that this was an ADP.


I'll check into it a bit later, not interested in points here Sean, it's a good question.
Will create an mdb frontend later and see what/if connection string works, probably the same as ASP/ADO connection

take care...

Alan

In deed that does work as well. I will award you the points...


Thanks Alan!

Sean

thanks Sean,
success with your app!

Alan