quote:

---

I think ASP would ideal. For one, I am a little familiar with ASP. How to add,display,delete,update the records; more so than VB. The other thing is I am hoping to have this on the intranet within an existing layout (design will have to match, not sure if it would make a difference) Lastly, a lot of users.

---

Ok, I see it.

quote:

---

In terms of records being added, one person per subgroup will add an average of 15 records daily. There are rougly 36 subgroups. These subgroups are divided into 4 or 5 groups. The groups will run queries on the individual employee (like a subgroup), a subgroup, a group or all/some groups.

---

Sorry, this is something I didn't understand completely. This means you'll have 36 people inserting data, one for each subgroup?

quote:

---

Now, I like the idea of running an insert based on a subgroup. So if I say I want to add the data for subgroup 8. I could have one form that would have lets say 15 rows and each row could represent a record with the relevant fields going to the field in the db?

---

You can have different ways to implement this, but that would be the general idea.

quote:

---

quote:

---

Another option would be to create the same page with a form for each employee, where you post back to the same page, update the data and can continue. That seems messy too.

---

Thats what I was thinking. On a succesful insert, redirect to the same page. I was thinking perhaps pull every employee within a subgroup and after every success, redirect with a next employeeid in the querystring and use that for the where clause. But I dont know if this could be done and it sounds a little complicated and rough. Also, employee IDs will not be sequential in a given group.

---

The insert would be coordinated at a higher level by subgroup. Imagine that this subgroup coordination page runs a query returning existing subgroups always ordered in the same way (by ID for example) - (You could also return only subgroups without daily data for the day already inserted).
On running for the first time, this script gets a subgroup ID, checks to see if it has all employee records for that day (this should be a backcheck to avoid missing employees due to errors in the insertion by the following script...). If it hasn't, runs another script (let's call it subgroup exec page), passing the subgroup ID as a parameter). This page allows the insertion of data for each employee in the subgroup. After the user fills in all the data, the script writes it to the database and, in success, invokes the coordination script with the subgroup id that has just been completed.
Now the coordination script will get the subgroup imediately following the one received as parameter in its subgroup recordset that results from the query that returns all the subgroups always ordered in the same way. Get the picture?

Problems: mainly resulting from the possibility of errors during the subgroup exec. In that case, you'd need to give that info back to the coordinating script. The coordinating script would recall the subgroup exec script, with the same subgroup. Here you could have this exec script to be "smart" to find out which records for the subgroup/day had already been written, so that the user should not be forced to reinserte them again.

Obviously if you have a person per subgroup to insert daily data this makes less sense. On the other hand if several persons inserted data for more than one subgroup, you can register the subgroups for which each person inserts the data and, in the coordination script consider that info or the query returning subgroups.

This is the overall picture as I see it. There would be some other minor details to consider, but it looks feasible and reasonable to me. It is, however, not a simple solution, due to the stateless nature of Web server based apps.
You could also use session variables to clearly register the state of the daily info registration process. In case of error you could use that info to restart from the last successful operation...

Hope this helps. You have several ideas and several options to consider. But it's looking good ...

Edited by - ruirib on 20 February 2002 17:13:09

<form action info>
 <table>
  <tr> 'this is the first record to be added
   <td> the fields with the corresponding info</td>
  </tr>
  <tr> 'the next person
   <td> the same as above</td>
  </tr>
 </table>
</form>
 

quote:

---

You need an INSERT statement for each record, that is for each employee.

That won't be bad either because you'll also need to loop through the form controls, so it will be: read values from controls -> inserted them in the database.

---

SQL INSERT statements add one or more new rows to a table The INSERT statement has two
variations The first variation adds one row by assigning values to a specified list of columns toa specified table. The values you want to insert follow a VALUES statement.
You put parentheses around both the field list and the values list. 

For example: 

INSERT INTO tablename (field1 [, field2] ...)
VALUES (value1 [, value2] ...)  


You must provide a value for all fields that cannot accept a null value and do not have a
default value. You do not have to provide values for identity columns.

The second variation lets you add multiple rows using a SELECT query in place of the VALUES list, as follows: 

INSERT INTO TableName (field1 [, field2] ...) <SELECT Query>  


If you're inserting data into all the columns in the target table, you can omit the field
 list. The SELECT statement you use to obtain the data you want to insert can include any
 clause or condition discussed in the previous section, including calculated fields and a
 GROUP BY clause. 

  

SELECT dailyactivity.employeeID, dailyactivity.ot, tblemployees.subgrouprc
FROM tblemployees INNER JOIN dailyactivity ON tblemployees.employeeID = dailyactivity.employeeID
WHERE (((tblemployees.subgrouprc)="3"))

INSERT INTO target-table
SELECT ...

INSERT INTO target-table [(column1,[,column2[,...]]) ]
VALUES (value1[,value2[,...]]);

quote:

---

The form objects will loop with the next employeeid. How do I make an insert for every record? Do I break the SQL statement per loop?

---

Man, this is not going well . I only noticed this message after I answered the one following it. But here it goes...

I would probably have form elements name incrementally, with the same page for all employees at the same time.
This would mean I would have for example

Box1_emp1 Box2_emp1 Box3_emp1 ...

Box1_emp2 Box2_emp2 Box3_emp2 ...

...

Box1_empN Box2_empN Box3_emp3 ...

You know previously the number of employees for each subgroup so you can generate the HTML for the appropriate number of controls. You could also create a hidden control where you write the total number of employees / rows, so that you could use that number on the script that will loop through the form rows to get the data for insertion, once the form is submitted.

With this it's rather obvious how you'd handle the insertion of data.

Loop through the form rows, using the hidden control value as the loop control variable (easy because control names are related to row number).

In each iteration read the corresponding form row values and write it into the database using an INSERT TO statement to add that single row.

quote:

---

ruirib, my next is going to have the code I thought might work, tell me what you think and where the insert statement could/might work

---

This one is also answered I think.

Tell me what ya think...






Edited by - ruirib on 22 February 2002 17:29:59

quote:

---

I was coming to the same conclusion after reading some other web pages as well as trying it in access97.

MySql has something that I am not sure if it works in regular access/MS SQL.

It says that you can loop the insert statement. I came accross it a few times but not for anything but MySql.

I am running out of ideas. The web doesn't help much.
I think I may be able to do something like the following:

run a query find the number of employees in a given subgroup.
count them.
then give each one an id in a querystring.
then on each page have a form generated with the next id in the insert statement. after insert go to next page with newt id, until the last one. Where it will say, youre done.

Sounds sloppy but I have no other idea. I think I can put something like that together. I think doing it off of one form will be nearly impossible.

---

quote:

---

You could generate a page for the desired subgroup (example subgroup=3) that has a form for EACH employee in that subgroup. Then each form has all the fields to be inserted with the employeeid as a hidden field. When a form is submitted, you post back to the same page. To make this work, you place code at the beginning of the page that will execute the sql insert statement only if Request.Form("submit") <> "" (or something similar). After successful insert, the page is displayed showing all the employee forms again. You could use some javascript to set the focus to the next employee form.
Edited by - Nikkol on 22 February 2002 18:19:27

---

This is another viable option. There is one thing that I personally don't like (I admit that it's just a matter of personal taste), and that is submitting the form to the same page. I think it makes the ASP a bit more confusing. I'd rather submit to a page that performs the database updates and then can "calls back" the original one. Less conditions on the first script, clearer code, less error prone. Just a matter of personal taste though. I do admit that code clarity is a big thing for me ....

quote:

---

You could generate a page for the desired subgroup (example subgroup=3) that has a form for EACH employee in that subgroup. Then each form has all the fields to be inserted with the employeeid as a hidden field. When a form is submitted, you post back to the same page.

---

Well after my first answer something came to my mind: you can't submit several forms at once can you? If you fill several forms which one will you submit when it comes to submitting the page?
I've never tried it before, and I may be wrong, but I think only the submitted form will have its controls values posted.

If this happens to be true, you'll need a row of controls for each employee, instead of a form for each employee, snaayk. From the page processing script point of view it doesn't make much difference.

Nikkol if your option is doable, then I've learned something today ...