Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2005
    Posts
    21

    Unanswered: Problem sending date parameters to stored procedure

    Can anyone help,

    I have a problem with calling my stored procedure in vb and passing in its parameters. the system is supposed to then use the store the procedures recordset and use this to pass into a crystal report.

    I am getting the following message:
    Procedure 'TestReport' expects parameters '@StartDate', which was not supplied.
    This happens on the objCom.Execute line.

    This is my code:
    Code:
    Dim CRXApplication As New CRAXDRT.Application
        Dim CRXReport As CRAXDRT.Report
        Dim CRXDatabase As CRAXDRT.Database
        
        Set CRXReport = CRXApplication.OpenReport(APPConst.DatabasePath & stReport & ".rpt")
        
            Dim objCom As adodb.Command
            Set objCom = New adodb.Command
            Dim prm_one As adodb.Parameter
            Dim prm_two As adodb.Parameter
            Dim prm_three As adodb.Parameter
            Dim fdate, tdate As Date
            
            Call CRXReport.Database.Tables(1).SetLogOnInfo("dell330", "Rota", "RotaAdmin", "dadcahadfu")
            Set CRXDatabase = CRXReport.Database
        
            fdate = cboFrom.Value
            tdate = cboTo.Value
            With objCom
                .CommandText = "TestReport"
                .CommandType = adCmdStoredProc
                .ActiveConnection = cn.ConnectionString
                Set prm_one = .CreateParameter("@StartDate", adDBTimeStamp, adParamInput, 8, fdate)
                .Parameters.Append prm_one
                Set prm_two = .CreateParameter("@EndDate", adDBTimeStamp, adParamInput, 8, tdate)
                .Parameters.Append prm_two
                Set prm_three = .CreateParameter("@Team", adInteger, adParamInput, 4, 2)
                .Parameters.Append prm_three
    
                .Parameters.Refresh
            End With
            
            Set SQLRecs = objCom.Execute
        CRXReport.DiscardSavedData
        CRXDatabase.SetDataSource SQLRecs, 3, 1
    This is my Stored Procedure:
    Code:
    CREATE PROCEDURE [dbo].[TestReport]  @StartDate datetime, @EndDate datetime, @Team int  AS
    
    
    
    SELECT     dbo._Staff.UniqueName, 2 * DATEDIFF(day, @StartDate, dbo._StaffUnavailable.[Date]) + dbo._StaffUnavailable.TimeCode AS Expr1, 
                          COUNT(dbo._StaffUnavailable.ID) AS CountOfID, dbo._StaffUnavailable.[Date], dbo._Team.Description AS Team, dbo._Staff.Team_ID
    FROM         dbo._Staff INNER JOIN
                          dbo._StaffUnavailable ON dbo._Staff.Staff_ID = dbo._StaffUnavailable.Staff_ID INNER JOIN
                          dbo._Team ON dbo._Staff.Team_ID = dbo._Team.ID
    GROUP BY dbo._Staff.Staff_ID, dbo._Staff.UniqueName, 2 * DATEDIFF(day, @StartDate, dbo._StaffUnavailable.[Date]) + dbo._StaffUnavailable.TimeCode, 
                          dbo._StaffUnavailable.[Date], dbo._Team.Description, dbo._Staff.Team_ID
    Having Team_ID = @Team AND ([Date] >= @StartDate AND [Date] <= @EndDate)
    GO
    Thanks in advance

    Ian.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Ian

    I know there are some gotchas with dates and ADO but for the life of me I can't remember them. I've mocked up a couple of things to test but I don't know if my errors are due to that or not.

    Anyway - if you enumerate and loop through your parameters collection you'll see that they actually contain no values. I changed this bit:
    Code:
            With objCom
                Set prm_one = .CreateParameter("@StartDate", adDBTimeStamp, adParamInput, 8, fdate)
                .Parameters.Append prm_one
                Set prm_two = .CreateParameter("@EndDate", adDBTimeStamp, adParamInput, 8, tdate)
                .Parameters.Append prm_two
                Set prm_three = .CreateParameter("@Team", adInteger, adParamInput, 4, 2)
                .Parameters.Append prm_three
     
                .Parameters.Refresh
     
                .CommandText = "TestReport"
                .CommandType = adCmdStoredProc
                .ActiveConnection = cn.ConnectionString
            End With
    And the values stuck but I got a new error (Implicit conversion from datatype datetime to int is not allowed. Use the CONVERT function to run this query). I tested my sproc in QA but it worked fine.
    Last edited by pootle flump; 06-30-06 at 08:20.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oh yeah - simple code to enumerate:
    Code:
            For i = 0 To .Parameters.Count - 1
     
                Debug.Print .Parameters(i).name & ": " & .Parameters(i).Value
     
            Next i
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Sep 2005
    Posts
    21
    Done that, but i now get the error msg:
    Optional feature not implemented.

    Any suggestions ?

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Where's the sproc?

    Pass the date as varchar, and let the sproc convert it...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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