Results 1 to 8 of 8

Thread: Exec stored procedure to return ADODB.Recordset [Resolved]

Threaded View

  1. #1

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,709

    Exec stored procedure to return ADODB.Recordset [Resolved]

    I created a stored procedure in SQL that outputs a record. I need
    to have a function in vb exec the sp and return a recordset to use
    in my program.
    Stored procedure...
    Code:
    IF EXISTS(SELECT Name FROM SysObjects
          WHERE Name = 'sp_RRGetTask' AND Type = 'P')
       DROP PROCEDURE sp_RRGetTask
    GO
    
    CREATE PROCEDURE sp_RRGetTask 
       @Task_No nvarchar(8) OUTPUT
    AS 
    SELECT *
       FROM Tasks 
       WHERE Task_No = @Task_No
    GO
    Function in vb to execute it.
    VB Code:
    1. Public Function Exec_SPrs(ByRef oCnnS As ADODB.Connection, ByVal sp_Name As String) As ADODB.Recordset
    2. '<RR 09/16/2003 - VB/OUTLOOK GURU>
    3.     On Error GoTo No_Bugs
    4.    
    5.     Dim oCM As ADODB.Command
    6.     Dim lRecs As Long
    7.    
    8.     Set oCM = New ADODB.Command
    9.     oCM.ActiveConnection = oCnnS
    10.     oCM.CommandType = adCmdStoredProc
    11.     oCM.CommandText = sp_Name
    12.    
    13.     'NEED TO CHANGE FROM HERE???
    14.     oCM.Execute lRecs
    15.     If lRecs > 0 Then
    16.         gbExec_SPrs = True
    17.     Else
    18.         gbExec_SPrs = False
    19.     End If
    20.     Exit Function
    21.    
    22. No_Bugs:
    23.    
    24.     MsgBox Err.Number & " - " & Err.Description, vbOKOnly + vbInformation, App.ProductName
    25.    
    26. End Function
    This is my code for executing stored procedures that don't return anything.
    How can I complete it to return an ADODB.Recordset?
    Last edited by RobDog888; Sep 17th, 2003 at 12:59 PM.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width