Is VB 2005 the place to do this

[SBTBILL]

I have a project to covert a a VFP program to VB. The project involves taking a small list of distributors about 30,000 many of whom are inactive and merging it with sales for the month.

Basically, I have to take one pass and get their personal sales. Then I have to take 5 or more passes and get the sales of their downlines. The downlines are not directly connected to the top of the tree only to their immediate upline.

I've tried using DataTables but that seems to be difficult as the search routines don't seem to work well.

I'm thinking of just keeping it in VFP or trying Access. The only reason to move to VB is Microsofts plans.

 

[Sorwen]

You would want your data in a database. You might then use vb.net to write a front end to manipulate the data in someone way, but it doesn't really remove the necessity of a database to store/query the data. I should say necessity as you can get around it, but would be like putting stone wheels on your sports car.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!

 

[Sorwen]

should = shouldn't

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!

 

[SBTBILL]

Kind of my thought, too as in the VFP version I use about a dozen temporary tables. Was looking at Access and since this part will never be on the web it seems to have the internals that would work. Course I have the VFP version pulling from the SQL tables now so I think I'll stick with that as it means all the calculations stay consistent with the last 10 years.

 

[Sorwen]

I'm not sure how you are all setup and doing things, but I mean you could query the SQL tables in VB.net. You left out that was how you were getting the data. If you have something like an SQL database then I found it is just easier to pass complex queries to the database and let it pass back the data I want to view/manipulate. Still if it is meeting your needs there isn't really an overwhelming reason to change. It just all depends on what you want to do.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!

 

[SBTBILL]

Only reason I was thinking of changing is that VFP is being discontinued by MS.

Actually I use 2 permanent (orders and Customers) tables and the rest are temporary. I do store the end result in 3 other tables. The change over is coming about because we are switching from an accounting program that uses DBFs (SBT) to a program that uses Pervasive 10 (Peachtree). Our orders are in an in-house-table that uses Microsoft SQL. All the temp tables are DBFs or cursors.

The final 3 have been in SQL for years and now I am pulling the original 2 from SQL as well. Course since this is a commission program I still have a few months of testing.

Anyhow, thanks for the info.

Bill Couture
sbtbill.com

 

[ProgrammerSteve]

I ran across this post and it sounds related to something I'm working on right now. I work for an MLM company and I have to rewrite a commission program that calculates down to 6 levels. Currently it's written in Access and I'm converting it in to Dot Net. sbtbill - you say that you use 5 or more passes to get the sales of the downline and a dozen temporary tables, I don't understand this technique can you explain how it works?

 

[ProgrammerSteve]

Ok, I've got my initial program up and running in VB.NET with an MSSQL backend. We've got about 70,000 people I have to calculate commission for down to 6 levels, I'm pulling the original data in through a stored procedure in SQL and mapping it all to DataTables in a master DataSet in memory and then doing the final calculations from there. It runs all 6 levels of commissions for all 70,000 people in about 2 minutes.

 

[SBTBILL]

That's faster then what I have. The way we do things is to pay standard commissions to 5 levels down. Then we sometimes pay a bonus override on the 5 active levels down. That is someone could get a commission on someone 20 levels down because they were on the 2nd active level to that person. Lots of recursion there.

Right now all our stuff is in VFP DBF's with about 36,000 people. We'll be moving to SQL over the next coupld months. Anyhow, other then I just do a direct quiry and store to temporary tables sounds like we are doing about the same thing.

I decided to leave it in VFP because we've been using esentially the same code for the last 8 to 10 years, so our distribuors are used to it. Why did you move from Access VB to streight VB?

 

[ProgrammerSteve]

You definately will see a huge performance boost when you move from DBF's to a database system like SQL.

When you say you do a direct query and then store to temporary tables it sounds like you're doing all the calculations within your program, that's probably why it's slow. The data I pull in is already 90% computed and summed by the SQL query, I only do minimal calculations in code.

Also you say you're doing lots of recursion, sounds like looping, when you move to SQL you can write your queries in a way that SQL will do most of the calculations for you (self joins, scalar summations, etc).

The reason I wrote in VB.NET instead of Access is VB.NET is a fully-fledged programming language, Access uses VBA which has several limitations.