1

I am in the process of building my first website using Visual Studio 2012, and MySQL. This is my first experience really using either of them, so my knowledge is lacking, but I'm slowly picking it up.

I am trying to develop a website that will be used for our lockout/tagout database at work. It will eventually have the ability to automatically generate reports/tags, but I have not gotten that far as of yet. What I would like to be able to do, is provide two dropdownlists, one for the complex, and another for machines (these will be used to sort the given devices). When the user selects a specific complex, I would like the machine dropdownlist to be limited to the machines specific to that complex. For example, my SQL query would look something like this:

SELECT * FROM machine WHERE complex_id = complex.complex_id ORDER BY machine_name;

In order to compare machine.complex_id and complex.complex_id, I figured that the use of a parameter was the correct approach to take.

SELECT * FROM machine WHERE complex_id = @compid ORDER BY machine_name;

However, I am getting hung up with the use of parameters in the SQL statements. Having looked over the following page: MSDN, I was able to figure out how one would use a parameter in an SQL statement. However, it was not clear to me as to how one would actually create the parameter. Even after much googling, I haven't found anything that really stood out as the correct approach for what it is I am trying to accomplish.

EDIT

<%@ Page Title="Lockout" Language="VB" MasterPageFile="~/Site.Master" 
AutoEventWireup="true" CodeBehind="Lockout.aspx.vb" Inherits="Lockout.Lockout" %>

<asp:Content runat="server" ID="BodyContent" ContentPlaceHolderID="MainContent">

<asp:DropDownList 
    ID="DropDownList_Complex" 
    runat="server" 
    DataTextField="complex_name"
    DataSourceID="SqlDataSource_Complex" 
    DataValueField="complex_id" 
    AutoPostBack="True" 
    AppendDataBoundItems="True">
    <asp:ListItem Value="" Selected="True">
        Select
    </asp:ListItem>
</asp:DropDownList>

<asp:DropDownList
    ID="DropDownList_Machine"
    runat="server"
    DataTextField="machine_name"
    DataSourceID="SqlDataSource_Machine"
    DataValueField="machine_id"
    AutoPostBack="True"
    AppendDataBoundItems="true">
    <asp:ListItem Value="" Selected="True">
        Select
    </asp:ListItem>
</asp:DropDownList>

<asp:SqlDataSource 
    ID="SqlDataSource_Complex" 
    runat="server" 
    ConnectionString="<%$ ConnectionStrings:lockoutConnectionString %>" 
    ProviderName="<%$ ConnectionStrings:lockoutConnectionString.ProviderName %>" 
    SelectCommand="SELECT complex_id, complex_name FROM complex ORDER BY complex_name">
</asp:SqlDataSource>

<asp:SqlDataSource
    ID="SqlDataSource_Machine"
    runat="server"
    ConnectionString="<%$ ConnectionStrings:lockoutConnectionString %>"
    ProviderName="<%$ ConnectionStrings:lockoutConnectionString.ProviderName %>"
    SelectCommand="SELECT machine_id, machine_name, complex_id FROM machine ORDER BY machine_name">
</asp:SqlDataSource>

kubiej21
  • 700
  • 4
  • 14
  • 29
  • There are a lot of examples on the web as well as in StackOverflow. The first StackOverflow answer I found was [here](http://stackoverflow.com/a/652999/2091410); there are many more. – Ed Gibbs Jul 02 '13 at 14:12
  • Where is your code where you create the `SqlConnection`? – Karl Anderson Jul 02 '13 at 14:16
  • Maybe I'm not understanding it, but to me it looks as though that page is using parameters within the SQL statement, and is not necessarily creating them. When selecting a given complex, is that the way in which I would need to store complex.complex_id? – kubiej21 Jul 02 '13 at 14:19
  • I have included the code for the SQLConnection. – kubiej21 Jul 02 '13 at 14:50

1 Answers1

1

You want to use a ControlParameter in the SqlDataSource (the one that populates your machine dropdown) that points to the first dropdown you mentioned (the complex one). Like this:

<asp:SqlDataSource
    ID="SqlDataSource_Machine"
    runat="server"
    ConnectionString="<%$ ConnectionStrings:lockoutConnectionString %>"
    ProviderName="<%$ ConnectionStrings:lockoutConnectionString.ProviderName %>"
    SelectCommand="SELECT machine_id, machine_name, complex_id FROM machine WHERE complex_id=@complex_id ORDER BY machine_name">
    <SelectParameters>
        <asp:ControlParameter Name="complex_id" ControlId="DropDownList_Complex" PropertyName="SelectedValue"/>
    </SelectParameters>
</asp:SqlDataSource>

This way, when you select a value in DropDownList_Complex, it automatically populates the parameter for the query populating the next dropdown.

Note that you will probably want to set "AutoPostBack" to true on DropDownList_Complex (to make this reload automatically).

Josh Darnell
  • 11,304
  • 9
  • 38
  • 66