Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2005
    Posts
    40

    Unanswered: Stored Procedure to Return a recordset

    In a nutshell, I am trying to set a combobox's row source using a stored procedure. Surely there is an easy way to do that.

    I am working with SQL 2000 as my back-end, and my front-end is an Access Project. The stored procedure I am trying to run is on a different database then the one my project is connected to, but from what I can see in my de-bugging efforts, that is not the problem.

    The Stored Procedure;

    CREATE PROCEDURE dbo.sp_eeLinksByName
    @EmployerNum char(6)

    AS

    SELECT dbo.TIMS_eeLinksByName.eeLink, dbo.TIMS_eeLinksByName.Employee FROM dbo.TIMS_eeLinksByName
    WHERE (dbo.TIMS_eeLinksByName.eeErNum = @EmployerNum)
    ORDER BY dbo.TIMS_eeLinksByName.Employee

    returns 169 records when I run it directly from the MS Visual Studio environment.


    However whe I try to run it from VBA with the following code;

    Dim sp_eeLinksByName As String
    Dim ConnectionString As String
    Const DSeeLinksByName = "SOS-1"
    Const DBeeLinksByName = "Insync"
    Const DPeeLinksByName = "SQLOLEDB"

    Dim objeeLinksByNameConn As New ADODB.Connection
    Dim objeeLinksByNameRs As New ADODB.Recordset
    Dim objeeLinksByNameComm As New ADODB.Command

    ConnectionString = "Provider=" & DPeeLinksByName & _
    ";Data Source=" & DSeeLinksByName & _
    ";Initial Catalog=" & DBeeLinksByName & _
    ";Integrated Security=SSPI;"

    ' Connect to the data source.
    objeeLinksByNameConn.Open ConnectionString

    ' Set a stored procedure
    objeeLinksByNameComm.CommandText = sp_eeLinksByName
    objeeLinksByNameComm.CommandType = adCmdStoredProc
    Set objeeLinksByNameComm.ActiveConnection = objeeLinksByNameConn

    ' Execute the stored procedure on
    ' the active connection object...
    ' "CurrTSCalendar" is the required input parameter,
    ' objRs is the resultant output variable.
    objeeLinksByNameConn.sp_eeLinksByName CurrTSEmployer, objeeLinksByNameRs

    ' Display the result.
    'Debug.Print "Results returned from sp_CustOrdersOrders for ALFKI: "
    Select Case objeeLinksByNameRs.RecordCount
    Case 0
    'Do Nothing
    Case Is > 0
    'Get the Employee List
    objeeLinksByNameRs.MoveFirst
    Do While Not objeeLinksByNameRs.EOF
    MyControl.AddItem (objeeLinksByNameRs.Fields("eeLink") & ";" & objeeLinksByNameRs.Fields("Employee"))
    objeeLinksByNameRs.MoveNext
    Loop
    End Select

    'Clean up.
    'objRs.Close
    objeeLinksByNameConn.Close
    Set objeeLinksByNameRs = Nothing
    Set objeeLinksByNameConn = Nothing
    Set objeeLinksByNameComm = Nothing

    I get an "Object Variable or With Blick Vraiable not Set"...... for the life of me I do not know why? Does anyone have any thoughts?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi
    Which line do you get the error? Click on Debug when you receive the error (assuming you don't have an error trap) and the offending line will highlight.

    I suspect it is when you try to use the recordset.
    Code:
     objeeLinksByNameConn.sp_eeLinksByName CurrTSEmployer, objeeLinksByNameRs
    that's a new one on me. What about
    Code:
     Set objeeLinksByNameRs = objeeLinksByNameComm.Execute
    Also - you aren't passing the parameter to the sproc so that will fail too. Instantiate an ADODB parameter object, set the value and append to the command object parameters collection.
    Also, this post is probably better for the Access forum as it is really about ADO rather than SQL Server.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2005
    Posts
    40

    You're Right

    Yes, that is the line causing the error, which I still get even when I change it as you suggested. Can you give me that sproc info in sample code, or a little less techie please?

    Thanks So much....

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Just getting it for you Larry. Could you paste the line that gets highlighted please?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jan 2005
    Posts
    40

    Errors on

    It errors on the following line....

    Set objeeLinksByNameRs = objeeLinksByNameComm.Execute

    I was passing parameters with other sproc as I had shown in my original example;

    objeeLinksByNameConn.sp_eeLinksByName CurrTSEmployer, objeeLinksByNameRs

    for other sproc and it worked fine. I guess I should have been specifying parameters more formally, huh? Using New ADODB.Parameter?

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Example of using parameter object
    Code:
     
    Public Sub ADOParam()
    	Dim cnn As New ADODB.Connection
    	Dim com As New ADODB.Command
    	Dim rst As ADODB.Recordset
    	Dim prm As ADODB.Parameter
     
    	Dim strSQL As String
    	Dim i As Integer
     
    	Set rst = New ADODB.Recordset
     
    	cnn.ConnectionString = "Provider=sqloledb;Data Source=(Local);Initial Catalog=BOARD;Integrated Security=SSPI;"
     
    	cnn.Open
     
    	com.CommandText = "sp_Param"
    	com.CommandType = adCmdStoredProc
    	com.ActiveConnection = cnn
     
    	Set prm = com.CreateParameter("@Var", adVarChar, adParamInput, 10, "One")
    	com.Parameters.Append prm
     
    	Set rst = com.Execute
     
    	Do While Not rst.EOF
     
    		Debug.Print rst(0)
    		rst.MoveNext
     
    	Loop
    	cnn.Close
    	Set cnn = Nothing
     
    End Sub
    Sproc:
    Code:
     CREATE PROCEDURE [dbo].[sp_Param] 
    @Var as VarChar(10)
    AS
    select * from TOne
    where MyCol = @Var
    GO
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Another error - you never set a value for sp_eeLinksByName
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Working Code:
    Code:
    Sub BoardAdo()
    	Dim sp_eeLinksByName As String
    	Dim ConnectionString As String
    	Const DSeeLinksByName = "(Local)"
    	Const DBeeLinksByName = "BOARD"
    	Const DPeeLinksByName = "SQLOLEDB"
    	
    	Dim objeeLinksByNameConn As New ADODB.Connection
    	Dim objeeLinksByNameRs As New ADODB.Recordset
    	Dim objeeLinksByNameComm As New ADODB.Command
    	Dim CurrTSEmployer As ADODB.Parameter
    	
    	ConnectionString = "Provider=" & DPeeLinksByName & _
    	";Data Source=" & DSeeLinksByName & _
    	";Initial Catalog=" & DBeeLinksByName & _
    	";Integrated Security=SSPI;"
    	
    	' Connect to the data source.
    	objeeLinksByNameConn.Open ConnectionString
    	
    	sp_eeLinksByName = "sp_Param"
    	
    	' Set a stored procedure
    	objeeLinksByNameComm.CommandText = sp_eeLinksByName
    	objeeLinksByNameComm.CommandType = adCmdStoredProc
    	Set objeeLinksByNameComm.ActiveConnection = objeeLinksByNameConn
    	
    	' Execute the stored procedure on
    	' the active connection object...
    	' "CurrTSCalendar" is the required input parameter,
    	' objRs is the resultant output variable.
    	Set CurrTSEmployer = objeeLinksByNameComm.CreateParameter("@Val", adVarChar, adParamInput, 10, "one")
    	
    	objeeLinksByNameConn.sp_Param CurrTSEmployer, objeeLinksByNameRs
    	
    	' Display the result.
    	'Debug.Print "Results returned from sp_CustOrdersOrders for ALFKI: "
    	
    	If Not objeeLinksByNameRs.EOF Then
    		objeeLinksByNameRs.MoveNext
    		Debug.Print objeeLinksByNameRs(0)
    	End If
    	
    	'Clean up.
    	'objRs.Close
    	objeeLinksByNameConn.Close
    	Set objeeLinksByNameRs = Nothing
    	Set objeeLinksByNameConn = Nothing
    	Set objeeLinksByNameComm = Nothing
    	
    End Sub
    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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