Question Sorting and displaying access database in datagridview

Nymaaaand

New member
Joined
Jul 6, 2019
Messages
3
Programming Experience
Beginner
Hello everyone :)

I'm very bad, and I just started coding again - So bare with me.

I'm trying to build a little project/case managing app for my work but I'm having some trouble sorting an access database properbly.
I created several sorting buttons which work great, but I also want to sort by any dates before todays date - So projects that are over the due-date.

I'm trying to use this

C#:
string todayDate = DateTime.Today.ToString("MM/dd/yyyy");
            DataView dv;
            dv = new DataView(sagstyringDBDataSet.Tables[2], "forfaldsdato = 'todayDate' ", "forfaldsdato Desc", DataViewRowState.CurrentRows);
            dataGridView1.DataSource = dv;
I'm getting this error on line 3:
Exception Unhandled line 3:
System.Data.EvaluateException: 'Cannot perform '=' operation on System.DateTime and System.String.'
It works great with simple text like

C#:
  DataView dv;
            dv = new DataView(sagstyringDBDataSet.Tables[2], "Status = '5. Afventer' ", "Status Desc", DataViewRowState.CurrentRows);
            dataGridView1.DataSource = dv;
Anyone got an idea on how to do this?

Alternately how to search for projects that have due-dates before the current date and mark alle the text in that row red?

TL;DR: I need help sorting a table/Access databasse by dates in cells - trying above examples.

Hope someone can help :)!

Have a great day.

Regards
 

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
2,730
Location
Sydney, Australia
Programming Experience
10+
If you did this:
C#:
var myName = 'Nymaaaand';

MessageBox.Show("Hello myName");
would you expect your name to be displayed? I would hope not. The characters in that literal string have no connection at all to your variable. If you expect the value of that variable to be displayed then you have to actually get the value of that variable and insert it into the string. You can do that with good old concatenation:
C#:
MessageBox.Show("Hello " + myName);
or you can use String.Format:
C#:
MessageBox.Show(String.Format("Hello {0}", myName));
or string interpolation:
C#:
MessageBox.Show($("Hello {myName}"));
You need to do the same to get the value of your date into your filter (note : filter, not sort). You don;t have to do it in two steps though, as String.Format and string interpolation allow you to format on the spot. You should also be wrapping date values in '#' symbols in this context:
Code:
dv = new DataView(sagstyringDBDataSet.Tables[2], $"forfaldsdato = #{DateTime.Now:MM/dd/yyyy}#", "forfaldsdato Desc", DataViewRowState.CurrentRows);
Also, you shouldn't keep creating new DataView objects all the time. Just bind the DataTable upfront and the data you see comes from its DefaultView property, which is a DataView. You can set the Sort and RowFilter properties of that DataView any time and your UI will reflect the change. Better still, bind your DataTable to a BindingSource, which you would add in the designer, and then bind that to the UI control(s). You then set the Sort and Filter properties of the BindingSource.
 

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
2,730
Location
Sydney, Australia
Programming Experience
10+
I'm very bad, and I just started coding again - So bare with me.
If you want to take your clothes off then go ahead, but don't expect any of us to join you. I just couldn't bear it. ;)
 

Nymaaaand

New member
Joined
Jul 6, 2019
Messages
3
Programming Experience
Beginner
If you did this:
C#:
var myName = 'Nymaaaand';

MessageBox.Show("Hello myName");
would you expect your name to be displayed? I would hope not. The characters in that literal string have no connection at all to your variable. If you expect the value of that variable to be displayed then you have to actually get the value of that variable and insert it into the string. You can do that with good old concatenation:
C#:
MessageBox.Show("Hello " + myName);
or you can use String.Format:
C#:
MessageBox.Show(String.Format("Hello {0}", myName));
or string interpolation:
C#:
MessageBox.Show($("Hello {myName}"));
You need to do the same to get the value of your date into your filter (note : filter, not sort). You don;t have to do it in two steps though, as String.Format and string interpolation allow you to format on the spot. You should also be wrapping date values in '#' symbols in this context:
Code:
dv = new DataView(sagstyringDBDataSet.Tables[2], $"forfaldsdato = #{DateTime.Now:MM/dd/yyyy}#", "forfaldsdato Desc", DataViewRowState.CurrentRows);
Also, you shouldn't keep creating new DataView objects all the time. Just bind the DataTable upfront and the data you see comes from its DefaultView property, which is a DataView. You can set the Sort and RowFilter properties of that DataView any time and your UI will reflect the change. Better still, bind your DataTable to a BindingSource, which you would add in the designer, and then bind that to the UI control(s). You then set the Sort and Filter properties of the BindingSource.
Ah, of course. Thank you very much - I knew that, but I didn't completely understand the line of code I think, your comment helped a lot.

I actually ended up just changing the "=" to a "<" in the comparison and using your logic I got just what I was looking for.
Thumbs up (Y)
 
Last edited:
Top Bottom