1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

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