Solved

How do I pass a variable to a SQL statement in FP

Posted on 2003-10-28
4
282 Views
Last Modified: 2013-12-24
I have a form that list multiple divisions in a drop down list. I want to be able to assign the department to the division without having to ask the user to select the department.

My SQL statement might look something like

Select Lname, Fname, SSN From Personnel Where Division='::Division::' and Dept='::varDept::'

I will use VBScript to create the varDept entry but I cannot figure out how to pass the variable to the sql statement. Any ideas?
0
Comment
Question by:rcmb
  • 3
4 Comments
 
LVL 14

Expert Comment

by:hhammash
ID: 9639784
Hi,

You will have to use the dynamic array.  The array should be multi-dimensional.  The list box will display the divisin,  then when the user makes the selection,  the department field will be automatically filled with the second dimension of the array which has the department name.

regards
hhammash

0
 
LVL 14

Expert Comment

by:hhammash
ID: 9639995
Hi,
Plese check this link:
http://www.fmhs.uaeu.ac.ae/nml/900test.asp

If this is what you need,  there is a very easy way to do it in Frontpage:

This is the way.
------------------
The page names here are only for my example, you can change them:

01- Create a new page and save it as SubmitInfo.asp
02- Insert a form
03- Remove the buttons of the form
04- Inside the form insert a DRW
05- Pick up only the Division field
06- Make the DRW drop down menu (Option 4/5)
07- Then proceed and finish the DRW
08- Right click the form select properties and name the form as MyForm
09- Post the form to SubmitInfo.asp
10- Go back to the page, click once on the Drop down
11- Switch to HTML view
12- Add onChange=Myform.submit() to the drop down code
It should look like:
<select NAME="CustomerID" SIZE="1" onChange=MyForm.submit()>
13- Go back to the page,  save it
14- Under the first form insert a new form,  keep the buttons
15- In the second form insert another DRW
16- Make the DRW to pick up only Department Field
17- In the criteria select DivisionName=DivisionName
18- In the default value put a division number to keep the DRW, otherwise you will have  "no records returned".
19- Continue with the DRW and make it list-one field per item
20- In the list options select TextFields
21- Continue the DRW, and uncheck the Add Search form checkbox
22- Finish the DRW
23- Go to the page and remove the buttons of the DRW
24- Save the page
25- Post the second form which has the second DRW to the Database
26- Save the page

Hope it helps
hhammash
0
 
LVL 12

Author Comment

by:rcmb
ID: 9668058
hhammash

What you posted helps the example I provided but did not answer the question. I am trying to pass a variable to a sql statement. I can do it if I just use straight ASP but what I am trying to do is get a vaiable accepted by the SQL code in FrontPage. Any ideas?

RCMB
0
 
LVL 14

Accepted Solution

by:
hhammash earned 250 total points
ID: 9669258
Hi,

I hope I understood what you meant.  Please try the above link again,  select a customerid, the country will be selected and press submit.

If this is what you need.  You have to follow my steps above but:

After step 16 and before 17,  lets say 16a:
1- Right Click the form
2- Select form properties
3- Click advanced
4- Click Add
5- Type the field name exactly,  in my case it is CustomerID
6- In the value type <%=request.form("CustomerID")%>

Modify Step 17 and select Division=Division and Dept=Dept

Your sql should look like:
Select Lname, Fname, SSN From Personnel Where(Division='::Division::' and Dept='::varDept::')

varDept should be the field in the form hidden field.

Then post the form to the page that has the SQL,  which is waiting to the variables passed from the form.  If the SQL is on the same page then post the form to the page itself.

Regards
hhammash

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
Code Editors 10 212
Renaming file after uploading in Coldfusion 4 253
LMS Tracking Survey Result 1 51
How to protect website documents without Windows Security 7 112
When deciding to adopt any help desk solutions many factors should be explored before taking decisions. This will change from business to another but in general there are some kind of rule of thumb. Here are some quick tips: Do we need only ticket…
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 manually back up a WordPress Database. This will be demonstrated using a Windows 8 PC. The Host used will be IPage.com Log into your Hosting account. IPage will be used for demonstration : Locat…
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…

825 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