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
314 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In this tutorial, I'll explain how to create an animated progress meter in a wireframe prototype developed using Axure RP 7.0 - a leading prototyping tool for designing web sites and software. (For more information about Axure and gett…
Objective of This Article In 1990’s, when I was a budding software professional, I had a lot of confusion about which stream or technology, I had to choose to build my career. In those days, I had lot of confusion like whether to choose System so…
The purpose of this video is to demonstrate how to make a WordPress Site faster and smaller in size by cleaning up the database. This will be demonstrated using a Windows 8 PC. Plugin WP Optimize will be used. Go to your WordPress login page. T…
The purpose of this video is to demonstrate how to reset a WordPress password if you are locked out and cannot reset the password. A typical use would be if you cannot access the email to which WordPress would send the password recovery email to…

730 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