Question DataTable Sorting By Date

[greggfromnebraska]

Hi all, quick question here about a program I’m trying to make in VB.NET in Visual Studio 2010. The (simplified) premise of what I want the program to do is that I want my program to do is that I’d like for it to allow the user to input the name and meeting date for clients of theirs (as a date) and show up in a DataGridView box. I’d like for it to then save the data that the user inputs to an XML file so that they can pull it up later and make edits, change meeting dates, delete contacts, and such. Also, I really need for it to be able to sort by the meeting dates from most to least current.
My problem is this – it keeps treating the date information as just a string, and it sorts it accordingly (“1/1/2015” shows up before “2/1/2013” and the like). I have tried everything imaginable to try to solve this issue, but have hit various brick walls along the way.
When the program opens, the first thing it does is makes the DataTable and the columns, and then reads the xml data into it using the following code:

[The following are the declarations in the module]

Public contactTable As DataTable
Public contactName As DataColumn = New DataColumn(“Contact Name”)
Public meetingDate As DataColumn = New DataColumn(“Meeting Date”)

[The following is the main form loading]

contactTable = New DataTable(“Contacts List”)
contactTable.Columns.Add(contactName)
contactTable.Columns.Add(meetingDate)
contactTable.ReadXml(“Contacts.xml”)
DataGridView1.DataSource = contactTable

And then the DataGridView goes ahead and does its thing treating all of the data as strings. I’m already well aware that the DataType needs to be changed for the meetingDate DataColumn, so I’ve tried several solutions there:

[Changed its declaration in the module]

Public meetingDate As DataColumn = New DataColumn(“Meeting Date”, System.Type.GetType(Date))

All that accomplished was that suddenly NOTHING appeared in the DataGridView at all, so I took that away and changed it so it would declare after the columns had been added to the table:

[Changed in the main form loading]

contactTable = New DataTable(“Contacts List”)
contactTable.Columns.Add(contactName)
contactTable.Columns.Add(meetingDate)
contactTable.Columns(“Meeting Date”).DataType = GetType(Date)
contactTable.ReadXml(“Contacts.xml”)
DataGridView1.DataSource = contactTable

That did the exact same thing as what had happened before with the module declaration change – nothing showed up in the DataGridView. So I tried manually going in with a For loop after the xml file had loaded:

[Changed in the main form loading]

contactTable = New DataTable(“Contacts List”)
contactTable.Columns.Add(contactName)
contactTable.Columns.Add(meetingDate)
contactTable.ReadXml(“Contacts.xml”)
For Each row In contactTable.Rows
                row(“Next Action”) = CDate(row(“Next Action”))
Next
DataGridView1.DataSource = contactTable

All that did was made it so that when the data showed up in my DataGridView, it appeared as “1/1/2015 12:00:00 AM” instead of the “1/1/2015” it had shown up as before, but it still sorted the same way; treating the data as strings. So with that in mind I tried a combination effort:

[Changed in the main form loading]

contactTable = New DataTable(“Contacts List”)
contactTable.Columns.Add(contactName)
contactTable.Columns.Add(meetingDate)
contactTable.Columns(“Meeting Date”).DataType = GetType(Date)
contactTable.ReadXml(“Contacts.xml”)
For Each row In contactTable.Rows
                row(“Next Action”) = CDate(row(“Next Action”))
Next
DataGridView1.DataSource = contactTable

All that did was the thing where it made it so that nothing appeared in the DataGridView. I’m at my wit’s end here – I just really want this to work but it seems that everything I’ve thrown at it and every other bit of info I’ve seen that’s worked for someone else has just failed here. If there’s any other information I can give that makes the problem more apparent or anything else I can do to describe the issue, please let me know, I just really want it solved. Thank you so much in advance for your time and help with this!

 

[JohnH]

Try deleting your xml file, and save a new one (DataTable.WriteXml) based on 'meeting date' DataColumn being Date data type and not String. It looks like the format of the input does not meet the Date data type serialization format.

 

[IanRyder]

Hi,

I agree with JohnH here since I am not able to recreate this with a valid date. Just to help a bit on your structure, when reading your XML directly into a DataTable then you have to specify your column structure first. i.e:-

Dim myDataColumns() As DataColumn = {New DataColumn("ContactName", GetType(String)),
                                     New DataColumn("MeetingDate", GetType(Date))}
DT.Columns.AddRange(myDataColumns)
DT.ReadXml("d:\temp\tempxml.txt")

Hope that helps.

Cheers,

Ian

 

[greggfromnebraska]

I had to create an XSD file in addition to the XML file, but that wound up clearing up the whole issue.

contactTable.WriteXmlSchema("Contacts.xsd")
contactTable.WriteXml("Contacts.xml")

Then to read it I just did the ReadXmlSchema and ReadXml and it worked.

Thanks!