Resolved How to Change the row color of DataGridView based on SQL Query?

[Omer Butt]

What I tried is: (But need to know what to put in if condition and what to put in foreach condition)

ViewMedicine_DataGrid is a name of DataGridView
Pharmacist_Medicine is a name of a Table in Sql Server
E_Date is a column name of Expiry Date

private void ViewMedicine_DataGrid_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
        {
            string MarkExpire = "select * from Pharmacist_Medicine where E_Date >= getdate()";
            SqlDataAdapter ExpCMD = new SqlDataAdapter(MarkExpire, Conn.Connect);
            DataTable MrkExp = new DataTable();
            ExpCMD.Fill(MrkExp);
            if ( )
            {
                foreach ( )
                {
                    row.DefaultCellStyle.BackColor = Color.Yellow;
                    row.DefaultCellStyle.BackColor = Color.Red;
                    
                }
            }
        }

Please tell in the above mentioned code what to Mention in if and foreach conditions to make that code work perfectly to change the color of the row in datagridview based on query.

 

[jmcilhinney]

If you want to do something to a row then you don't handle the CellFormatting event. Either way, you don't use a loop. Events with a name beginning with "Cell" are about doing something to a specific cell, specified by the e.ColumnIndex and e.RowIndex properties. Similarly, events with a name beginning with "Row" are about doing something to a specific row.

As for what code you need, that depends on what you actually want to do. You haven't bothered to actually explain that so we can't tell you how to do it.

based on Sql Query​

Based how? Explain.

 

[Omer Butt]

If you want to do something to a row then you don't handle the CellFormatting event. Either way, you don't use a loop. Events with a name beginning with "Cell" are about doing something to a specific cell, specified by the e.ColumnIndex and e.RowIndex properties. Similarly, events with a name beginning with "Row" are about doing something to a specific row.

As for what code you need, that depends on what you actually want to do. You haven't bothered to actually explain that so we can't tell you how to do it.

Based how? Explain.

Sir what else to explain the Question explains a lot what I want to do is to change the row color of a DataGridView on the basis of query in the code already mentioned the query and what I am missing, ok if something I am doing wrong in the code please correct thats what my question is how to what I did is what I tried as my part, Correct my part of code to tell and guide this is the right way, have you carefully ready everything in the Question or not. I care to typed other than the code but you don't bother to read the text and just see the code and saying explain, Anyway if you need explaination again, The Query is string MarkExpire = "select * from Pharmacist_Medicine where E_Date >= getdate()"; which means when Expiry Date is greater than current date it should change the row of the expired product row color change in DataGridView and please it would be better answer with the example or atleast by answering for what asked by reading carefully if there is anything wrong in the code please correct I am learning not an expert. Thats why here in this forum seeking help in learning good approach

 

[Skydiver]

But that SQL query makes no sense in that particular cell formatting event. The CellFormatting event is called for every cell in the DataGridView including the headers. This is why he is asking how can it be possibly based on the SQL query when in the query that you presented you didn't in pass in the row or column number? Further more a SQL query has no way of just pulling your so-called E_Date out of the DataGridView. So how is the cell formatting based on the SQL query?

Anyway, the correct way to go about this is when you are performing the query to populate the entire datagrid view, you also have a calculated column which has a boolean value that is the result of E_date > getdate(). Then when you are presenting the data retrieved, you do not show that calculated column. For each row where that calculated column is true, you would set the the DataGridViewRow.DefaultCellStyle to your desired style to warn the user that drug is expired.

Think about it. If you have 10 columns and 100 drugs, with your current approach of querying the database, you would query the database at least 1100 times if you don't have row headers and just column headers. If you had row headers as well, 1200 times. And that's each time the DataGridView has to redraw itself. With my suggested approach above you only perform a query once.

 

[Skydiver]

Also beware of using getdate(). If I'm not mistaken, that function returns the date on the SQL server, not on the client machine. If the server is a different time zone than the client machine you could get some weird behavior at particular times of the day.

 

[Omer Butt]

But that SQL query makes no sense in that particular cell formatting event. The CellFormatting event is called for every cell in the DataGridView including the headers. This is why he is asking how can it be possibly based on the SQL query when in the query that you presented you didn't in pass in the row or column number? Further more a SQL query has no way of just pulling your so-called E_Date out of the DataGridView. So how is the cell formatting based on the SQL query?

Anyway, the correct way to go about this is when you are performing the query to populate the entire datagrid view, you also have a calculated column which has a boolean value that is the result of E_date > getdate(). Then when you are presenting the data retrieved, you do not show that calculated column. For each row where that calculated column is true, you would set the the DataGridViewRow.DefaultCellStyle to your desired style to warn the user that drug is expired.

Think about it. If you have 10 columns and 100 drugs, with your current approach of querying the database, you would query the database at least 1100 times if you don't have row headers and just column headers. If you had row headers as well, 1200 times. And that's each time the DataGridView has to redraw itself. With my suggested approach above you only perform a query once.

Yes Sir as I mentioned my code is what I tried is a must to show my effort what I did I know its wrong but shown otherwise if I didn't then the answer be like I haven't bother to try myself or even bother to show what I did, that's why shown my part.

But besides I also explained what I wanted to achieve and I know I did it wrong I questioned based on Query because my try was based on Query which as Now I understand is not a right way please if one of you guide the right way of achieving that part. As for Understanding the situation again, Data is added successfully to the table by the user (Where Dates are not manually entered but are DateTimePicker) and also on the dashboard using the query: select count(Med_Name) from Pharmacist_Medicine where E_Date <= getdate() for the chart to display no of expired medicine. This is what I want to achieve is in the View Medicines user control. Where There is a search box, a data grid and a delete button to delete a medicine if wanted. Till here everything shown and worked fine but I wanted in the DataGridView the medicine record that is expired that row color changed according to the Date Expired. Even if no Query. I edited the Question above from base on Query to Based on condition and the condition is If the Date is greater than and equal to Expired then row color is red if one month left in the medicine to be expired then the Row color is Yellow please if you can show a sample code on how to achieve this situation

 

[Skydiver]

I edited the Question above from base on Query to Based on condition

Please don't do that in the future in this forum. This is not StackOverflow where you keep going back to the original question and massage it based on the comments and responses. This is a standard forum where there is chronological order to the posts. Now posts #2, #3, and #4 make it look like we were on drugs and didn't read the first post.

 

[Omer Butt]

Please don't do that in the future in this forum. This is not StackOverflow where you keep going back to the original question and massage it based on the comments and responses. This is a standard forum where there is chronological order to the posts. Now posts #2, #3, and #4 make it look like we were on drugs and didn't read the first post.

Ok, I turned it back to SQL Query but again am I going to get the solution or you will again felt like on drugs that I again set it back to Sql Query just to get you out of that on drugs feeling, So you can see #2, #3 and #4 in right order. Now if I can get a solution.

 

[Skydiver]

Until you changed the parameters of what you are trying to do in post #6 , a solution was recommended in the second paragraph of post #4.

With your other ways of doing things in post #6, then you don't even have to add an extra computed column that you don't show. Simply include the expiration date column as part of your regular query. Then as previously described, modify the row level style instead of a per cell style based on the expiration date column value.

 

[Omer Butt]

Well after a lots and lots of tries according to what suggested here nothing worked and I researched and then tried this way to achieve what I wanted and explained above posting here for the reference for other. As Resolved

Used RowPrePaint Event to achieve the desired result

private void ViewMedicine_DataGrid_RowPrePaint(object sender, DataGridViewRowPrePaintEventArgs e)
        {
            var ExpiryDate = Convert.ToDateTime(ViewMedicine_DataGrid.Rows[e.RowIndex].Cells[7].Value);
            var TodayDate = DateTime.Today;
            var Expired = ExpiryDate >= TodayDate;
            var ExpiredToBe = ExpiryDate >= TodayDate.AddDays(-30);
            if (Expired)
            {
                ViewMedicine_DataGrid.Rows[e.RowIndex].DefaultCellStyle.BackColor = Color.LightCoral;
            }
            else if (ExpiredToBe)
            {
                ViewMedicine_DataGrid.Rows[e.RowIndex].DefaultCellStyle.BackColor = Color.Gold;
            }
        }

 

[Skydiver]

I'm glad that you found a solution that works for the way you think. Consider though that with your approach, your code will be hit 101 times if you have 100 rows every time the control repaints. It's not like your data will change unless you load new data into the DataGridView. You could just set the styles at the end of the load operation.