How to print only the specified rows from the grid view-CodePudding

Welcome I have a sales screen with an invoice that is displayed in the grid view with the report of Extra Robert I just want when I select some rows using the select box only the selected rows are printed on one page I tried this code but it prints each row in a separate report I just want to print the selected rows on one page. Thank you

for (int i = 0; i <= DgvSale.Rows.Count - 1; i  )
{
    if (Convert.ToBoolean(DgvSale.Rows[i].Cells[8].Value) == true)
    {
        tblRpt1 = db.readData("SELECT [Order_ID] as 'رقم الفاتورة',talb_ID as 'طلب',[Cust_Name] as 'اسم العميل',Products.Pro_Name as 'المنتج',Products.Group_ID as 'قسم',Products_Group.Group_Name as 'اسم القسم',[Sales_Detalis].[Qty] as 'الكمية',[Price] as 'السعر',[User_Name] as 'الكاشير',[Date] as 'التاريخ',[Unit] as 'الوحدة',[Sales_Detalis].Tax_Value as 'الضريبة',Price_Tax as 'السعر بعد الضريبة',[typetalab] as 'نوع الطلب',[priceservic] as 'خدمة',[shiftname] as 'شيفت',notes as 'ملاحظات',shiftnum as 'شيفت رقم',Print_Group.Print_Name,Print_Group.Name_Group  FROM [dbo].[Sales_Detalis] , Products,Products_Group,Print_Group where Products.Pro_ID = Sales_Detalis.Pro_ID and Products.Pro_ID= "   row1.Cells[0].Value   " and Order_ID = "   id   "  and Products.Group_ID = Products_Group.Group_ID and Print_Group.Name_Group=Products.Group_ID  ORDER BY Order_ID ASC", "");
    }
}
if (Properties.Settings.Default.SalePrintKind == "8CM")
{
    devOrderSales2 rpt = new devOrderSales2() { DataSource = tblRpt1, DataMember = "OrderSales2" };
    rpt.DataSource = tblRpt1;
    rpt.Parameters["ID1"].Value = id;
    rpt.PrinterName = Properties.Settings.Default.PrinterNameSteker;
    rpt.xrTableCell8.Visible=true;
    if (Properties.Settings.Default.saleprintauto == "0")
    {
        rpt.Print();
    }
    else if (Properties.Settings.Default.saleprintauto == "1")
    {
        rpt.ShowPreviewDialog();
    }
}

CodePudding user response：

I suggest to create the WHERE condition dynamically with the selected records:

string sql = @"SELECT [Order_ID] as 'رقم الفاتورة',talb_ID as 'طلب',[Cust_Name] as 'اسم العميل',Products.Pro_Name as 'المنتج',Products.Group_ID as 'قسم',Products_Group.Group_Name as 'اسم القسم',[Sales_Detalis].[Qty] as 'الكمية',[Price] as 'السعر',[User_Name] as 'الكاشير',[Date] as 'التاريخ',[Unit] as 'الوحدة',[Sales_Detalis].Tax_Value as 'الضريبة',Price_Tax as 'السعر بعد الضريبة',[typetalab] as 'نوع الطلب',[priceservic] as 'خدمة',[shiftname] as 'شيفت',notes as 'ملاحظات',shiftnum as 'شيفت رقم',Print_Group.Print_Name,Print_Group.Name_Group
    FROM
        [dbo].[Sales_Detalis]
        INNER JOIN Products
            ON Products.Pro_ID = Sales_Detalis.Pro_ID
        INNER JOIN Products_Group
            ON Products.Group_ID = Products_Group.Group_ID
        INNER JOIN Print_Group
            ON Print_Group.Name_Group=Products.Group_ID
    WHERE
        Products.Pro_ID IN ({0}) AND Order_ID = {1}
    ORDER BY Order_ID ASC";

var productIds = new List<int>();
for (int i = 0; i <= DgvSale.Rows.Count - 1; i  ) {
    if (Convert.ToBoolean(DgvSale.Rows[i].Cells[8].Value) == true) {
        productIds.Add(Convert.ToInt32(DgvSale.Rows[i].Cells[0].Value));
    }
}
if (Properties.Settings.Default.SalePrintKind == "8CM" && productIds.Count > 0) {
    string commandText = String.Format(sql, String.Join(", ", productIds), id);
    tblRpt1 = db.readData(commandText, "");

    var rpt = new devOrderSales2 {
        DataSource = tblRpt1,
        DataMember = "OrderSales2",
        PrinterName = Properties.Settings.Default.PrinterNameSteker
    };
    rpt.xrTableCell8.Visible = true;
    rpt.Parameters["ID1"].Value = id;
    if (Properties.Settings.Default.saleprintauto == "0") {
        rpt.Print();
    } else if (Properties.Settings.Default.saleprintauto == "1") {
        rpt.ShowPreviewDialog();
    }
}

Also, you should use the modern JOIN syntax instead of the outdated join conditions in the WHERE clause.

Note that I have set up a SQL string with {0} and {1} as placeholders later being replaced with the String.Format method. I assumed the product ids to be of type int. The resulting WHERE conditions looks something like this:

WHERE
    Products.Pro_ID IN (12, 30, 55, 68) AND Order_ID = 17

The C# verbatim strings introduced with @" allow you to include line breaks within the string and to make the SQL text more readable.

row1 always points to the same row. Get the row dynamically with DgvSale.Rows[i] instead, within the loop.

CodePudding user response：

One way is to first collect all the relevant IDs from the rows, and only do 1 db query, eg:

var toprint_ids = new List<long>();
for (int i = 0; i <= DgvSale.Rows.Count - 1; i  )
{
    if (Convert.ToBoolean(DgvSale.Rows[i].Cells[8].Value) == true)
    {
        var itm_id = Convert.ToInt64(row1.Cells[0].Value);
        ids.Add(itm_id);
    }
}

if (toprint_ids.Count == 0)
    return;

tblRpt1 = db.readData("SELECT [Order_ID] as 'رقم الفاتورة',talb_ID as 'طلب',[Cust_Name] as 'اسم العميل',Products.Pro_Name as 'المنتج',Products.Group_ID as 'قسم',Products_Group.Group_Name as 'اسم القسم',[Sales_Detalis].[Qty] as 'الكمية',[Price] as 'السعر',[User_Name] as 'الكاشير',[Date] as 'التاريخ',[Unit] as 'الوحدة',[Sales_Detalis].Tax_Value as 'الضريبة',Price_Tax as 'السعر بعد الضريبة',[typetalab] as 'نوع الطلب',[priceservic] as 'خدمة',[shiftname] as 'شيفت',notes as 'ملاحظات',shiftnum as 'شيفت رقم',Print_Group.Print_Name,Print_Group.Name_Group  FROM [dbo].[Sales_Detalis] , Products,Products_Group,Print_Group where Products.Pro_ID = Sales_Detalis.Pro_ID and Products.Pro_ID in ("   string.Join(",", toprint_ids)   ") and Order_ID = "   id   "  and Products.Group_ID = Products_Group.Group_ID and Print_Group.Name_Group=Products.Group_ID  ORDER BY Order_ID ASC", "");


if (Properties.Settings.Default.SalePrintKind == "8CM")
{
    devOrderSales2 rpt = new devOrderSales2() { DataSource = tblRpt1, DataMember = "OrderSales2" };
    rpt.DataSource = tblRpt1;
    rpt.Parameters["ID1"].Value = id;
    rpt.PrinterName = Properties.Settings.Default.PrinterNameSteker;
    rpt.xrTableCell8.Visible=true;
    if (Properties.Settings.Default.saleprintauto == "0")
    {
        rpt.Print();
    }
    else if (Properties.Settings.Default.saleprintauto == "1")
    {
        rpt.ShowPreviewDialog();
    }
}

Please notice the change in the query as well: in (" string.Join(",", toprint_ids) ") Also if you have non-numeric IDs for your rows (eg Guids), then you need to change the list to a List<string> and also change your query to like in ('" string.Join("','", toprint_ids) "')