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

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?
LVL 12
rcmbAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

hhammashCommented:
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
hhammashCommented:
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
rcmbAuthor Commented:
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
hhammashCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Development Software

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.