Populating a form using data from SQL 2000

Posted on 2004-10-02
Last Modified: 2013-12-24
My company maintains extensive info on our employees in SQL 2000. They will be asked to fill out an online form (which will send its result to Access) with various types of info, but when it comes to their personal info, I'd like to have them pick their name from a list or do a search for it, and then have the data from SQL populate things like their address and department, based on whatever is stored in SQL. This way they can do it faster and we'll know the info matches our records exactly. What's the best way to go about this? Thanks.
Question by:Rudolph_C_Mancilla
  • 3
LVL 12

Accepted Solution

rcmb earned 125 total points
ID: 12211218
First, do you have a database connection created in FrontPage that points to your SQL 2000 database?

If not, you will need to create this by using your ODBC connector and then have FrontPage point to the ODBC connection.

Open a blank page and click on insert Form, Form

Then click on insert, Database, Results

A wizard will begin and in step 1 select your connection to the SQL 2000 database.
In step 2 select the table containing the employee information
In step 3 edit the list to list only the employee's last name and "unique record identifier" (we can modify this later) and then click on More options... and select ordering and order by last name
In step 4 select Drop Down list and in the top option select last name and in the bottom option select your "unique record identifier"
In step 5 uncheck the search form box (if checked) and make sure display all records is selected.

right click inside the form and select Form Properties and select send to other
Then click Options and enter employee_form.asp. Click OK to return to your document.

Save the page as somename.asp

This builds your dropdown list that the employees will select. (I personaly recommend you do not build a drop-down as this allows all employees to select anyone's information and modify it. What I would do is have a generic form that requires the user to input their SSN (or employee number) and have that form submit to another form that displays their information.)

On the second page we will repeat some of the options as above.

Open new page and Insert, Database, results
Step 1 select your database
Step 2 select your table
Step 3 select the fields you desire to make available (this will be the fields you desire to pre-populate)
also select More options... criteria and select the "unique record identifier"
Step 4 select List one field per item and then select Text fields
Step 5 uncheck all check boxes and select display all records together (should be the only thing checked)

Finish - This will build a form for you and have all the fields you desire to pre-populate. Now modify the form as desired and add any additional form fields needed and dress the form up to make it look like you desire.

Right click in form and select Form Properties and Send to other and enter somefilename.asp as this will be the form you use to write the data to your access database.

Save the page as employee_form.asp

Now we have built the drop-down form and the form that displays the pre-populated information (with additional fields you may have created).

Now open a new form and select Insert, Database region

Step 1 select the access database
Step 2 select edit query and write a slq query like so:

INSERT INTO YourTable (LastName, FirstName, Address, City, State) VALUES ('::LastName::', '::FirstName::', '::Address::', '::City::', '::State::')

The first part of the query (after yourtable) will be equal to the fields actually in the access database and the second part (after values) will be equal to you actual form field names in the file employee_form.asp.

Finish the wizard and save the file with the same name you submitted the form to in the file employee_form.asp.

This will write the data to the database.

LVL 14

Assisted Solution

hhammash earned 125 total points
ID: 12212430

Another suggestion in case you don't want to use the Insert Query.

The Employee Form
1- Create a new page
2- Insert > Form > Text Box
3- Name the field as EmployeeID
4- Save the page as SelectId.asp
5- Right Click the form and select Form Properties
6- Click send to other
7- Click Options
8- In Action field type OnlineForm.asp
9- Save the page as GetId.asp

The Additional Fields form + Staff Information
01- Create a new page
02- Insert > Form
03- Inside the form click Insdert > Database Results
04- Select Database connection (database name) - Next
05- Select the Record source (table name) - Next
06- In step 3/5 click more options
07- Click Criteria
08- Click Add
09- Click EmployeeID=EmployeeID
10- Uncheck "limit number of returned records to 256"
11- OK
12- Click Edit List
13- Select the fields that you want to display to the user while filling the form
14- OK then Next
15- Select List- one field per item from the drop down list
16- Another drop down will appear,  click it and select Text Fields
17- Next
18- Display All Records
19- Uncheck the Add Search Form
20- Finish

Now you will see inside the form the Database Results Region with yellow areas,  remove the Submit Query and Reset buttons.  Now under the second yellow area add the additional fields that you want.

Under the new fields right click the form and select Form Properties.  Click on Send to Databases. Click on Options then select the database and the concerned table.

Save the new page as OnlineForm.asp.

Note:  You have to create a table for the new form.  This table will store the information of the staff who filled the form and the additional fields.

What happens is.  In the first form,  the employee types his/her EmployeeID number and presses submit.  A new page will open with fields populated with related info.  Under these info you will see additional fields to fill.  When the user presses submit,  the form will write to the new table inserting the information retrieved from the Employees table in addition to the new data entered in the form.

To be more secure,  I prefer if you create a page with user name and password.  This page will take you to the same page "OnlineForm.asp",  but you can make sure that the employees who has the username and password accessed the page.  If an employee knows the EmployeeID of others he/she can fill the form for them.

If you are interested  here is the way to do it.

The first page
1- Insert > Form > Text Field
2- Insert > Form > Text Field
3- Name the first field and UserName
4- Name the second field as Password,  give the property of the password.
5- Right click the form
6- Post the form to OnlineForm.asp

Now OnlineForm.asp page will not change except in the criteria you have to select:

But you should have a username and password for each employee in the Employees table.  This way if an employee knows the ID of other won't be able to fill the form.  He should know the password and username to do that.

Anyway this is a simple level of security but will do the job.

That is all.

LVL 14

Expert Comment

ID: 12394251
Any luck Rudolph?
LVL 14

Expert Comment

ID: 12671482
No objection

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Coldfusion Code128 barcode problem 11 244
importing  maven Project 5 312
Serif Plus workable? 4 48
monthly fee and support for nutrionist 3 143
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 …
Turn A Profile Picture Into A Cartoon Using Photoshop And Illustrator This tutorial will teach you how to make a cartoon style image out of a regular picture. I have tried to keep the tutorial as simple as possible. I used Adobe CS4 for this tuto…
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 update a WordPress Site’s version. WordPress releases new versions of its software frequently and it is important to update frequently in order to keep your site secure, and to get new WordPress…

839 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