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
305 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

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!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to convert ASPX website to HTML? 3 55
Looks good in lg screen, md screen but disappears on mobile 4 74
bootstrap submit button 12 116
How do I bind my model the view. 7 59
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 …
This guide will walk you through the essential considerations and tech stack for building scalable websites. Know how to grow your business the smart way!
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 set up the permalinks on a WordPress Website. 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 : Go t…

932 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

12 Experts available now in Live!

Get 1:1 Help Now