Reading from one table then writing to another (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

DesiMcK -> Reading from one table then writing to another (6/4/2004 18:47:50)

Is it possible to read info form one table then write this info plus more to another?

The scenario: Select class > display PupilName, PupilSurname

Have a form that shows the pupil details along with two text boxes beside each pupil - txtHomework, txtGrade

Then I will add the titles and grades and submit the form to another table - tblHomework.

Desi



Spooky -> RE: Reading from one table then writing to another (6/4/2004 20:56:14)

The only thing you should require is the PupilID - no other detail from the original table should be saved to another.
Add the pupilID to a hidden form and submit it to your processing page with the new information



DesiMcK -> RE: Reading from one table then writing to another (6/5/2004 7:11:51)

I need to view the pupil details as I input their grades. How can the pupil names to viewed beside the etxt boxes for their grades.

Is my database setup up incorrectly.

There is one table - tblPupil: pupilID, pupilSurname, pupilName, pupilForm, Class

and the other - tblHomework: ID, pupilID, pupilSurname, pupilName, pupilForm, Class, homeowrkTitle, homeworkGrade

in tblHomework the same pupil will have lots of entries - that is why I formed the tblPupil so that you can select a class and all the pupils in that class will be shown. Then if possible the pupil details and the two additional fields should be written to tblHomework.

Desi



BeTheBall -> RE: Reading from one table then writing to another (6/5/2004 9:14:07)

Spooky beat me to it. Is your db set up incorrectly? Well, it's set up inefficiently and will waste resources. Dbs are relational, meaning the tables relate to one another. You use JOINS to bring the date from tables together. For example, you say:

quote:

There is one table - tblPupil: pupilID, pupilSurname, pupilName, pupilForm, Class

and the other - tblHomework: ID, pupilID, pupilSurname, pupilName, pupilForm, Class, homeowrkTitle, homeworkGrade


Similar to what Spooky implied, tblPupil is fine. tblHomework should contain only ID, pupilID, homeworkTitle, homeworkGrade.

You can still view all that data because each table contains the field pupilID. So you could display all fields from each table with this SQL:

SELECT * FROM tblPupil INNER JOIN tblHomework ON tblPupil.pupilID = tbleHomework.pupilID

The general rule, is you should never (OK, almost never) repeat data, other than including an ID field to enable the joining of tables.



DesiMcK -> RE: Reading from one table then writing to another (6/6/2004 18:57:08)

I think the problem is my db design. Can I ask for advice on how this db should be set up. I struggle with the relationships so I usually opt for a simplier (yet less efficient) flat file.

Basically, there are 6 teachers, with 40 classes and 30 pupils (approx) per class. Pupils only in one class, tecahers can share some classes. I want a way to add homework titles and grades and then show a report.

Thanks for your consideration.

Desi



BeTheBall -> RE: Reading from one table then writing to another (6/6/2004 19:56:30)

I am not a teacher, I just play one on TV. [;)] However, I am thinking you would have a pupil table, which you do. A class table, a teachers table, enrollment table, assignments table and then maybe a scores table. The assignments table would include an ID for each assignment and a title and maybe a description, the class ID, maybe a due date, etc. The scores table would include the pupil id, the assignment id and the score(grade). An enrollment table would include the class id and pupil id for every class so you could readily see what students were enrolled in which classes. The ID fields are then used to link tables together. For example, since the enrollment table contains the pupil ID, you could readily generate an individual student's class schedule. Each record in the class schedule would also include the teacher ID from the teacher table so as to identify which teacher instructs each class. However, you don't want to repeat all of the teachers information, just the ID.

Does that help any?



DesiMcK -> RE: Reading from one table then writing to another (6/7/2004 17:55:36)

Hi,

While on another forum I discovered someone else with similiar trouble. He was doing the attendance register in Access. His db seems to me to be much more effecient than mine. I liked his attendance form where he chooses a class and takes the register.

Could something similiar be produced using asp and if so could you please assist me in starting it.

His db can be downloaded from here

Thanks,

Desi



BeTheBall -> RE: Reading from one table then writing to another (6/7/2004 19:00:44)

Be glad to help. You need to realize that the db tables are the first step. Did you notice in the db you downloaded that the attendance table only includes the student ID and class ID, no other fields from the other tables? That is what I was trying to explain above. I much prefer web-based forms and reports to Access-based forms and reports. Personally, I find them much easier to create. The process is essentially this: Create your tables with the fields you need. Then create on-line forms to populate the tables. Then, you may also want more online forms for editing data and perhaps even delete data. Only you know what your needs are. As I have said, just avoid duplicating fields in multiple tables other than ID fields.

Suppose you have a table called tblPupils. You would then create a form with form fields to insert the records into the pupil table. Frontpage is more than capable of handling such a task. Same goes for every other table you choose to have unless you decide for reason or other that for a particular table it's just easier to enter the data directly to the db table. For example, if you only have 6-7 teachers and rarely have changes in staff, you may decide it's not worth the effort to create an on-line form to add/update teachers, but rather in the rare instance a change occurs just open the table directly and change it.



DesiMcK -> RE: Reading from one table then writing to another (6/8/2004 12:38:46)

Doing the tables and the suggested forms should not be a problem. Do I use Access for the queries or is this done with asp?

Desi



BeTheBall -> RE: Reading from one table then writing to another (6/8/2004 12:42:20)

I do most queries in ASP. Sometimes, a real hairy one is easier in Access, but that is the exception, not the rule. Will you be using FrontPage to create your web pages?



DesiMcK -> RE: Reading from one table then writing to another (6/8/2004 12:50:47)

Yep - I think I will use the tables from the database above and then use the DRW on diet.

One other question - how does it work with relationships? Do I need to form these in Access or is this done also with asp?

Desi



BeTheBall -> RE: Reading from one table then writing to another (6/8/2004 12:54:53)

Actually both your query question and your relationship question carry the same answer. It's up to you. For the most part, I create JOINS in my SQL via ASP. It is also very feasible to join tables via a query in Access and then use the query as the source for the DRW. Any queries you create in Access will show as available sources in step 2 of the DRW.



DesiMcK -> RE: Reading from one table then writing to another (6/8/2004 13:43:34)

Yes - I've seen that but am I right in thinking that queries are for viewing only. I access you can use a query to add data to tables but through the drw I have not been able to do this.

I think I will create three tables and with your help I will use asp / sql for the rest. I will let you know when I have completed the tables and related forms.

Thanks,

Desi



BeTheBall -> RE: Reading from one table then writing to another (6/8/2004 14:26:25)

You are correct. For updating, inserting and deleting, you will want to create your queries in ASP.



DesiMcK -> RE: Reading from one table then writing to another (6/8/2004 19:15:50)

Hi Duane. Ok - I have three tables: tblPupil - NameID, FirstName, LastName, ClassID, PupilForm ( all text fields)
tblClass - ClassID, Teacher (all text fields)
tblAttendance - ID, regDate, NameID, ClassID, Present (all text texts)

I cheated and used FP to generate database editors to add, edit and update tblPupil and tblClass.

So what now? Is it created the register lists? This would be a drop down box to select class, then the class list is displayed with puil names and a box to record if they are present. This info will need to be written to tblAttendance.

BTW - the field present will take three values - P for present, A for absent and L for late.

Looking forward to your reply,

Desi



BeTheBall -> RE: Reading from one table then writing to another (6/8/2004 21:25:19)

I would then do this. On a new page, insert a blank form. Then within the form, insert a DRW where you select from tblPupil the PupilID, Firstname and Lastname. In step 3 of the wizard, click the "More Options" button and then the "Criteria" button. Choose "Add" and then specify ClassID in both the Field Name and Value options. Then keep the default settings in step 4 and in step 5 mark the "Display all records" option and uncheck the "Add Search Form" option. You should now have a table of within a form. Let's save the page at this point as Attendance_Form.asp. You then want to create the form that will provide the DRW with the search criteria. Do this by creating another new page. Again, insert a blank form. Then, within the form, do "Insert", Database Results. Choose the same connection as the other page. Then in step 2, choose tblClass. Here is where I have a question on your table. You say tblClass has two fields, Teacher and ClassID. I would think since teachers probably have more than one class you would want to add another field that better describes the class, some sort of short title, such as FirstHour_Science. I would do this because although you only want to insert the ClassID to the db, the person entering the data may not know the ClassID so we need a more descriptive field to tie into the ClassID.

So, in step 3, click the "Edit List" button. Remove all fields except the ClassID and the field you create as a title for the class. In step 4 in the "Choose formatting options" menu, choose, "Drop-down List". For the value to display, choose the title field and in the value to submit, choose the ClassID field. Then finish the wizard. Now right-click your form and choose "Form Properties". Click the "Options" button and then in Action, enter Attendance_Form.asp.

If all goes well, the form with the classes should submit to Attendance_Form.asp, which will appear pre-populated with a list of the students in the class. Let's get that much done and then move to modifying the list of students to include the necessary form fields to send to the Attendance table.



DesiMcK -> RE: Reading from one table then writing to another (6/9/2004 16:31:33)

Thank you Duane for such a great detailed set of instructions. Everything you have requested is complete. On the issue of class Id I have used unique class codes that the tecahers are familiar with - 9B Ma1 - year 9 band B maths set 1 etc. So I did not need an extra field for class description as the code describes all we need.

Ready and waiting on your next instruction, Captain!! ( Was that a line from Star Trek!![:D])

Desi



BeTheBall -> RE: Reading from one table then writing to another (6/9/2004 18:11:16)

Great. Now you need to add a couple of form fields to Attendance_Form.asp. First, you need to add a field that will inherit the value of the Class Code passed from the previous page and I assume you need to capture the date for which you are marking the attendance. For the date, I would add a text box. Right-click it and choose "Form Field Properties". Assuming the regDate field date in your db is the date of the class session, name the form field the same thing, regDate. If the teachers normally take attendance on the actual day of the class you probably will want to insert this into the value portion of the form field properties:

<%=Date()%>

That way the field will prepopulate with today's date. I would put this form field at the very top of the form, before the table that holds the db results.

As far as the ClassCode, I would add a text field for that as well. However, once you verify that the text field is properly reading the value from the previous form, I would change it from Type="TEXT" to Type="HIDDEN". The value should be:

<%=Request.Form("ClassCode")%>

Once that is done, we will play with the DRW a bit to put the values into form fields. I apologize for not giving you the whole process at once, but my boss (the one at work and the one at home) want me to do some projects for them. [&o]



DesiMcK -> RE: Reading from one table then writing to another (6/9/2004 18:22:31)

Please don't apologise. This way is great. There is an old Chinese saying that I quote to my new teachers when they are feeling that the whole thing is too tough. It goes ' How do you eat and elephant? The answer - In small pieces!'

I'm all done and ready for the next piece of elephant when you have a chance.

Thanks for the amount of time you have given me so far.

Desi



BeTheBall -> RE: Reading from one table then writing to another (6/9/2004 20:34:41)

Now we need to add the rest of the form and a few small pieces of code that will help us later on when we actually perform the INSERT. Each record will contain the following:

ClassCode
recDate
PupilID
&
Present

In my mind, I am thinking that most students are present most days. So, why not use a radio button field with the "Present" value prechecked? Since you need to generate a radio button field for each student, you need to place the code between:

<!--#include file="_fpclass/fpdbrgn1.inc"-->

AND

<!--#include file="_fpclass/fpdbrgn2.inc"-->

You may or may not know, but everything between those two includes is part of a loop. So anything we place there will be repeated for each record returned. Since your results are in a table, I would create a new column in Normal View of FP. Then, switch to HTML view and within the new <td> tag you just created in Normal view, place this code:

Present (On-Time)<input type="radio" value="P" checked name="Present<%=i%>">Late <input type="radio" name="Present<%=i%>" value="P">Absent <input type="radio" name="Present<%=i%>" value="P">
<input type="HIDDEN" name="PupilID<%=i%>" Value="<%=FP_FieldVal(fp_rs,"PupilID")%>">

Now, you are probably wondering what the "i" is all about. Well, to perform our bulk insertion of records, we need to give each field name a distinct name. The "i" is going to help us do that. Just above:

<!--#include file="_fpclass/fpdblib.inc"-->

Put this:

<%i=0%>

Then, just after:

<!--#include file="_fpclass/fpdbrgn1.inc"-->

put:

<%i=i+1%>

That will cause i to increase by one each time you loop through the records. As a result, the field PupilID will actually become PupilID1 for the first record, PupilID2 for the second record, and so on.

Finally, before the </form> tag, place this:

<input type="text" name="RecordCount" Value="<%=i%>" size="2">

Now we are ready to test again. Go to the first form where you enter the ClassCode and submit it. When Attendance_Form.asp loads, we first hope for no error messages. If there are none, do "View", "Source" in Internet Explorer. You should see form fields such as, Present1, Present2, Pupil1, Pupil2 and the value for the field RecordCount should be the same as the number of records displayed.

Good luck.



DesiMcK -> RE: Reading from one table then writing to another (6/10/2004 2:37:29)

I get an error:

ADODB.Fields error '800a0cc1'

ADO could not find the object in the collection corresponding to the name or ordinal reference requested by the application.

E:\INETPUB\MRMCKEOWN\DEANES\REGISTER\../../_fpclass/fpdblib.inc, line 48

Any ideas?

Desi



BeTheBall -> RE: Reading from one table then writing to another (6/10/2004 8:48:41)

Ahh, and I was going along so well. [&o] That error means you are trying to write a value that doesn't exist. For example, if my SQL is SELECT PupilFirstName, PupilLastName FROM tblPupils and then later in my code I put:

<%=FP_FieldVal(fp_rs, PupilFName%>

I will get the error you got because my recordset has no value named PupilFName. So have a look at the values you are trying to write out and see if you can see any glaring mistakes. Otherwise, post the code for the page and let's have a look.



DesiMcK -> RE: Reading from one table then writing to another (6/10/2004 12:22:41)

My bad!! I have NameID not PupilId. Sorry for the trouble.

It's all wirking fine but I can't view source. When I select it nothing happens. So do we go on or is it important to view the source first?

Desi



BeTheBall -> RE: Reading from one table then writing to another (6/10/2004 16:31:24)

quote:

It's all wirking fine but I can't view source. When I select it nothing happens.


That's odd. Can you do a view source on any page? It's not crucial to what we're doing, but I would like to know if there are any errors.



DesiMcK -> RE: Reading from one table then writing to another (6/10/2004 16:55:16)

Found an article on the microsoft support site - deleted the temp internet files and view source now works.

The numbers iterate as expected so we're back on track!

Desi



BeTheBall -> RE: Reading from one table then writing to another (6/10/2004 17:24:33)

OK, time for the home stretch. Right-click the form and choose "Form Properties". Mark the "Send to Other" radio button and click "Options". Enter Attendance_Insert.asp. Now we just have to create Attendance_Insert.asp. The DRW cannot handle the bulk insert we are going to do. So create a new page in FP. Then between the <body> and </body> tags insert the following code:

<% DIM conntemp, mySQL, myDSN, recCount
recCount = Request.Form("RecordCount")
myDSN ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("\fpdb\NameofYourDB.mdb")
'myDSN would be specific to your environment

set conntemp=server.createobject("adodb.connection")
conntemp.open myDSN


FOR i = 1 to recCount

IF Request("Present" & i) <> "" THEN

mySQL = "INSERT INTO Attendance (NameID, Present, ClassCode, regDate) VALUES"
mySQL = mySQL & " ('" & Request("NameID"&i) & "', '" & Request("Present" & i) & "', '" & Request("ClassCode") & "', #" & Request("regDate") & "#)"

conntemp.execute(mySQL)

END IF

NEXT
conntemp.close
set conntemp=nothing
Response.write "Record added."
%>

You need to edit the above to use the name of your db and if your fpdb folder is not just below the root, you will have to adjust the path to the db. That should be it. The good thing is, you should be able to modify this process to do a similar table for test/assignment scores. Let us know how it turns out.



DesiMcK -> RE: Reading from one table then writing to another (6/10/2004 18:08:48)

That's brilliant Duane. i can't thank you enough. I will try to create and display the crosstab query so that the registers can be viewed in a sensible way. I will inform you of my progress.

Thnak you very much for your help in do this,

desi



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.078125