?
Solved

Problem with ADO Output Parameter returning value from SQL Sproc

Posted on 2004-11-27
15
Medium Priority
?
6,835 Views
Last Modified: 2009-12-16

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?


0
Comment
Question by:Keyman
  • 8
  • 5
14 Comments
 

Author Comment

by:Keyman
ID: 12686521
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
 
LVL 16

Expert Comment

by:muzzy2003
ID: 12686654
Sean, you need to raise this request in community support I think.
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12688469
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:Keyman
ID: 12688501
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
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12688586
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
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12688616
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
 

Author Comment

by:Keyman
ID: 12688691
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
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12688757
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
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12688787
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
 

Author Comment

by:Keyman
ID: 12688791
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
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12688809
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
 
LVL 26

Accepted Solution

by:
Alan Warren earned 500 total points
ID: 12689201
This fixes it Sean

tested from an mdb...

  With mcnn
    .CursorLocation = adUseClient
    .Open strConnect
  End With

Alan
0
 

Author Comment

by:Keyman
ID: 12690605
In deed that does work as well. I will award you the points...


Thanks Alan!

Sean

0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12692457
thanks Sean,
success with your app!

Alan
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

809 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