Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2005
    Posts
    183

    Unanswered: VBA / ADO calling storeprocedure, no result

    Edit: Added stored procedure code snippet in the bottom. Might help you help me :-)

    Having a curiosity here that I have a hard time trying to debug.

    From Excel I'm calling a stored procedure via ADO, and despite using the exact same values that I'm calling the SP with from Query Analyzer, I don't get any result.

    Obviously, I'm doing something wrong in Excel, but can't figure out what.

    Ideas and suggestions welcome. I've tried copy pasting the essential code without tons of comment lines, so bear with me if somethings misssing (shouldnt be the case).

    Thanks, Trin

    Code:
        maanedparam.Type = adSmallInt
        aarparam.Type = adChar
        aarparam.Size = 4
        stationparam.Type = adChar
        stationparam.Size = 20
    
        kommando.ActiveConnection = noegleDNS
        kommando.CommandTimeout = 0
        kommando.CommandText = "EXCEL_noegletal_beskutogubeskyt"
        kommando.CommandType = adCmdStoredProc
    
       For Each ark In Application.Worksheets
                
            ark.Activate
            arknavn = Replace((ActiveSheet.Cells(60, 3).Value), "'", "")
    
                forrigeaar = (ActiveSheet.Cells(3, 8).Value) - 1 ' kolonnerne til venstre er forrige år
                aar = ActiveSheet.Cells(3, 8).Value
                maaned = maanedteller
    
                kommando.Parameters.Append maanedparam
                kommando.Parameters.Append aarparam
                kommando.Parameters.Append stationparam
                          
                maanedparam.Value = maaned
                aarparam.Value = forrigeaar
                stationparam.Value = arknavn
                         
                Set talRS = kommando.Execute
                
                ActiveSheet.Cells((4 + maanedteller), 2).Value = talRS!beskyt
                ActiveSheet.Cells((4 + maanedteller), 3).Value = talRS!ubeskyt
                
                Set talRS = Nothing
                
                kommando.Parameters.Delete (2)
                kommando.Parameters.Delete (1)
                kommando.Parameters.Delete (0)
    
         Next ark
    Code:
    @maaned as varchar(20),
    @aar as varchar(4),
    @skode as varchar(20)
    
    AS
    
    SELECT Sum(spig.[Afspillet beskyttede minutter]) as 'beskyt', Sum(spig.[Afspillet ubeskyttede minutter]) as 'ubeskyt'
    FROM Gramex_DW.dbo.[Spilletidspost Individuel Gramex] as spig
    WHERE datepart(mm, spig.Udsendelsesdato) = @maaned and datepart(yy, spig.Udsendelsesdato) = @aar and 
    spig.Stationskode like @skode
    Last edited by Trinsan; 08-14-12 at 06:22. Reason: added content
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

  2. #2
    Join Date
    Oct 2005
    Posts
    183

    3 changes for it to work

    Well, seems like I solved it.

    I did three changes

    1) Changed the maaned ADO parameter to Adusignedtinyint
    2) Changed the parameter in the Stored Proc to a tiny int
    3) Changed the like operator to = (equal to) in the where clause of the Stored Proc

    Whether or not the result is completely accurate, is yet to be determined. But it seems to work.

    Cheers, Trin
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

Posting Permissions

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