Solved

Populating a form using data from SQL 2000

Posted on 2004-10-02
6
182 Views
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.
0
Comment
Question by:Rudolph_C_Mancilla
  • 3
6 Comments
 
LVL 12

Accepted Solution

by:
rcmb earned 125 total points
Comment Utility
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.

RCMB
0
 
LVL 14

Assisted Solution

by:hhammash
hhammash earned 125 total points
Comment Utility
Hi,

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:
UserName=UserName
Password=Password

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.

Regards
hhammash
0
 
LVL 14

Expert Comment

by:hhammash
Comment Utility
Any luck Rudolph?
0
 
LVL 14

Expert Comment

by:hhammash
Comment Utility
No objection
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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…
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…
The purpose of this video is to demonstrate how to exclude a particular blog category from the main blog page. This is can be used when a category already has its own tab, or you simply want certain types of posts not to show up on the main blog. …
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…

762 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

6 Experts available now in Live!

Get 1:1 Help Now