469,275 Members | 1,871 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,275 developers. It's quick & easy.

VB.NET DataTable.Select() Leading Zeros problem

86
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.
Jan 22 '07 #1
4 14228
bplacker
121 100+
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.
Jan 22 '07 #2
enreil
86
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.
Jan 22 '07 #3
kenobewan
4,871 Expert 4TB
Does this article help?
How to: Filter and Sort Directly in Data Tables
Jan 23 '07 #4
enreil
86
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
Jan 23 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

6 posts views Thread by david | last post: by
2 posts views Thread by r.magdeburg | last post: by
5 posts views Thread by samik_tanik | last post: by
1 post views Thread by mmmgood1 | last post: by
6 posts views Thread by Clint Stowers | last post: by
6 posts views Thread by Rich Raffenetti | last post: by
6 posts views Thread by JimmyKoolPantz | last post: by
1 post views Thread by LFM | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.