An expression of non-boolean type

Thank you Prasad, it is working now

Hello Jignesh Kumar, 

Please I applied your code and am error popup saying invalid mdate column

Hello Prasad,

please this is my code, as I sald when I remode the amount column that is loaded into the dataGridView but when I include the amount column the error popup. thank you

 private void btnLoadData_Click(object sender, EventArgs e)
        {

            double _total = 0;
            int i = 0;
            dataGridView3.Rows.Clear();
            con.Open();
            cmd = new SqlCommand(@" SELECT p.ProID, p.pName, d.price, d.qty, SUM(d.amount) FROM tblDetails AS d 
            INNER JOIN tblProduct AS p ON d.ProductID = p.ProID
                INNER JOIN tblMain AS m ON d.dMainID = m.MainID
                WHERE m.mDate BETWEEN @StartDate AND @EndDate
                GROUP BY p.ProID, p.pName, d.price, d.qty
            ", con);
            cmd.Parameters.AddWithValue("@StartDate", dtSales1.Value.Date);
            cmd.Parameters.AddWithValue("@EndDate", dtSales2.Value.Date);
           
            dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                i += 1;
                _total += Double.Parse(dr["amount"].ToString());
                dataGridView3.Rows.Add(i, dr["ProID"].ToString(), dr["pName"].ToString(), Double.Parse(dr["price"].ToString()).ToString("#,##0.00"), dr["qty"].ToString(), Double.Parse(dr["amount"].ToString()).ToString("#,##0.00"));
            }
            dr.Close();
            con.Close();
            lblTotal.Text = _total.ToString("Ghc #,##0.00");

            con.Open();
            cmd = new SqlCommand("select isnull(sum(amount),0) from tblDetails  where mDate between'" + dtSales1.Value.ToString("dd-MM-yyyy") + "'and '" + dtSales2.Value.ToString("dd-MM-yyyy") + "'", con);
            lblTotal.Text = Double.Parse(cmd.ExecuteScalar().ToString()).ToString("#,##0.00");
            con.Close();

        }

Hello,

Please change use Isnull check for your amount column,

select p.ProID, p.pName,d.price,d.qty,sum(IsNull(d.amount,0)) from tblDetails as d inner join tblProduct as p on d.ProductID=p.ProID where mDate between'%" + dtSales1.Value.ToString("dd-MM-yyyy") + "'and '" + dtSales1.Value.ToString("dd-MM-yyyy") + "%'group by p.pName, d.price, d.qty, IsNull(d.amount,0)

select sum(isnull(amount,0)) from tblDetails where  where mDate between'" + dtSales1.Value.ToString("dd-MM-yyyy") + "'and '" + dtSales2.Value.ToString("dd-MM-yyyy") + "

Hello Emmanual, I may need the piece of code to check. Which line throwing this exception?

If you're encountering a System.IndexOutOfRangeException specifically within the System.Data namespace, it's likely related to working with data structures like DataTables, DataRows, or data access using indices that don't exist.

For instance, this kind of exception might occur when working with DataTables or DataRows if you're trying to access a column that doesn't exist or trying to access a row that is out of the range.

Hello Prasad,

this is another error coming up and when I remove the amount column the dataGridView is able to fetch all the data except the amount and when I include the amount it brings the error again

Well noted with thanks Prasad, I will check and revert please

To resolve the error, here are a few things to check and potentially modify in your code:

1. Column Naming: Ensure that the column name "mDate" exists in the tables tblMain, tblProduct, and tblDetails.

2. Alias Usage: When you are performing a SQL join with multiple tables and filtering by date, make sure you use the correct alias for the date column. If mDate is in tblMain, use the correct alias when referencing it in the query.

Here's an example based on your code:

cmd = new SqlCommand(@"
    SELECT p.ProID, p.pName, d.price, d.qty, SUM(d.amount)
    FROM tblDetails AS d
    INNER JOIN tblProduct AS p ON d.ProductID = p.ProID
    INNER JOIN tblMain AS m ON d.MainID = m.MainID
    WHERE m.mDate BETWEEN @StartDate AND @EndDate
    GROUP BY p.ProID, p.pName, d.price, d.qty
", con);

cmd.Parameters.AddWithValue("@StartDate", dtSales1.Value.Date);  // Assuming dtSales1 is your start date parameter
cmd.Parameters.AddWithValue("@EndDate", dtSales2.Value.Date);  // Assuming dtSales2 is your end date parameter

// Execute the command and process the results...

This assumes that mDate is in the tblMain table, and it's linked to the other tables through a column like MainID.

Also, it's a good practice to use parameterized queries to prevent SQL injection and improve query performance.

Make sure to adjust the table and column names, as well as the join conditions (ON clauses) according to your actual table structure.

Additionally, always handle exceptions and error messages within a try-catch block to catch potential errors in your SQL queries and connections. This allows you to debug more effectively.

Remember to verify your table structure and adjust the SQL query according to your actual database schema to ensure accuracy.