Make a Crystal Report with data fetched from two differents tables

Question

Hi, Im using vb.net and I need to fetch data from two different tables and display it in form of report. These are the schemas and data of my two tables:

CREATE TABLE personal_details (staff_ID integer PRIMARY KEY, title varchar(10), fn varchar(250), ln varchar(250), mn varchar(250), dob varchar(50), hometown varchar(250), securityno varchar(50), phone varchar(15), phone2 varchar(15), phone3 varchar(15), email varchar(250), address varchar(300), confirmation varchar(50), retirement varchar(50), designation varchar(250), region varchar(250));
INSERT INTO personal_details VALUES(1,'Mr.','Selom','AMOUZOU','Kokou','Sunday, March 28, 2010','Ho',7736,'024-747-4883','277-383-8383','027-838-3837','[email protected]','Lapaz Kum Hotel','Sunday, March 28, 2010','Sunday, March 28, 2010','Designeur','Brong Ahafo');


CREATE TABLE training(
    training_ID integer primary key NOT NULL,
    staff_ID varchar(100),
    training_level varchar (60),
    school_name varchar(100),
    start_date varchar(100), end_date varchar(100));
INSERT INTO training VALUES(1,1,'Primary School','New School','Feb 1955','May 1973');
INSERT INTO training VALUES(2,1,'Middle/JSS','Ipmc','Feb 1955','May 1973');

Im trying to fetch and display data from the tables above the following way:

Dim rpt As New CrystalReport1()
            Dim da As New SQLiteDataAdapter
            Dim ds As New presbydbDataSet
            ds.EnforceConstraints = False

If conn.State = ConnectionState.Closed Then
                conn.Open()
            End If

Dim cmd As New SQLiteCommand("SELECT p.fn, t.training_level FROM personal_details p INNER JOIN training t ON p.staff_ID = t.staff_ID", conn)
            cmd.ExecuteNonQuery()
            da.SelectCommand = cmd
            da.Fill(ds)

            rpt.SetDataSource(ds)
            CrystalReportViewer1.ReportSource = rpt
            conn.Close()

My problem is that nothing displays on the report unless I take off either the training fields or the personal_details fields from the report. Need your help. Thanks

Answer 1

My guess (since you don't specify what database engine you're using) is that you're unsuccessfully comparing the varchar staff_id with the integer staff_id. Try defining training.staff_id as an integer as well.