use a variable for -table selection- in my ASP SQL statement

T

Tim

Hi All,
I have a small issue that I can't seem to figure out. I have a SQL
statement that is dependant on the results of a drop down to chose
which table to select from. Unfortunately it does not seem to work.
Could anyone point me in the right direction? Unfortunately the
datebase cannot be changed.

I guess my question is this. Is it possible to use a variable in a SQl
select statement to choose the table? If so, where is my syntax bad.
If not, how can i get around this without changing the database

Thanks in advance!


*** here's some of the code!!

<form method="POST" name="Form1" action="test1.asp">

<p><nobr><!--#include file="../_fpclass/fpdblib.inc"--> <%
fp_sQry="SELECT * FROM eqtable ORDER BY category"
fp_sDefault=""
fp_sNoRecords="No records returned."
fp_sDataConn="Inventory"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=True
fp_sMenuChoice="Category"
fp_sMenuValue="Category"
fp_iDisplayCols=1
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>

<select NAME="Category" SIZE="1" ONCHANGE=Form1.submit()> <option
selected><%=Request.Form("Category")%></option>
<!--#include file="../_fpclass/fpdbrgn1.inc"-->
<option><%=FP_FieldHTML(fp_rs,"Category")%></option>
<!--#include file="../_fpclass/fpdbrgn2.inc"-->
</select>
</nobr></p>
</form>

<form method="POST" name="Form2" action="YourPage2.asp">

<p><nobr><!--#include file="../_fpclass/fpdblib.inc"--> <%
fp_sQry="SELECT Name FROM " & Category & " ORDER BY Name"
fp_sDefault=""
fp_sNoRecords="No records returned."
fp_sDataConn="Inventory"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=True
fp_sMenuChoice="Sub_Category"
fp_sMenuValue="Sub_Category"
fp_iDisplayCols=1
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<%
IF Request.Form("Category") = "" Then
Else
%>
<select NAME="Sub_Category" SIZE="1" ONCHANGE=Form2.submit()> <option
selected><%=Request.Form("Sub_Category")%></option>
<!--#include file="../_fpclass/fpdbrgn1.inc"-->
<option><%=FP_FieldHTML(fp_rs,"Sub_Category")%></option>
<!--#include file="../_fpclass/fpdbrgn2.inc"-->
</select>
</nobr></p>
<input type="hidden" name="Category" value="<% =
Request.Form("Category") %>">
</form>
<%End IF%>
 
A

Aaron [SQL Server MVP]

Are you really sure you want to do this? You should read up on SQL
injection and dynamic SQL. Without considering all the problems with this
approach,

fp_sQry="SELECT Name FROM " & Category & " ORDER BY Name"

.... should be ...

fp_sQry="SELECT Name FROM " & Request.Form("Category") & " ORDER BY Name"

Also, please stop using FrontPage to generate hideous ASP code for you.
 
P

Patrice

If all those tables are using a common structure you could use a request
union to simulate having a single "product" tables with a category field.

One day it could be better to actually change the DB and do it the other way
round (having really a single table and views simulating the current tables)
before perhaps suppressing those views once all is fixed...

Hope it will help ;-)

Patrice
 
T

Tim

Aaron said:
Are you really sure you want to do this? You should read up on SQL
injection and dynamic SQL. Without considering all the problems with this
approach,

fp_sQry="SELECT Name FROM " & Category & " ORDER BY Name"

... should be ...

fp_sQry="SELECT Name FROM " & Request.Form("Category") & " ORDER BY Name"

Also, please stop using FrontPage to generate hideous ASP code for you.

This will be an internal website for a small company, so the threat
risk is very low, but thank you for the advice and the help!

Tim
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,734
Messages
2,569,441
Members
44,832
Latest member
GlennSmall

Latest Threads

Top