How Can I Query Multiple Tables for My Crystal Reports Using VB and ODBC?-Mysql Tutorial-php.cn

How Can I Query Multiple Tables for My Crystal Reports Using VB and ODBC?

Barbara Streisand

Release： 2024-11-24 16:58:11

Original

457 people have browsed it

How Can I Query Multiple Tables for My Crystal Reports Using VB and ODBC?

Querying Multiple Tables for Crystal Reports

This article addresses a common question encountered when generating reports using ODBC and Crystal Reports in Visual Basic. The question seeks assistance in incorporating a SQL query that joins multiple tables into the report.

Placing the Query

To incorporate the provided query into your Crystal Report, navigate to the Report Wizard and locate the ODBC connection you established with a username and password. Within the wizard, you will find a field labeled "Command Text." This is where you can enter your SQL query.

SELECT ts.`SCHEDIDNO`,
       ts.`DAYNAME`,
       DATE_FORMAT(ts.`TIMESTART`, '%h:%i %p') as TIMESTART,
       DATE_FORMAT(ts.`TIMEEND`, '%h:%i %p') as TIMEEND,
       ts.`GRADELEVEL`,
       ts.`SECTIONNAME`,
       ts.`SUBJECTNAME`,
       ts.`FACFULLNAME`,
       ts.`ROOMNAME`,
       tf.`FACFULLNAME` as PERSONNEL,
       tf.`DEPARTMENT`,
       tse.`Adviser`
FROM `tblschedule` ts,
     `tblfaculty` tf,
      `tblsection` tse
WHERE ts.`GRADELEVEL` = " &amp; lblgrade.Text &amp; "
    AND ts.`SECTIONNAME` = '" &amp; lblsect.Text &amp; "'
    AND ts.`DEPARTMENTNAME` = tf.`DEPARTMENT`
    AND tf.`ADMINISTRATOR` = 1
    AND tse.`SECTIONNAME` = '" &amp; lblsect.Text &amp; "'

Copy after login

Sample Code for Reference

Below is example code to guide you in incorporating your query into the report:

VB.NET

Private CRPT As New ReportDocument
Private APPPATH As String
Private PARAM As New ParameterFields
Public PARAM_DESC As New ParameterDiscreteValue
Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load

    APPPATH = Application.StartupPath & "\REPORT\sample.rpt"
    CRPT.Load(APPPATH)
    Me.CrystalReportViewer1.ReportSource = CRPT

    PARAM = CRPT.ParameterFields

    PARAM_DESC.Value = Format(DatePicker1.Value, "yyyyMMdd")
    PARAM("DATEP").CurrentValues.Clear()
    PARAM("DATEP").CurrentValues.Add(PARAM_DESC)
    Me.CrystalReportViewer1.Refresh()
End Sub

Copy after login

CRPT = new ReportDocument();
APPPATH = Environment.CurrentDirectory + "Sample.rpt";
CRPT.Load(APPPATH);
Report_Viewer.Refresh();
CRPT.SetParameterValue("syear", Servercls.year);


CRPT.SetParameterValue("smonth", Servercls.month);
CRPT.SetParameterValue("sday", Servercls.day);
CRPT.SetParameterValue("datevalue", Servercls.Datevalue);
Report_Viewer.ReportSource = CRPT;

sc.configureCrystalReport();
Report_Viewer.Refresh();

Copy after login

Considerations

Ensure that the parameter names in your Crystal Report match those specified in your code base. By implementing these instructions, you can effortlessly retrieve data from multiple tables and generate a comprehensive report using Crystal Reports in your VB application.

The above is the detailed content of How Can I Query Multiple Tables for My Crystal Reports Using VB and ODBC?. For more information, please follow other related articles on the PHP Chinese website!