Crystal Reports trying to select from 2 tables

Question

I want to select data from 2 tables. This is the 2 tables

CREATE TABLE [dbo].[Invoice] (
[InvoiceID]    INT          IDENTITY (1, 1) NOT NULL,
[CustomerName] VARCHAR (50) NULL,
[Telephone]    CHAR (10)    NULL,
[Date]         VARCHAR (30) NULL,
[Total]        FLOAT (53)   NULL,
[Discount]     FLOAT (53)   NULL,
[ToPay]        FLOAT (53)   NULL,
CONSTRAINT [Invoice_PK1] PRIMARY KEY CLUSTERED ([InvoiceID] ASC)

CREATE TABLE [dbo].[Orders] (
[InvoiceID] INT          NOT NULL,
[ItemNO]    INT          NOT NULL,
[Category]  VARCHAR (50) NULL,
[ItemName]  VARCHAR (50) NULL,
[Price]     FLOAT (53)   NULL,
[Qty]       INT          NOT NULL,
[SubTotal]  FLOAT (53)   NULL,
CONSTRAINT [Orders_FK1] FOREIGN KEY ([InvoiceID]) REFERENCES [dbo].[Invoice] ([InvoiceID])

So I want to select data from these 2 tables by the Invoice ID. This is my codes

SqlConnection conect = new SqlConnection("Data Source=DESKTOP-R34C6VV\\SQL;Initial Catalog=Restaurant;Integrated Security=True");


            try
            {
                String str = "Data Source=DESKTOP-R34C6VV\\SQL;Initial Catalog=Restaurant;Integrated Security=True";

                String query = "select * from Invoice where InvoiceID = '" + value + "'";


                SqlConnection con = null;
                con = new SqlConnection(str);

                SqlCommand cmd = new SqlCommand(query, con);
                SqlDataReader view;

                con.Open();

                view = cmd.ExecuteReader();


                if (view.HasRows)
                {
                    if (view.Read())
                    {

                        Cus_Name = view.GetString(1);
                        Cus_Tel = view.GetString(2);
                        Date = view.GetString(3);
                        Total = view.GetDouble(4);
                        Discount = view.GetDouble(5);
                        ToPay = view.GetDouble(6);

                        PrepareBill_txt1.Clear();
                        go = 1;
if (go == 1)
        {

            Print open = new Print();

            Crystal_Bill cr = new Crystal_Bill();

            TextObject var1 = (TextObject)cr.ReportDefinition.Sections["Section1"].ReportObjects["Text17"];
            TextObject var3 = (TextObject)cr.ReportDefinition.Sections["Section1"].ReportObjects["Text19"];
            TextObject var4 = (TextObject)cr.ReportDefinition.Sections["Section1"].ReportObjects["Text20"];
            TextObject var6 = (TextObject)cr.ReportDefinition.Sections["Section1"].ReportObjects["Text22"];
            TextObject var7 = (TextObject)cr.ReportDefinition.Sections["Section4"].ReportObjects["Text23"];
            TextObject var8 = (TextObject)cr.ReportDefinition.Sections["Section4"].ReportObjects["Text24"];
            TextObject var9 = (TextObject)cr.ReportDefinition.Sections["Section4"].ReportObjects["Text25"];

            var1.Text = value;
            var3.Text = Date;
            var4.Text = Cus_Name;
            var6.Text = Cus_Tel;
            var7.Text = Total.ToString("0.00");
            var8.Text = Discount.ToString("0.00");
            var9.Text = ToPay.ToString("0.00");


            int a = System.Convert.ToInt32(value);
            cr.SetParameterValue("pInvoiceID", a);

            open.crystalReportViewer1.ReportSource = cr;

                    open.Show();

and I created a method for the other table to select from. This is my codes for that method:

 Crystal_Bill cr = new Crystal_Bill();
        SqlConnection conect = new SqlConnection("Data Source=DESKTOP-R34C6VV\\SQL;Initial Catalog=Restaurant;Integrated Security=True");
        string sql = "SELECT * from Orders WHERE InvoiceID ='"+PrepareBill_txt1.Text+"'";
        DataSet dt = new DataSet();
        SqlDataAdapter adapter = new SqlDataAdapter(sql,conect);
        adapter.Fill(dt,"Orders");
        cr.SetDataSource(dt.Tables["Orders"]);

So the method works alone fine and the other one works fine too alone, but when I try to combine them I do not get any result only the first code works the Invoic table. I tried to put that method around the the program but it did not work. This is for my college project.

When I start the program I get all the data in the database. I want to select it by the InvoiceID like the method I created. This is my Crystal Report picture:

Answer 1

As per your code, you just fetch records from the Orders table but You need to join two tables orders and Invoice and select the required column as per your need, and simply drag and drop column in crystal report as you have previously done.

i.g The query should be written something like shown below.

SELECT o.InvoiceID,
    o.CustomerName,
    o.Telephone,
    o.Date,
    o.Total,
    o.Discount, 
    o.ToPay,
    i.ItemNO,
    i.Category,
    i.ItemName,
    i.Price, 
    i.Qty, 
    i.SubTotal
FROM orders As o
INNER JOIN Invouce AS i ON o.InvoiceID = i.InvoiceID  
WHERE i.InvoiceID  = 1