We help IT Professionals succeed at work.

Stored Procedure ADO and VB 6 - Return Recordset and Output Variable

1,544 Views
Last Modified: 2012-06-27
How Can I use this stored Proc in VB 6 with ADO and return the recordset and the RecordCount in an output variable?  I have tried a number of ways and I can either get the RS or the Output variable.  I stripped all output wording out of the STORPROC.  Bellow is the ADO VB code I am using


CREATE  proc usp_DOSELOG
@cltid int

as


SELECT  tblDose.DoseID, cltLNAME AS CNAME, tblDose.CltID, tblDose.GuestID, tblDose.dtDate,
tblDose.dtMedDate, tblDose.Dose, tblDose.strUser, tblDose.strVoidReason, tblDose.blVoid,
tblDose.bottletype, 'LETTER' = case when strvoidreason Like 'RTI%' then 'R' else case when
 strvoidreason Like 'SPILL%' then 'S' else case when blException = 1 then 'E' else '' end end end
FROM tblDose LEFT JOIN tblClient ON tblDose.CltID = tblClient.cltID
WHERE tblDose.dtDate >= (getdate()-360)  and tbldose.cltid = @cltid
order by tblDose.dtdate desc, dtmeddate desc
GO

'''VBCODE
Dim adocmd As ADODB.Command

Set adocmd = New ADODB.Command

      With adocmd
      .ActiveConnection = conn
      .CommandText = "usp_DOSELOG"
      .CommandType = adCmdStoredProc
       .Parameters.Refresh
       .Parameters(1) = lngClt

   
              rsDOSEHIST.CursorType = adOpenStatic
              rsDOSEHIST.CursorLocation = adUseClient
              rsDOSEHIST.LockType = adLockReadOnly 'adLockOptimistic
                       
     
       Set rsDOSEHIST = .Execute

       
End With

Comment
Watch Question

Commented:
.RecordCount
.Field
Dirk HaestProject manager
CERTIFIED EXPERT

Commented:
while rs.eof == false
   fieldcount = rFields.count
   For x = 0 to fieldcount-1
           msgbox rFields.Item(x).name & " = " & rFields.Item(x).value
     Next
     rs.movenext
endwhile
try this, database used is Northwind from SQL Server, just replace the connection string to suit your needs

Private Sub Command1_Click()
    Dim param As Integer, ProductName As String, NoOfRecords As Integer
    Dim rs As New ADODB.Recordset, connStr As String
    Dim conn As New ADODB.Connection
    param = 1
    connStr = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Password=;Initial Catalog=Northwind;Data Source=(local)"
    conn.Open connStr
    conn.CursorLocation = adUseClient
   
    rs.Open "EXEC MyProduct " & param, conn, adOpenDynamic, adLockOptimistic, adCmdText
    ProductName = rs("ProductName")
    NoOfRecords = rs.RecordCount
    MsgBox "Product Name: " & ProductName & vbCrLf & "Records: " & NoOfRecords
    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing
End Sub

Author

Commented:
I don;t find a stored procedure called my Product in the northwind sql db.  Also doesn't clientside cursors slow the connection way down??
I just created the MyProduct Stored Procedure to demostrate fetching of data via stored procedures which applies to your scenario. With regards to adUseClient, I don't know the adverse effect of it, I tried using adUseServer, sure it fetched the data but generated a -1 recordcount. that i do not know.

CREATE PROCEDURE MyProduct
@PID as int
 AS
select * from Products where productid=@PID
GO
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Run time error 3705 - Operation not allowed while object is open???

Author

Commented:
I got it
 It can't  be like this   Set rsDOSEHIST = .Execute
it has to be   Set rsDOSEHIST = adocmd, , 3,3

Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.