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
323 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
WordPress Tutorial 4: Recommended Plugins

Now that you have WordPress installed, understand the interface, and know how to install new parts, let’s take a look at our recommended plugins.

 
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

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

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

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…
Dramatic changes are revolutionizing how we build and use technology. Every company is automating, digitizing, and modernizing operations. We need a better, more connected way to work together as teams so we can harness the insights from our system…
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…
The purpose of this video is to demonstrate how to add AdSense Ads to a WordPress Website, and how to set up WordPress to automatically place Ads in Sidebars. This will be demonstrated using a Windows 8 PC. Log into your AdSense account. : Cli…

617 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