Click here to Skip to main content
14,976,129 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
<pre>In my code, everything works fine except the total in dataGridView not printed in the Invoice. How to solve. Further, I need to show the column texts in bold (UID, Item & Price) & increase font size little more. the program is about to filter-out RFID UIDs, then fetch the database and load the record into DGV.


What I have tried:

pre><pre lang="C#"><pre>private void textBox1_TextChanged(object sender, EventArgs e)
        {
            if (textBox1.Text.StartsWith("[") && textBox1.Text.EndsWith("]"))
            {

                string S = textBox1.Text.Replace("[", "").Replace("]", "").Replace("Cart#1:", "");
                var values = new List<string>(S.Split(',')).Select(x => x.Trim()).ToList();
                var PositiveList = values.Where(x => x.StartsWith("+")).ToList();
                var NegativeList = values.Where(x => x.StartsWith("-")).ToList();
                for (int i = 0; i < NegativeList.Count; i++){NegativeList[i] = NegativeList[i].Replace("-", "");}
                for (int i = 0; i < PositiveList.Count; i++){PositiveList[i] = PositiveList[i].Replace("+", "");}

                foreach (string string1 in NegativeList){PositiveList.Remove(string1);}
                                                
                listBox4.DataSource = PositiveList;//listBox2.DataSource = values;//listBox3.DataSource = NegativeList;

                OleDbConnection connection = new OleDbConnection();
                connection.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Dell\OneDrive\Desktop\Database1.mdb";
                connection.Open();

                for (int i = 0; i < PositiveList.Count; i++)
                {
                    String A = PositiveList[i];
                    OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM item where UID='" + A + "'", connection);
                    DataTable dt = new DataTable();
                    oda.Fill(dt);
                    int n = dataGridView2.Rows.Add();
                    dataGridView2.Rows[n].Cells[0].Value = dt.Rows[0][1].ToString();
                    dataGridView2.Rows[n].Cells[1].Value = dt.Rows[0][2].ToString();
                    dataGridView2.Rows[n].Cells[2].Value = dt.Rows[0][3].ToString();

                }

                dataGridView2[1, dataGridView2.Rows.Count - 1].Value = "Total";

                decimal tot = 0;

                for (int j = 0; j < dataGridView2.Rows.Count; j++)
                {
                    var value = dataGridView2.Rows[j].Cells[2].Value;
                    if (value != DBNull.Value){tot += Convert.ToDecimal(value);}
                }

                if (tot == 0){  }
                dataGridView2.Rows[dataGridView2.Rows.Count - 1].Cells[2].Value = tot.ToString();

                 DGVPrinter printer = new DGVPrinter();
                 printer.Title = "Smart Shopping Invoice"; //give your report name
                 printer.SubTitleFormatFlags = StringFormatFlags.LineLimit | StringFormatFlags.NoClip;
                 printer.PageNumbers = true; // if you need page numbers you can keep this as true other wise false
                 printer.PageNumberInHeader = false;
                 printer.PorportionalColumns = true;
                 printer.HeaderCellAlignment = StringAlignment.Near;
                 printer.Footer = "footer"; //this is the footer
                 printer.FooterSpacing = 15;
                 printer.printDocument.DefaultPageSettings.Landscape = true;
                 printer.PrintDataGridView(dataGridView2);

                            

            }
        }
Posted
Updated 20-Feb-21 20:26pm

1 solution

Don't do it like that!
C#
OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM item where UID='" + A + "'", connection);

Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'
The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'
Which SQL sees as three separate commands:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'x';
A perfectly valid SELECT
SQL
DROP TABLE MyTable;
A perfectly valid "delete the table" command
SQL
--'
And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

Do something sensible with your indentation - when I see code like this:
C#
if (value != DBNull.Value){tot += Convert.ToDecimal(value);}
It says to me "the author has no idea what he is doing". It's good practice to use "unnecessary" curly brackets, but not on the same line as the condition!
And code like this doesn't help your cause either:
C#
if (tot == 0){  }

You aren't consistent which style of indexing you want to use:
C#
dataGridView2[1, dataGridView2.Rows.Count - 1].Value = "Total";
...
dataGridView2.Rows[dataGridView2.Rows.Count - 1].Cells[2].Value = tot.ToString();


And frankly, that's some "hopeful" code - as it it assumes stuff works, and hopes like heck that it does!
You read from a DB - in a loop - and don't check if any items are returned when you could build a collection of items and use a SQL IN clause instead: SQL IN Operator[^]

As for "why doesn't it print the total?" we can't tell: we have no idea what the PrintDataGridView method does, or even what the DGVPrinter class is!
I would start by looking at that, and see what it actually does, once I'd sorted out the code that calls it ...
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900