crystal reports

[steve_rm]

Hello

I have a database of a library system. I create a report that will display all the overdue books using Visual C#. I have looked at the wizard for creating the reports. But nothing that allows me to do this type of query.

SELECT BookRef, Title, DateDue FROM Book WHERE DateDue < '" + txtCurrentDate.Text + "' ";

But l am not sure how to create a report that will execute this query.

Thanks in advance,

Steve

[techgnome]

What DBMS are you using? If you are using SQL Server (or even Oracle) you can use a stored procedure with a paramter, then point the report to the SP and run it that way.

If you are using Access, then a parameterized query is the way to go.

Tg

[steve_rm]

I am using MS Access. Do you have any working examples of using the parameterized query.

Thanks,

Steve

[appdalesolution]

Here is some code i use within Visual Basic that returns a recordset from a stored procedure in SQL Server and then assigns the recordset to the report.

Its a multi tier app so ive provided the client code first then the component code and finally the stored procedure code:

Client Code:

Private Sub mnuReportSelectedClientsAuditProgramme_Click()
    Dim rptAuditProgramme As New rptAuditProgramme
    Dim rstAuditMain As ADODB.Recordset
    Dim rstAuditStarts As ADODB.Recordset
    Dim oClient As ChurSMS.clsClient
    Dim rptSub As CRAXDDRT.Report
    
    On Error GoTo err_AuditProg
    Set objHourglass = New clsHourglass
    If flxClient.Row <> 0 And Not IsNull(dtFrom) And Not IsNull(dtTo) Then
        Set oClient = CreateObject("ChurSMS.clsClient")
        Set rstAuditMain = oClient.GetAuditProgramme(flxClient.TextMatrix(flxClient.Row, 1), dtFrom, dtTo, rstAuditStarts)
        Set oClient = Nothing
        rstAuditMain.Filter = GetFilteredClients
        If Not rstAuditMain.EOF Then
            rptAuditProgramme.Database.SetDatasource rstAuditMain
            Set rptSub = rptAuditProgramme.OpenSubreport("rptAuditProgStarts")
            rptSub.Database.SetDatasource rstAuditStarts
            rptAuditProgramme.ReadRecords
            rptAuditProgramme.txtDates.SetText "Counts between: " & Format(dtFrom.Value, "dd/mm/yyyy") & " and " & Format(dtTo, "dd/mm/yyyy")
            Set frmReportViewer.m_oReport = rptAuditProgramme
            frmReportViewer.m_sReportCaption = "Audit Programme"
            frmReportViewer.crvReportViewer.EnableExportButton = True
            frmReportViewer.Show
            Set rptAuditProgramme = Nothing
        End If
    End If
    Call UnloadHourglass
    Exit Sub
    
err_AuditProg:
    Call ErrorHandler
End Sub

DLL Code:

Public Function GetAuditProgramme(ByVal lClientID As Long, ByVal dtDateFrom As Date, ByVal dtDateTo As Date, Optional ByRef rstAuditStarts As ADODB.Recordset) As ADODB.Recordset
 
    '*****************************************************************************
    'Purpose:   Return recordset containing specified client's audit programme
    'Inputs:    lClientID:  Long value containing the specified client id number
    'Returns:   Disconnected ADO recordset object
    '*****************************************************************************
    
    Dim CN As ADODB.Connection
    Dim comGetAuditProgramme As ADODB.Command
    Dim comGetAuditProgrammeStarts As ADODB.Command
    Dim rstAuditProgramme As ADODB.Recordset
    
    On Error GoTo err_GetAuditProgramme
    Set CN = CreateObject("ADODB.Connection")
    CN.Open m_sConnect
    Set comGetAuditProgramme = CreateObject("ADODB.Command")
    Set comGetAuditProgrammeStarts = CreateObject("ADODB.Command")
    Set rstAuditProgramme = CreateObject("ADODB.Recordset")
    With comGetAuditProgramme
        .ActiveConnection = CN
        .CommandType = adCmdStoredProc
        .CommandText = "sp_GetClientAuditProgramme"
        .Parameters.Append .CreateParameter("ClientID", adInteger, adParamInput, , lClientID)
        .Parameters.Append .CreateParameter("DateFrom", adDate, adParamInput, , Format(dtDateFrom, "dd/mm/yyyy"))
        .Parameters.Append .CreateParameter("DateTo", adDate, adParamInput, , Format(dtDateTo, "dd/mm/yyyy"))
    End With
    With rstAuditProgramme
        .CursorLocation = adUseClient
        .CursorType = adOpenStatic
        .LockType = adLockReadOnly
        Set .Source = comGetAuditProgramme
        .Open
        Set .ActiveConnection = Nothing
    End With
    If Not IsMissing(rstAuditStarts) Then
        Set rstAuditStarts = CreateObject("ADODB.Recordset")
        With comGetAuditProgrammeStarts
            .ActiveConnection = CN
            .CommandType = adCmdStoredProc
            .CommandText = "sp_GetClientAuditProgrammeStarts"
            .Parameters.Append .CreateParameter("ClientID", adInteger, adParamInput, , lClientID)
            .Parameters.Append .CreateParameter("DateFrom", adDate, adParamInput, , Format(dtDateFrom, "dd/mm/yyyy"))
            .Parameters.Append .CreateParameter("DateTo", adDate, adParamInput, , Format(dtDateTo, "dd/mm/yyyy"))
        End With
        With rstAuditStarts
            .CursorLocation = adUseClient
            .CursorType = adOpenStatic
            .LockType = adLockReadOnly
            Set .Source = comGetAuditProgrammeStarts
            .Open
            Set .ActiveConnection = Nothing
        End With
    End If
    Set GetAuditProgramme = rstAuditProgramme
    Set CN = Nothing
    GetObjectContext.SetComplete
    Exit Function
err_GetAuditProgramme:
    GetObjectContext.SetAbort
    Err.Raise Err.Number
End Function

Stored Procedure Code:

Alter Procedure sp_GetClientAuditProgramme
	(
		@ClientID int,
		@DateFrom datetime,
		@DateTo datetime
	)
AS
SELECT     dbo.tblClient.CLT_Name, 
                      dbo.tblClientContact.CLC_Title + ' ' + dbo.tblClientContact.CLC_Forenames + ' ' + dbo.tblClientContact.CLC_Surname AS ClientContactName, 
                      dbo.tblClientContact.CLC_JobTitle, dbo.tblSite.SIT_Name, dbo.tblSite.SIT_ClientSiteNumber, dbo.tblJob.JOB_Date, 
                      dbo.tblSiteContact.STC_Title + ' ' + dbo.tblSiteContact.STC_Forenames + ' ' + dbo.tblSiteContact.STC_Surname AS SiteContactName, 
                      dbo.tblClient.CLT_ID, dbo.tblJob.JOB_StaffRequirement, dbo.tblJob.JOB_ID, dbo.tblSite.SIT_Address1, dbo.tblSite.SIT_Address2, 
                      dbo.tblCounty.CTY_Name, dbo.tblCountry.CRY_Name, dbo.tblSite.SIT_Address3, dbo.tblSite.SIT_PostCode, dbo.tblJobGroup.JGP_GroupID, 
                      COUNT(dbo.view_AuditStaffCount.STW_JobID) AS ActualStaff, dbo.tblClient.CLT_ChargeUnitsDiffMargin, dbo.tblClient.CLT_ChargePercentDiffMargin
FROM         dbo.tblClient INNER JOIN
                      dbo.tblSite ON dbo.tblClient.CLT_ID = dbo.tblSite.SIT_ClientID INNER JOIN
                      dbo.tblJob ON dbo.tblSite.SIT_ID = dbo.tblJob.JOB_SiteID LEFT OUTER JOIN
                      dbo.view_AuditStaffCount ON dbo.tblJob.JOB_ID = dbo.view_AuditStaffCount.STW_JobID LEFT OUTER JOIN
                      dbo.tblJobGroup ON dbo.tblJob.JOB_ID = dbo.tblJobGroup.JGP_JobID LEFT OUTER JOIN
                      dbo.tblCountry ON dbo.tblSite.SIT_CountryID = dbo.tblCountry.CRY_ID LEFT OUTER JOIN
                      dbo.tblCounty ON dbo.tblSite.SIT_CountyID = dbo.tblCounty.CTY_ID LEFT OUTER JOIN
                      dbo.tblSiteContact ON dbo.tblCounty.CTY_ID = dbo.tblSiteContact.STC_CountyID AND dbo.tblCountry.CRY_ID = dbo.tblSiteContact.STC_CountryID AND 
                      dbo.tblSite.SIT_ID = dbo.tblSiteContact.STC_SiteID LEFT OUTER JOIN
                      dbo.tblClientContact ON dbo.tblClient.CLT_ID = dbo.tblClientContact.CLC_ClientID
GROUP BY dbo.tblClient.CLT_Name, dbo.tblClientContact.CLC_Title + ' ' + dbo.tblClientContact.CLC_Forenames + ' ' + dbo.tblClientContact.CLC_Surname, 
                      dbo.tblClientContact.CLC_JobTitle, dbo.tblSite.SIT_Name, dbo.tblSite.SIT_ClientSiteNumber, dbo.tblJob.JOB_Date, 
                      dbo.tblSiteContact.STC_Title + ' ' + dbo.tblSiteContact.STC_Forenames + ' ' + dbo.tblSiteContact.STC_Surname, dbo.tblClient.CLT_ID, 
                      dbo.tblJob.JOB_StaffRequirement, dbo.tblJob.JOB_ID, dbo.tblSite.SIT_Address1, dbo.tblSite.SIT_Address2, dbo.tblCounty.CTY_Name, 
                      dbo.tblCountry.CRY_Name, dbo.tblSite.SIT_Address3, dbo.tblSite.SIT_PostCode, dbo.tblJobGroup.JGP_GroupID, dbo.tblClient.CLT_ChargeUnitsDiffMargin, 
                      dbo.tblClient.CLT_ChargePercentDiffMargin
HAVING      (dbo.tblJob.JOB_Date >= CONVERT(DATETIME, @DateFrom, 103)) AND (dbo.tblJob.JOB_Date <= CONVERT(DATETIME, @DateTo, 103)) OR
                      (dbo.tblJob.JOB_Date >= CONVERT(DATETIME, @DateFrom, 103)) AND (dbo.tblJob.JOB_Date <= CONVERT(DATETIME, @DateTo, 103)) OR
                      (dbo.tblJob.JOB_Date >= CONVERT(DATETIME, @DateFrom, 103)) AND (dbo.tblJob.JOB_Date <= CONVERT(DATETIME, @DateTo, 103))

With the actual report you need to assign an Active Data Source and assign either a field definition text file or a dummy table or view.

Hope this helps im sure you should be able to adapt this for Access.

[Asgorath]

Hi Steve
You can add a typed dataset of your access table Book to solution.
Then create a add a new crystal report and use the wizard. Select Project Files > Ado.NEt Dataset > select your typed dataset > select table Book.

Finish designing the report normally.

The crystalReport has a property SetDataSource pass a dataset with the result of your query : SELECT BookRef, Title, DateDue FROM Book WHERE DateDue < '" + txtCurrentDate.Text + "' " .
Display the report normally on the CrystalReportViewer class.

Regards
Jorge