ASP Help with Inserting Data into Table

[Cheryl3D]

Hi:

I'm working on a web-based time and attendance application in Dreamweaver MX with an MS SQL Server 2000 database on the back-end.

I have a need to add new records to a tabled in the database in a batch, if possible.

I have a table called 'Weeks', that contains the following fields:

id
last_name
first_name
workdate
timein
timeout
bhrs (for billable hours)

These fields in the 'Weeks' table correrspond to an Active Server page (ASP) that contains a timesheet format for example such as:

Name John Smith
Week Day Workdate TimeIn TimeOut Billable Hours

Monday 5/1/2004 0900 0500 8

Tuesday 5/2/2004 0900 0500 8

Wednesday 5/3/2004 0900 0400 7

Thursday 5/4/2004 0900 0500 8

Friday 5/5/2004 0900 0500 8

In the 'Weeks' table, for the employee called John Smith, the data entries in the table would look like:

id last_name first_name workdate timein timeout bhrs
1 Smith John 5/1/2004 0900 0500 8
2 Smith John 5/2/2004 0900 0500 8
3 Smith John 5/3/2004 0900 0400 7
4 Smith John 5/4/2004 0900 0500 8
5 Smith John 5/5/2004 0900 0500 8

My question is: how do I take an ASP page with each of the text boxes for the day of the week and make it so that when the user clicks the submit button for the time sheet, using ado and the .addnew and .update methods the data entered by the user will loop through the days of the week on the asp form and insert 5 separate records into the 'Weeks' table instead of just one row for John Smith with 5 different workdates?

Do I need to structure the 'Weeks' table differently?

Like for instance:

id
last_name
first_name
workdate1
workdate2
workdate3
workdate4
workdate5
workdate6
workdate7
timein1
timein2
timein3
...
timeout1
timeout2
timeout3
...
bhrs1
bhrs2
....

(I did 7 work dates for the seven days of the week.) Is this a better structure for what I want in the 'Weeks' table?

I believe the structure that I have now is more simple and better. I just need help with how to use either T-SQL or ADO to loop through the 7 days of the week shown on the ASP page and for each new workdate add a new row in the 'Weeks' table with the related timein, timeout, and bhrs information. (But keep in mind that the first_name and last_name needs to be added to each new row as well, even if the names are repeated).

Can anyone help me with this?

Any help is greatly appreciated.

Thanks,
Cheryl

 

[MoLaker]

I would suggest doing it with an INSERT INTO sql rather than .addnew. SQL Server likes that much better. You could run 7 INSERT INTO sql's one after the other with the data for each day (including the name).

"INSERT INTO Weeks (last_name,first_name,workdate,timein,timeout,bhrs) values(" & [name of asp field for last name here] & "," & [name of asp field for first name here] & "'".... you get the idea. Assuming the ASP is a form filled in by the user, you would have to handle cases where data was missing or not correct (such as an invalid date).

A separate record for each date is probably best. At some point in time you may want to do some totaling of hours for more than one week. 7 days per record would make this clumsey. Probably the main thing I would do differently is to create an "employee" table and give each employee a unique ID code. Then store that code instead of the name in each record. I would probably store timein and timeout as date/time fields and possibly do away with the 'workdate' field altogether. But, this might depend upon how the data is to be used.

 

[DreXor]

easiest way would be skipping the recordsetobject :

( after submit )
Set Connection = Createobject("ADODB.Connection")
Connection.Open ConnectionStuff

for i=1 to 7
SQL = "Insert into Weeks(Last_name,First_name,WorkDate,Timein,Timeout,bhours) values('" & request("last_name" & i) & "','" Request("First_Name"& i) & "'," & etc etc etc & ")"
RsAdd = Connection.execute (SQL)
Next
Set RS = nothing