Best Practices Question

I'd like recommendations as to the best way to accomplish my task...

I've got a website that contains an online application. The application will be 5-6 pages long. Each page will contain 20-30 textboxes and/or dropdownlists. All this information will write to a database and also be written to a .TXT file which will be emailed to me.My question is:

Is it better to carry the information submitted on page one to page two, then carry that information from pages one and two to page three (etc) until I get to the final page and then have it all write to the database in one session? I have read an article on how to carry information from one page over to another, but I don't know if that process would be inadvisable, considering the amount of information I'm gathering....

OR

Should I write each page's information to the database as I go along, appending the new information to the table row from each page as it's submitted? This sounds like the better option to me, but I don't know how to tell Page 2 programatically to find the same row that Page 1 wrote to, then tell Page 3 to find that same row that Page 2 and 1 wrote to...etc...

All help, code samplesand recommendations will be greatly appreciated! (I'm programming in VB using Visual Studio .NET)

~Steph

 

Thanks for replying, Bruce!


I really would prefer to post the data to the database as I go along. What's a good way to make the data append to the same row?


I'm thinking I could have the user create their own ID, which would become the primary key in the database. Then, I could have the code capture the user ID on each page so the code knows what row to write the data to.


What I'd prefer is to create a primary key column that the user doesn't have to specify, that populates on its own. But, how I could tell the code to reference that unique ID without knowing what it was ahead of time or having the user pick it themselves, I don't know. Is there a way to do that?


~Steph

 

If you have the user create their own key, then you are faced with the problem of first checking the database to see if the key is unique, then notifying the user if it is not, prompting for entry of another key and so forth until you achieve a unique key.

My philosophy has been for many years that each table should have its own unique key obtained by using the auto numbering features the database provides:
* if you are using Access use an auto nunber field
* if using MS SQL server, make the key field type int and set the identiity property to true
* if using MySql there is probably something similar, but I don't know what it is

In either case, after the first group of data is inserted into the table, you can use 'SELECT MAX IdColumnName FROM TableName' to retrieve the id value of the just added row.

If your database is MS Sql Server you can use @@Identity or one of the other identity functions to return the id value of the most recently added record.

 

Thanks Scott!


I like the idea of the auto-incrementing ID field...I am using MySQLand I know how to make the column assign a unique, auto-incrementing ID number. Using the "Select MAX" feature makes a lot of sense too...and I'm sure MySQL has a corresponding command.


My new question, based on the response, is where I go from here.... (I am, alas, a real newbie...)


*The user has filled out page one of the application and clicks submit.
*Everything they've filled out thus far writes to the database, which also assigns a unique ID
*The user fills out the information for page two and clicks submit.


I obviously have the code call the table row with the most recent (Max) ID created and append the data, right? So, would the code be something like...


mysqlstring = "UPDATE tablenameINSERT (column names) VALUES (values from page 2 of form)WHERE cust_id = MAX"


Or something to that effect?


Thanks for all the help!





~Steph

 

One other option might be to use a single page with panels rather than multiple pages to collect data.

For example:

<aspanel id='Page1' runat='server' visible='false'>
all the html, text boxes etc for page one goes here
</aspanel>

<aspanel id='Page2' runat='server' visible='false'>
all the html, text boxes etc for page two goes here
</aspanel>

..... additional panels as needed, one for each page of data

Then in code instead of using response.redirect or server.transfer to move between pages,
you do a: Page1.visible = false
Page2.visible = true

The data from each page is maintained in the viewstate between postbacks, so when the data entry process is completed you can address any of the controls on any of the pages (panels) in code and have access to what the user entered.

One drawback could be an increased page load time, but that may not be a problem if the length of data entered in each text box is relatively short.

One additional comment...

You are the best judge of your own time constraints, but I think that with the number of data items you are dealing with, if you expect any kind of volume, storing the info in text files will lead to real headaches -- probably sooner rather than later,

If you possibly can, my advice would be to bite the bullet now and take the time you need to learn how to use stored procedures in MySql in conjunction with data access code to get data into and out of your database and to edit data once it is in the database.

Since I do not use MySql, I don't know how to be of any further help, but I'm sure there are many experienced MySql users here who can.

 

***Late Breaking Development***


I intend to pursue the database option, but (being pressed for time at this point), will have to temporarily use the first option -- carrying the data along from page to page.


I've read a lot about Sessions, but still have not been able to apply them as I need them. How would I best make all the information from Page 1 be "remembered" all the way to Page 10? How can I make Page 2, Page 3, Page 4 (etc) be remembered along the way?


I assume that this is where I might be able to use the Session(tbFirstName.Text) and Context.Items stuff, but is it feasible with 20+ textboxes per page and 10 pages? If I'm not posting the "results" anywhere on any of the pages, is it enough tocite the Context.Items in the Page_Load event?


Until I can get the database fully functional, I'm intending to put the results of all 10 pages into a single .TXT file, which I would do in the click event of page 10.


I'm feeling a little overwhelmed by all of this, so I'd love any insight anyone can give. Thanks for your patience and willingness to help!

 

Thanks so much for the suggestion, Scott.


I think do my project exactly this way. It solves my 'appending to a record in a database' problem, too.
You made my day!

~Steph

PS - I'll take your advice on the database to heart, too. )