VB.NET DataTable.Select() Leading Zeros problem

[enreil]

Hello everyone,

I've run into a problem that is giving me some trouble, and I hope somebody out there may have a little more insight than I. I am trying to use VB.NET's Datatable.Select() method to return a row from an in-memory table of about 36,000 rows. This would work quite well provided that my data did not contain leading zeros. Even that would probably be ok provided that the rows differed by more than the leading zeros, but that is not the case. For instance the primary key for one row is '00003101' while another primary key is '003101'. Note that these are two entirely different rows. The '00003101' row occurs before the '003101' row in the database, so when I run the following code:

Dim dr as Datarow = dt.Select("OrderID = 003101")(0)

it returns the '00003101' row when I wanted the '003101' row. This is not how I would have designed the database, but I must work with it. Do you have any suggestions on how to make the Datatable.Select() command work? Thank you in advance.

[bplacker]

One solution that comes to my mind is to add another column and automatically assign a value to it, making this new column the primary key. Make it auto_number, or incremented, so that it increments by one for each value.

[enreil]

In a normal situation, I believe you're right. The trouble with this table is that it contains an 8-level hierarchy. There is an id column (which is the primary key and is the column with the zero problem) and a "parent" column. This parent column contains pointers to the primary key of other rows in the same table. For instance, data on the lowest tier contains a parent value that is the primary key to the 7th level in the hierarchy, which in turn contains a parent value for the 6th level in the hierarchy and so on... I may be wrong, but I don't know that an auto_number or incremental primary key would do much good here.

I could break this main table down into 8 smaller tables - 1 for each level of the hierarchy - and then use an auto numbering primary key. However, this creates more overhead than is desirable for this project, so I'd like to try to find a solution utilizing the single table.

I could also try coding a sort of "search and replace" script that would find all instances of leading zeros and assign a different number. However, I really want to avoid altering the data in this table.

Is there a way to force the Datatable.Select() command to match the entire contents of a cell? I'd like it to be able to match "003101" but not "00003101". Outside of the select command I could do this with a regular expression, but I don't think that's allowed in this circumstance.

One solution that comes to my mind is to add another column and automatically assign a value to it, making this new column the primary key. Make it auto_number, or incremented, so that it increments by one for each value.

[kenobewan]

Does this article help?
How to: Filter and Sort Directly in Data Tables

[enreil]

Yes, it does! My problem was that I wasn't enclosing my criteria in single quotes. My select statement, instead of looking like this:

Datatable.Select("OrderID = 003101")

should look like this

Datatable.Select("OrderID = '003101'")

This is a subtle difference, but it seems like these little things are what cause us the most trouble sometimes! Thanks for the assistance.

Does this article help?
How to: Filter and Sort Directly in Data Tables