Using DropDownList values in an SQL statement

Discussion in 'C#' started by lespaul00, Jun 22, 2008.

0
  1. #1
    Hello,

    I have been using Visual Web Developer 2008 to create a simple dynamic web page (at least I thought it would be simple). I used to use ColdFusion, but my requirements shifted, and I need to use ASP.NET. So, I'm trying to learn enough to convert over. This is what I have so far:

    I have 3 DropDownList boxes populated by my database. Essentially, these lists will act as filters for my main SQL statement result. However, I do not know how to tie the list box choices into my SQL statement. Let me show:

    <html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">


    [COLOR="Red"]<asp:DropDownList ID="DropDownList1" runat="server" 
        DataSourceID="AccessDataSource1" DataTextField="RegionName" 
        DataValueField="RegionID">
    </asp:DropDownList>[/COLOR]


    [COLOR="Blue"]<asp:AccessDataSource ID="AccessDataSource1" runat="server" 
        DataFile="C:\Documents and Settings\My Documents\Visual Studio 2008\WebSites\WebSite1\site.mdb" SelectCommand="SELECT RegionID, RegionName
FROM tblRegion
ORDER BY RegionName"></asp:AccessDataSource>[/COLOR]

    [COLOR="red"]<asp:DropDownList ID="DropDownList2" runat="server" 
        DataSourceID="AccessDataSource2" DataTextField="Abbreviation" 
        DataValueField="STATE_ID">
    </asp:DropDownList>[/COLOR]    

[COLOR="blue"]<asp:AccessDataSource ID="AccessDataSource2" runat="server" 
        DataFile="C:\Documents and Settings\My Documents\Visual Studio 2008\WebSites\WebSite1\site.mdb" SelectCommand="SELECT Abbreviation, STATE_ID
FROM StateNames
ORDER BY Abbreviation"></asp:AccessDataSource>[/COLOR]
   
[COLOR="red"] <asp:DropDownList ID="DropDownList3" runat="server" 
        DataSourceID="AccessDataSource3" DataTextField="Plant_Group_Name" 
        DataValueField="Plant_Group_ID">
    </asp:DropDownList>[/COLOR]
    
[COLOR="blue"]<asp:AccessDataSource ID="AccessDataSource3" runat="server" 
        DataFile="C:\Documents and Settings\My Documents\Visual Studio 2008\WebSites\WebSite1\site.mdb" SelectCommand="SELECT Plant_Group_ID, Plant_Group_Name
FROM Plant_Groups
ORDER BY Plant_Group_Name"></asp:AccessDataSource>[/COLOR] 

[COLOR="blue"]<asp:AccessDataSource ID="AccessDataSource4" runat="server" 
        DataFile="C:\Documents and Settings\My Documents\Visual Studio 2008\WebSites\WebSite1\site.mdb" SelectCommand="SELECT i.Site_Name, i.RegionID, i.STATE_ID, p.Plant_Group_ID, p.Plant_Group_Name, r.RegionName, s.Abbreviation
FROM ((Site_Info AS i 
INNER JOIN tblRegion r ON i.regionID = r.RegionID)
INNER JOIN Plant_groups AS p ON i.Plant_Group_ID = p.Plant_Group_ID)
INNER JOIN StateNames AS s ON i.STATE_ID = s.STATE_ID
WHERE 1 = 1

AND i.RegionID = [COLOR="DarkOrange"][B]4[/B][/COLOR]
AND i.STATE_ID = [COLOR="darkorange"][B]42[/B][/COLOR]
AND i.Plant_Group_ID = [COLOR="darkorange"][B]8[/B][/COLOR]
ORDER BY i.RegionID ASC, i.SITE_NAME ASC"></asp:AccessDataSource>[/COLOR]    

[COLOR="Lime"]<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
        DataSourceID="AccessDataSource4">
        <Columns>
            <asp:CommandField ShowSelectButton="True" />
            <asp:BoundField DataField="Site_Name" HeaderText="Site_Name" 
                SortExpression="Site_Name" />
        </Columns>
    </asp:GridView>[/COLOR]
    </form>
</body>
</html>
    Code (markup):
    So the first three sections of code in RED are the three DropDownLists I created. By each, there's code in BLUE that are the SQL statements to populate the lists. Great, this works fine. I have 1 question pertaining to this:

    1. How do I make the default choice for the lists "Select...", and have the results return all values? As in a filter, you want the default to not filter any results.

    Moving on,... Here's question #2:

    The 4th BLUE section of code is my main SQL statement. This will return my results based on my DropDownList criteria (in the green portion gridview). However, I do not know how to link the results of the user's choices (in the 3 list boxes) into my main SQL query. For example... in ORANGE above, I just placed in placeholder values. But, instead of "4" I want this value to be the user's choice from the "RegionID" DropDownList, and so on.

    And one final question:

    3. To connect to my database so far, I see VWD looks on my hard drive: "C:\Documents and Settings\My Documents\Visual Studio 2008\WebSites\WebSite1\site.mdb". I know from experience, that this will not work once I upload this to a web server when i'm finished. How do I change this so it will default to look in the web site's root directory?

    Any help of these two questions would be GREATLY appreciated! I am a beginner at ASP.NET. Also, I did test the SQL queries above, and they work with my database. So, you can assume no tweaking is necessary for these.

    THANKS!
     
    lespaul00, Jun 22, 2008 IP