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

Discussion in 'ASP General' started by Tim, Oct 12, 2004.

  1. Tim

    Tim Guest

    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%>
    Tim, Oct 12, 2004
    #1
    1. Advertising

  2. 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" <> wrote in message
    news:...
    > 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%>
    Aaron [SQL Server MVP], Oct 12, 2004
    #2
    1. Advertising

  3. Tim

    Patrice Guest

    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" <> a écrit dans le message de
    news:...
    > 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%>
    Patrice, Oct 12, 2004
    #3
  4. Tim

    Tim Guest

    "Aaron [SQL Server MVP]" <> wrote in message news:<>...
    > 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" <> wrote in message
    > news:...
    > > 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
    Tim, Oct 13, 2004
    #4
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Simon Niederberger
    Replies:
    2
    Views:
    16,501
    Christian Kaufhold
    Jan 7, 2005
  2. Andrew Crowe
    Replies:
    1
    Views:
    4,461
    Andrew Crowe
    Sep 13, 2004
  3. Froefel
    Replies:
    1
    Views:
    788
    Mark Rae [MVP]
    Jul 4, 2007
  4. billb

    passing variable to sql statement in asp.net 2.0?

    billb, Jul 10, 2006, in forum: ASP .Net Web Controls
    Replies:
    1
    Views:
    188
    Alessandro Zifiglio
    Jul 12, 2006
  5. weiwei

    asp and sql statement in sql server db

    weiwei, Sep 22, 2004, in forum: ASP General
    Replies:
    3
    Views:
    204
    Jeff Cochran
    Sep 22, 2004
Loading...

Share This Page