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
308 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
New Website 15 30
Laravel 5.1 + lucadegasperi/oauth2-server-laravel 3 463
HTML Editor to Choose? (2015) 13 82
Problem to Popup 37 116
When deciding to adopt any help desk solutions many factors should be explored before taking decisions. This will change from business to another but in general there are some kind of rule of thumb. Here are some quick tips: Do we need only ticket…
This article provides a case study on how our local youth baseball league deployed a new website, including the platform selection, implementation and benefits to the league.
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 Import and export files in WordPress. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Click on Too…

808 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