Solved

Populating a form using data from SQL 2000

Posted on 2004-10-02
6
186 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
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.

RCMB
0
 
LVL 14

Assisted Solution

by:hhammash
hhammash earned 125 total points
ID: 12212430
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
ID: 12394251
Any luck Rudolph?
0
 
LVL 14

Expert Comment

by:hhammash
ID: 12671482
No objection
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ColdFusion and quickBooks 4 76
easy wysiwyg web app db  for office football pool 9 59
Timer 6 89
Web Data Entry Application 5 91
Now that Expression Web 4.0 (http://www.microsoft.com/expression/products/Upgrade.aspx) is free if you buy or have the full version of Expression Web 3.0, now is the best time to  migrate from FrontPage to Expression Web (http://www.frontpage-to-exp…
Objective of This Article In 1990’s, when I was a budding software professional, I had a lot of confusion about which stream or technology, I had to choose to build my career. In those days, I had lot of confusion like whether to choose System so…
The purpose of this video is to demonstrate how to set up an RSS Feed on a WordPress Website. This will be demonstrated using a Windows 8 PC. Feedburner will be used for this demonstration. Go to your WordPress login page. This will look like the…
The purpose of this video is to demonstrate how to set up basic WordPress SEO. This will be demonstrated using a Windows 8 PC. The plugin used will be WordPress SEO by Yoast. Go to your WordPress login page. This will look like the following: myw…

803 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