asked on

Need advice on how to handle data and windows forms.

I have an app that I developed for a client that keeps track of production info. In a year they have about 10,000 entires into 1 of their tables. Every year the DB gets backed up and a new year is created empty. There are also many other related tables. The exe file of the program sits in a folder on their server,where I allow sharing of the file. Saves them time from having to go to each computer that someone uses it and re-install every time I make an update. The most connections that I have to the database at 1 time is maybe 3, and it's not being accessed all that often. I used a lot of form wizards and form binding to control this data. So that's the background.

So my questions are:

1) What is the BEST and simplest way to connect, and display data, and keep things fast. Keep in mind I have approximately 75 fields on a form using tabs. Also using grids.
2) I do have to load all the records for previous scrolling, is there a better way, again speed issue.
3) I find that when I empty my db I get errors because of null. I'd like to try and avoid this.
4) Any other best practices? I'm an intermediate programmer.

Sancler

Fairly random thoughts.

If you clear your database at the start of each year then, in the early part of the year, there will be no (or few) records available to meet the requirement that you state "I do have to load all the records for previous scrolling". This makes me wonder whether ALL records are really necessary, especially when - towards the end of the year - the number is getting up to the 10,000 mark. This leads to the thought - bearing in mind that it is the record transfer which is likely to slow things down - that it might be worth revisiting the "load all records" requirement. No user is going to be able to make sense of 10,000 records at one time. So how about loading the latest few hundred, but with a facility to bring over more if the user wants to scroll back beyond those? Overall, it might take longer. But the appearance for the user (which is what is usually important) may well be faster: a minimal wait to start with, and a minimal wait when any "extra" records are needed. And the number of "extra" records could, in any event, probably be reduced by filtering options for the user and the judicious use of WHERE clauses in SELECT statements.

And how about a rolling back-up of the data, rather than an all-or-nothing, once a year bash? Figures would depend on operational considerations but if, once every six months, you moved the earliest six months records from the operating to the back-up, archive database, leaving the latest six months' records where they were that would avoid the nulls problem. And if you could reduce it to an operational file of, say, only six months length with the first three months being moved out on a rolling basis then, even if you did have to load ALL records there would, overall, be less to handle.

The basic programming (as opposed to design) point, I suppose, is that the less data that you need to transfer as a matter of course (that is, when it might not actually be needed) the better.

Apart from that, it is difficult to offer suggestions without knowing more detail of the setup (e.g. what is the database?) and the actual coding. It sounds like a lot of controls: but if the data that you have to show requires that number ...

Roger

ASKER CERTIFIED SOLUTION

Jeff Certain

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tigger996

ASKER

Ok some further explaination:

At the start of my app, they select the year they want to work with, and that selects the db (MS SQL 2000).

I constantly update/save the record (single record entry) (when they click update). I know this probably isn't the best way, but I do need the db updated as they are entered .

I designed this in VS2003.

I do agree with you on not loading all records. Once the records are entered and verified (by an admin) They really don't need to look it up again. And if they need to they can view a report.

I'm wondering if I shouldn't re-design this in 2005. Sounds like there are some better data features available.

I think I always get stuck with doing bound fields, or is it better to do it through code?

I don't mind creating an empty database for them every year. Keeps things really clean for them and the only thing they do with the previous year's information is view reports.

So after all of that... How is the best way to handle data - in the sense of bound fields or manual coding?

Thanks for all your tips and suggestions.

SOLUTION