Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > use a variable for -table selection- in my ASP SQL statement

Reply
Thread Tools

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

 
 
Tim
Guest
Posts: n/a
 
      10-12-2004
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%>
 
Reply With Quote
 
 
 
 
Aaron [SQL Server MVP]
Guest
Posts: n/a
 
      10-12-2004
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.

--
http://www.aspfaq.com/
(Reverse address to reply.)




"Tim" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) om...
> 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%>



 
Reply With Quote
 
 
 
 
Patrice
Guest
Posts: n/a
 
      10-12-2004
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

--

"Tim" <(E-Mail Removed)> a écrit dans le message de
news:(E-Mail Removed) om...
> 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%>



 
Reply With Quote
 
Tim
Guest
Posts: n/a
 
      10-13-2004
"Aaron [SQL Server MVP]" <(E-Mail Removed)> wrote in message news:<(E-Mail Removed)>...
> 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.
>
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
>
>
> "Tim" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed) om...
> > 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%>


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
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using a SQL statement with variable parameters and variable criteria Froefel ASP .Net 1 07-04-2007 09:38 AM
passing variable to sql statement in asp.net 2.0? billb ASP .Net Web Controls 1 07-12-2006 09:48 AM
asp and sql statement in sql server db weiwei ASP General 3 09-22-2004 04:12 PM
DBI SQL column datatype not jiving with SQL statement requirement dna Perl 1 01-18-2004 04:15 PM
Re: SQL statement working in SQL Server but not in .aspx.cs page William \(Bill\) Vaughn ASP .Net 0 08-21-2003 10:41 PM



Advertisments