Solved

Make a page that draws data from text entered and tables in SQL and Adds records to another table

Posted on 2004-08-27
7
302 Views
Last Modified: 2013-12-24
I have an SQL db named 15ff9099pc that contains a db named CSSD.

The tables that will be used are:

Table:  Classes

Fields: ClassID PK (increment identity seed, 1) int  -   ClassName nvarchar     (100) - 1     DepartmentID int (4) -  
ClassSection nvarchar (50) - 1     InstructorID int (4) - Term nvarchar (30) - Units int (4) - ClassYear     nvarchar (4)
Location varchar (50) - DaysAndTimes datetime (8) - Notes text (16) -  StartDate datetime (8)
FinishDate     datetime (8) - Attendees smallint (2)

Table:  Students
Fields: EmployeeNumber PK nvarchar (12) - FirstName     varchar (50) - LastName varchar     (50) ThirdName varchar (50)FourthName - varchar     (50) - Address varchar (50) - City nvarchar (50) StateOrProvince     nvarchar (20) PostalCode nvarchar (20) - PhoneNumber     nvarchar (10) Major nvarchar (50) - Email     varchar (70) - Address1 varchar     (50) Address2 varchar (50) - Town varchar (50) - State     varchar (2) - Zip varchar (10) - PhoneExtension varchar     (5)

Table: Assignments
Fields:  AssignmentID     PK int (4) (increment identity seed, 1)  - AssignmentDescription nvarchar (255) - ClassID     int (4)
Exam bit (1) - PercentOfGrade float (8) - MaximumPoints real (4)


Table: Instructors

Fields: PK InstructorID int (4) Identity seed auto increment - Instructor nvarchar (50) - PhoneNumber nvarchar (10) - Extension nvarchar (5)

Table: Departments
Fields: PK      DepartmentID int (4) Identity seed auto increment -  DepartmentName nvarchar (50) - DepartmentNumber int (4) - DepartmentManager     nvarchar (30) - DepartmentChairperson nvarchar     (50)

Table: Locations
Fields:  PK LocationID smallint (2) Identity seed auto increment - Location varchar (50)

Table: StudentsAndClasses
Fields: PK StudentClassID int (4) Identity seed auto increment - ClassID int (4) - EmployeeNumber varchar (12)

Page(s) Description:
I want to create an asp application that creates records in the classes table and update records in the students table.  The user would use a form to type in 'ClassName' text box which adds to classes table, dropdown list to choose an 'Instructor' (from the instructors Table), dropdown list to choose a  'DepartmentName' (from the Departments Table), Enter 'DaysAndTimes' in a text field (adds to classes table - if possible with some kind of input mask or pop up calendar control to make sure they use the right format), dropdown list to choose a 'Location' (from the locations table), 'Units' text box adds to classes table, 'Term' text box adds to classes table, Year text box adds 'ClassYear' to classes table, 'Attendees' text box adds to classes table, 'Notes' Memo box adds to classes table.

So far we are just adding a record to the classes table once this is set (and or added) a drop down box linked to the students table should allow the user to select a series of student (one at a time or several at once whichever makes most sense) who have taken the class by student FirstName, LastName, EmployeeNumber (one line in the dropdown)

The ClassID data from the Record created for the Classes and the EmployeeNumber from the Students table will create a record for each student selected in the ClassesAndStudents table.
 
0
Comment
Question by:dtolo
  • 5
  • 2
7 Comments
 
LVL 12

Expert Comment

by:rcmb
ID: 11916576
David,

Just so I understand

1. You desire a form to enter a class attended by the student and this would enter the data into the classes table.

2. Once this is done you desire to update the Student table with this same class?

3. Then you would like to select a class name and see all students that have attended the class?

Give me a quick rundown of exactly the flow you desire.

RCMB
0
 
LVL 2

Author Comment

by:dtolo
ID: 11930064
RCMB,
Close, but not exactly.  The three major players are tables called: classes, students, and students and classes.  The user is building a record for classes (which creates a value in a field called ClassID) and then the user selects students from a students table (which retrieves a value from the field called EmployeeNumber) those values are then passed to a table called Students and Classes.

This table holds the results of which students took which class.


The form would add a record to the Classes Table (fields: ClassName, -Instructor {drop down box drawing info from instructor table} -, -Department {drop down box drawing info from Department table} - -Location {drop down box drawing info from Location table} - etc.)

Basically the initial flow involves:

1) grabing some info from a few different tables
2) grabbing some info from user input
3) storing it in some variables
4) adding a record to the classes table
5) sellecting records from students table
6) Adding records to the StudentsAndClasses table containing ClassId from the Classes Table and EmployeeNumber from the Students Table.
0
 
LVL 2

Author Comment

by:dtolo
ID: 11930101
Just a general Idea of how to go about this in FrontPage would be fine.  Most people think that some thing this complicated can't be done in Frontpage, but I think it can.  Maybe an overview of the first step, and then I can award points and create a question for step two. etc. and down the line.  I can break this question up into as many parts as necessary.

~David
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 12

Accepted Solution

by:
rcmb earned 500 total points
ID: 11973765
David,

Once you have the first page up and running this code will help you to get the data to the database. The code will count the number of students you select in the form and then write your data to the first table. The second part will loop through the student ids and write them to the second table.

Enjoy, Curtis

<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252"> <title>DB Test</title> </head>

<body>
<%
varAttendee = request("EmployeeNumber")
varAttendeeCount = 0
varArray = Split(varAttendee, ", ")
For i = 0 to Ubound(varArray)
      varAttendeeCount = varAttendeeCount + 1
Next

ClassNameID = Request("ClassNameID")
DT = Request("DT")
InstructorID = Request("InstructorID")
DepartmentID = Request("DepartmentID")
LocationID = Request("LocationID")
Notes = Request("notes")


set Insert = Server.CreateObject("ADODB.Command")
Insert.ActiveConnection = "DRIVER={SQL Server};SERVER=15ff9099pc;DATABASE=TA;UID=sa;PWD=olot9864"

Insert.CommandText = "INSERT INTO Classes (ClassNameID, DaysAndTimes, InstructorID, DepartmentID, LocationID, Notes, Attendees) VALUES ('" & ClassNameID & "', '" & DT & "', " & InstructorID & ", " & DepartmentID & ", " & LocationID & ", '" & Notes & "', "&varAttendeeCount&")" Insert.CommandType = 1 Insert.CommandTimeout = 0 Insert.Prepared = true
Insert.Execute()
%>

<%
varArray = Split(varAttendee, ", ")
For i = 0 to Ubound(varArray)
      varUser = varArray(i)

set Insert = Server.CreateObject("ADODB.Command")
Insert.ActiveConnection = "DRIVER={SQL Server};SERVER=15ff9099pc;DATABASE=TA;UID=sa;PWD=olot9864"
Insert.CommandText = "INSERT INTO StudentsAndClasses (ClassID,
EmployeeNumber) VALUES (" & ClassNameID & ", " & varUser & ")" Insert.CommandType = 1 Insert.CommandTimeout = 0 Insert.Prepared = true
Insert.Execute()
Next
%>
0
 
LVL 2

Author Comment

by:dtolo
ID: 11976140
RCBM,

Thanks!

Once Again you have come through in a pinch!

~David
0
 
LVL 2

Author Comment

by:dtolo
ID: 11976156
Is there a way to display the entries just made to the user?
0
 
LVL 2

Author Comment

by:dtolo
ID: 11976170
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

In this short web based tutorial, I wanted to show users how they can still use the powers of FrontPage in conjunction with Expression Web 3.  Even though Microsoft eliminated the use of Web components, we can still use them with FrontPage and edit …
When setting up new project requests for our site, one of the most powerful tools our team has available to use is Axure (http://www.axure.com/). It’s a tool for creating software and web prototypes that can function and interact as if it were the a…
The purpose of this video is to demonstrate how to Test the speed of a WordPress Website. Site Speed is an important metric of a site’s health. Slow site speed can result in viewers leaving your site quickly and not seeing your content. This…
The purpose of this video is to demonstrate how to set up basic WordPress SEO. This will be demonstrated using a Windows 8 PC. The plugin used will be WordPress SEO by Yoast. Go to your WordPress login page. This will look like the following: myw…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now