473,372 Members | 1,234 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

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 14518
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

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

Similar topics

6
by: david | last post by:
Hi, I have an application as follows: MySQL database Back-Eend linked to MS Access Front-End and ASP Web Application. I require users to enter Serial Numbers such as: 0105123567 (10...
2
by: r.magdeburg | last post by:
//please tell me why... //and give me a hint to solve the problem with leading zeros. //snippet #include <iostream.h> #include <conio.h> int main() { int zahl = 0; cout << "Give me an int...
5
by: samik_tanik | last post by:
I need to export a datagrid to Excel. I could did this. But, also need to keep the leading zeros in the data. How can I acheive this? Any help would be appreciated. -- Thanking you in...
1
by: mmmgood1 | last post by:
Help, I'm linking an excel spreadsheet in access and I have datafields with leading zeros (01021). When the file is linked in access, I get a #num in the field with the leading zeros. The zeros...
6
by: Clint Stowers | last post by:
Using A2k Exporting a query to a CSV file. The problem is any text fields (i.e. 000345) lose any leading zeros. Exporting to an excel file this problem does not exist. Tried to create a...
6
by: Rich Raffenetti | last post by:
How can one format an integer into a hex string with leading zeros? Suppose an integer is 512 which in Hex is 200. I wish to print the 4-byte integer as 0200 or even 0x0200. The HEX function...
3
by: travellinman | last post by:
Hi, I'm trying to combine the text in two original fields and paste them into another 3rd field on a form, but the problem is that there are leading zeros in both original fields, which access does...
6
by: JimmyKoolPantz | last post by:
Task: Customer wants a script of the data that was processed in a "CSV" file. Problem: Zip-Code leading zeros are dropped Basically we have a client that has requested a custom script for...
1
by: LFM | last post by:
I have a database setup that uses ODBC connections into SQL for read only views to compare information from multiple sql databases in our company. Most of the tables use the Employee ID as the...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.