CODEWITHSUNDEEP
vb.netdatetimeselecttype-conversion
AER
AER

Reputation: 1531

How do I Select Rows in DataTable based on Name and Date in VB.net?

Trying to create a VB.net expression to select rows from a datatable that only have a certain vendor's name (Vendor column), and the event is after a certain date (PurchaseDate column).

My current approach is to use:

datatableVar.Select("Vendor = '" + vendorName.ToString + "' And PurchaseDate < Convert('" + eventDate.ToString + "', DateTime)")

Currently it is saying that DateTime is an invalid Type name, pretty sure this is the syntax for convert though and it takes DateTime as a thing to format to.

Upvotes: 0

Views: 1534

Answers (1)

jmcilhinney
jmcilhinney

Reputation: 54417

The best option depends on exactly what you want to do with the data afterwards but, assuming that you want to stick with that Select method, there's no need to call Convert because you can just use a DateTime literal:

datatableVar.Select($"Vendor = '{vendorName}' AND PurchaseDate < #{eventDate:M/dd/yyyy}#")

Note that I have also used string interpolation rather than concatenation, in order to aid readability. If you're using an earlier version than VB 2015, use String.Format instead:

datatableVar.Select(String.Format("Vendor = '{0}' AND PurchaseDate < #{1:M/dd/yyyy}#",
                                  vendorName,
                                  eventDate)

The reason that your original code didn't work is that you didn't do what the documentation tells you to do when calling Convert. The example in the documentation is this:

myDataColumn.Expression = "Convert(total, 'System.Int32')"

So you can see there that the type is qualified with the namespace and it is wrapped in single quotes. That means that:

"', DateTime)")

should have been:

"', 'System.DateTime')")

ALWAYS read the relevant documentation first.

Upvotes: 2

Related Questions