List sorting into an asp tree

[bazpaul]

Hey everyone, Im currently workin on a little project for work which is driving me insane! I didnt want it to come to this (asking experts for help) but i feel the project needs some fresh eyes to guide me to a better solution. So let me just outline what im trying to do; First, we have a large list of customers on our server, approx 1316 customers. Now on our intranet webpage i wish to create a fast way of finding customers using an ASP tree. So consider a list of customer names returned from an SQL query into a large array. I then wish to check the first couple of letters of each customer, and group customers with similiar names together under one branch in the tree. The Result would look something like this; Customers | |-A ___|-ar ______-arson, pat ______-arson, mick ______-arsen, jon ______... ______... ___|-as ______-asnot, mark ______... ______... ___|-Other A's ______... ______... |-B ___|-ba ______|-bai __________-bailey, john __________-bailey, mark __________... __________... ______|-bar __________-barker, paul __________-barton, mike __________... __________... ___|-br ______-bromich, dave ______-brockurst, mick ______... ______... ___|-Other B's ______... ______... |-C ___-charles, john ___-chaplin, pat ______-chan, johnny |-D ___|-da . . . .... just to explain anything with a | beside it represents a branch which has leaves while anythin with just - represents a leaf which will hold a customer name One main important thing about this code is that i want it to be very dynamic and one important feature is groups of similar names, being grouped in their own branch. For example the code should read like; if there are less than X names beginning with 'ar' then group these names under a branch called 'ar' But if there are more than X names beginning with 'ar' then search again through this group and compare the first 3 letters for similarity. This idea is shown in the B section, here the code found more than X names with -ba as the first two letters, and so and the code ran again comparing the first three letters, these groups of three letters had less hits than X and so are plotted in their own trees -bai and -bar. I hope you can follow me now as you read this and it makes some sort of sense! I want it to be so dynamic that lets say, we enter many customers with names starting with -bai, the program will then scan the first four letters of the name and if this is over X, it will scan the first five letters of each name, if ag

[Kschuler]

You can do this in SQL by writing a statement like this: SELECT * FROM myTable WHERE customerName like 'BAI%' The % is a wildcard character in SQL. The one thing to watch out for is that SQL it is case sensitive, so to search for all possiblities you'd have to uppercase the customerName first. Hope this helps.

[bazpaul]

yes i know that SQL statement. But then the list sorting is not dynamic, what if lots of 'BAT' customers were added to the table, one would then have to go into the code and add the SQL statement for that! i need to use arrays that sift through the table plucking out groups of names that have similar first letters! i just dont know the best approach!

[Kschuler]

You can perform some SQL to the in memory DataTable. This can be done using the .Select function on the DataTable object. It will return an array of table rows that you can loop through to load your array. So it would look something like this: 'dtMaster has all of your master data For Each myRow as DataRow in dtMaster.Select("Customer like 'BAT%') 'Add myRow("Customer") to your string array Next As I stated before, this works only if the data in your DataTable is the same case, so you may have to create a second column of the customer names and upper case it so that you can use this. I hope this helps and is what you were looking for.