Problem with ADO Output Parameter returning value from SQL Sproc


Here is my VBA code

    mcmd.ActiveConnection = mcnn
    mcmd.CommandType = adCmdStoredProc
    mcmd.CommandText = "pGetChangeOrderNumbers"
    mcmd.Parameters.Append mcmd.CreateParameter("@BaanSalesOrderNum", adInteger, adParamInput, , mSalesOrderNumber)
    mcmd.Parameters.Append mcmd.CreateParameter("RowCnt", adInteger, adParamOutput)
    Set mrs = mcmd.Execute()
    MsgBox mcmd.Parameters(1)
    MsgBox mcmd.Parameters("RowCnt")


Here is my Sproc from MS SQL

CREATE  PROC pGetChangeOrderNumbers
      @SO int,
      @RowCnt int = NULL output
AS
SELECT     C.ChangeOrderNum
FROM         tblChangeOrder C
WHERE     (C.BaanSalesOrderNum = @SO)

SELECT @RowCnt = @@ROWCOUNT


The Msgbox is empty. I do not get the Rowcount value.

When I run the sproc in the QA using the same input value it returns the proper rowcount.

Can anyone help?


KeymanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

KeymanAuthor Commented:
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
0
muzzy2003Commented:
Sean, you need to raise this request in community support I think.
0
Alan WarrenApplications DeveloperCommented:
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


 
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

KeymanAuthor Commented:
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.
0
Alan WarrenApplications DeveloperCommented:
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)
0
Alan WarrenApplications DeveloperCommented:
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
0
KeymanAuthor Commented:
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

0
Alan WarrenApplications DeveloperCommented:
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

 
0
Alan WarrenApplications DeveloperCommented:
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
0
KeymanAuthor Commented:
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.

0
Alan WarrenApplications DeveloperCommented:
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

0
Alan WarrenApplications DeveloperCommented:
This fixes it Sean

tested from an mdb...

  With mcnn
    .CursorLocation = adUseClient
    .Open strConnect
  End With

Alan
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
KeymanAuthor Commented:
In deed that does work as well. I will award you the points...


Thanks Alan!

Sean

0
Alan WarrenApplications DeveloperCommented:
thanks Sean,
success with your app!

Alan
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

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.