Display data in drop down

[foundsheep]

I have a drop down that will be auto populated when the user selects a previous drop down. The one in question is referencing a particular piece of equipment attached to the company that was selected. The database is set up with a manufacturer table, equipment table, and customer equipment table. My issue is not knowing the correct SQL statement to write in order to display the appropriate equipment. The display in the drop should pull manufacturer name from the mfg table and the description from the equipment table at the same time it should be the unique equipment for that company (that is based on the serial number). A helping hand is much appreciated.

[SheSaidImaPregy]

"SELECT a.manufacturername, b.description FROM manufacturer a JOIN equipment b ON a.manufacturerID=b.manufacturerID"

This assumes that you have a relational database and they are setup like this:

Manufacturer
- manufacturerID
- manufacturername

Equipment
- manufacturerID
- equipmentID (unique or Primary)
- description

[foundsheep]

Thanks for the response. My ddl looks like this:

<asp:DropDownList ID="DropDownListEquip" runat="server" CssClass="maintext" 
                                                                                DataSourceID="Equipment" DataTextField="mfgID" DataValueField="mfgID">
                                                                            </asp:DropDownList>

Here is the datasource:

<asp:SqlDataSource 
                                                                                ID="Equipment" 
                                                                                runat="server" 
                                                                                ConnectionString="<%$ ConnectionStrings:HRIServiceConnectionString1 %>" 
                                                                                SelectCommand="SELECT a.Company, b.Description FROM Manufacturers a JOIN Equipment b ON a.mfgID=b.mfgID">
                                                                                <SelectParameters>
                                                                                <asp:ControlParameter 
                                                                                ControlID="DropDownList1"
                                                                                Name="cusID"
                                                                                PropertyName="SelectedValue" 
                                                                                Type="String" />
                                                                                </SelectParameters>
                                                                            </asp:SqlDataSource>

And here is the error:

DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'mfgID'.

[SheSaidImaPregy]

That says that there is no column in your table with the name of mfgID

[foundsheep]

Au Contrair ... there is a mfgID in both Manuf and Equip. Which table is the error referring to?

[SheSaidImaPregy]

doesn't say. Could be either.

[foundsheep]

Ok, here is what I did. I removed ...
DataTextField="mfgID" DataValueField="mfgID"
from the ddl.

Now "System.Data.DataRowView" shows up in the ddl as the selection. It's not working but the error didn't come up this time.

[SheSaidImaPregy]

It's not working because you have no values to assign to it. And it won't fail cause nothing was done in the first place.

Check both your databases to assure yourself that mfgID is spelled correctly and exists in both tables.

[foundsheep]

There's only on db and the tables in question definitely have mfgID and it is spelled correctly.

The difference I see between this ddl an the others I have on this page is DataTextField="mfgID". The others have something different than the datavaluefield. I'm just not sure what is supposed to be in this case.

[SheSaidImaPregy]

Oh woops, I wasn't paying attention.

You can only put values you retrieve into the DROPDOWNLIST. You never pulled mfgID so you cannot use it as a TextValue or DataValue. That query you want this:

"SELECT Company, mfgID FROM Manufacturers"

Then, after the user picks the DDL, you want to populate a datalist or gridview or whatever with the correct info:

"SELECT a.Company, b.Description FROM Manufacturers a JOIN Equipment b ON a.mfgID=b.mfgID"

Am I right?

[foundsheep]

Basically, what is happening here is that a ticket is being created for a company. Whenever the company is selected from a ddl, then their associated equipment will populate another ddl. At this point the user selects the appropriate equip and fills the rest of the ticket out.

I don't think I need two Select statements though. I have another table called cus_equip related to equipment and manufacturers that identifies the specific equipment with the right company. Tying all of that together to show in the ddl is where I'm falling short in my knowledge.

[foundsheep]

Appendix A to my last post.

My table structure:

Manufacturer:
mfgID
Company
etc...

Equipment
eqpID
mfgID
Description
etc...

Cus_Equip
cuseqpID
SerialNumber
cusID
eqpID

[SheSaidImaPregy]

Well, you either need two queries, or one query and a dataset stored in memory. Basically, for the price of that dataset, you're better off with the second query.

However, you want two separate DDL's, then two queries is the best way to go.

After the first DDL is populated, an onSelectedIndexChange should strike a sub that populates the second DDL. However, you cannot populate the second DDL before the first one is chosen, hence you need two separate connections to the server. You can do this all with 1 query, but there is no need to pull a thousand records when you only need 10. Since you are requesting them at two different times, make those queries as best as possible to grab only the information you need.

[SheSaidImaPregy]

The way I would do it would be like this:

<form runat="server">
<asp:DropDownList ID="ddl1" onselectedindexchanged="ddl1_change" autopostback="true" runat="server" />
<asp:DropDownList ID="ddl2" runat="server" />
...
...
</form>

<script runat="server" language="vb">
Sub Page_Load(ByVal S As Object, ByVal E As EventArgs)
  Dim conn As New SqlConnection( connectionstring )
  Dim cmd As New SqlCommand ("SELECT company, mfgID FROM Manufacturers", conn)
  Dim dtrReader As SqlDataReader

  Try
    conn.Open()
    dtrReader = cmd.ExecuteReader()
    if dtrReader.HasRows then
      ddl1.DataSource = dtrReader
      ddl1.DataTextField = "company"
      ddl1.DataValueField = "mfgID"
      ddl1.DataBind()
    end if
    dtrReader.Close()
    conn.Close()
  Catch
  End Try
End Sub

Sub ddl1_change(ByVal S As Object, ByVal E As EventArgs)
  Dim conn As New SqlConnection( connectionstring )
  Dim cmd As New SqlCommand ("SELECT description, eqpID FROM Equipment WHERE mfgID=@mfgID", conn)
''
''However, you should change description to a name of the equipment, if there is a field for it.
''
  cmd.Parameters.AddWithValue("@mfgID", Trim(ddl1.SelectedItem.Value))
  Dim dtrReader As SqlDataReader

  Try
    conn.Open()
    dtrReader = cmd.ExecuteReader()
    if dtrReader.HasRows then
      ddl2.DataSource = dtrReader
      ddl2.DataTextField = "equipment"
      ddl2.DataValueField = "equipID"
      ddl2.DataBind()
    end if
    dtrReader.Close()
    conn.Close()
  Catch
  End Try
End Sub
</script>

Wow I forgot how much code goes into a simple bind. I Created myself a "cheat sheet" where I just reference functions that do all my binding for me. I could have done these in 4 lines each

[foundsheep]

Also, this is actually the second ddl being populated by the first one.

I have this working already for a contacts ddl:

Protected Sub DropDownList2_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DropDownList2.SelectedIndexChanged

I'm still unsure how I can get the correct equipment tied to the ddl. I'm assuming is has to reference the cuseqpID in the Cus_Equip table.

[SheSaidImaPregy]

please post to me in words exactly what you want (all ddl's, how you want them populated, what they contain, etc.)

And ill pump you out the code for it.

[foundsheep]

I attached a visual of what I'm trying to accomplish. I didn't annotate that the Equipment ddl would be populated by the company. By the way the company ddl refers to the owner of the equipment not the manufacturer.

[foundsheep]

Everything is working with the ddl population outside of the Equipment ddl (DropDownListEquip) being populated when the Company ddl (DropDownList1). As I said before, there is a Customer Equipment table (Cus_Equip) with these fields:

cuseqpID
eqpID
cusID
SerialNumber (doesn't need to be selected)

Manufacturer Table (Manufacturers)

mfgID
Company (this will be the first piece of text in the Equipment ddl)

Equipment Table (Equipment)

eqpID
mfgID
Description (This will be the second piece of text to show in the equipment ddl)

If something seems to be missing or doesn't jive with the db, let me know. Thanks.

[foundsheep]

Here is my working code for the ddl:

Partial Class AddCall
    Inherits System.Web.UI.Page
    Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DropDownList1.SelectedIndexChanged
        DropDownList2.SelectedIndex = 0
    End Sub
    Protected Sub DropDownList2_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DropDownList2.SelectedIndexChanged
        'labelSelection.Text = DropDownList1.SelectedItem.Text & " " & DropDownList2.SelectedItem.Text
        
        Dim Company As TextBox = TryCast(FormView1.FindControl("Company"), TextBox)
        Dim Address1 As TextBox = TryCast(FormView1.FindControl("Address1"), TextBox)
        Dim Address2 As TextBox = TryCast(FormView1.FindControl("Address2"), TextBox)
        Dim Phone As TextBox = TryCast(FormView1.FindControl("Phone"), TextBox)
        Dim FirstName As TextBox = TryCast(FormView1.FindControl("FirstName"), TextBox)
        Dim LastName As TextBox = TryCast(FormView1.FindControl("LastName"), TextBox)
        Dim City As TextBox = TryCast(FormView1.FindControl("City"), TextBox)
        Dim State As TextBox = TryCast(FormView1.FindControl("State"), TextBox)
        Dim Zip As TextBox = TryCast(FormView1.FindControl("Zip"), TextBox)

        Dim str_sql As String = "SELECT * FROM Customers WHERE cusID = " & DropDownList1.SelectedValue
        Using connection As New System.Data.SqlClient.SqlConnection("Data Source=IT-P02\SQLEXPRESS;Initial Catalog=HRIService;Integrated Security=True")
            Dim command As New System.Data.SqlClient.SqlCommand(str_sql, connection)
            connection.Open()
            Dim reader As System.Data.SqlClient.SqlDataReader = command.ExecuteReader()
            Try
                If reader.Read Then
                    Company.Text = reader.Item("Company").ToString
                    Address1.Text = reader.Item("Address1").ToString
                    Address2.Text = reader.Item("Address2").ToString
                    Phone.Text = reader.Item("Phone").ToString
                End If
            Finally
                ' Always call Close when done reading.
                reader.Close()
            End Try
            str_sql = "SELECT * FROM CONTACTS WHERE contactID = " & DropDownList2.SelectedValue
            command = New System.Data.SqlClient.SqlCommand(str_sql, connection)
            reader = command.ExecuteReader()
            Try
                If reader.Read Then
                    FirstName.Text = reader.Item("FirstName").ToString()
                    LastName.Text = reader.Item("LastName").ToString()
                End If
            Catch ex As Exception
                reader.Close()
            End Try

            reader.Close()
            str_sql = "SELECT * FROM Zip WHERE zipID = " & DropDownList1.SelectedValue
            command = New System.Data.SqlClient.SqlCommand(str_sql, connection)
            reader = command.ExecuteReader()
            Try
                If reader.Read Then
                    City.Text = reader.Item("City").ToString()
                    State.Text = reader.Item("State").ToString()
                    Zip.Text = reader.Item("Zip").ToString()
                End If
            Catch ex As Exception
                reader.Close()
            End Try
        End Using

    End Sub
    Protected Sub DropDownListEquip_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)

    End Sub

End Class

[SheSaidImaPregy]

Well that depends on whether or not each manufacturer has different equipment. So once the first ddl is populated (Company), you can then populate the third ddl (equipment). Unless for some reason equipment is the same to all companies? If so, why do you have the mfgID in the quipment table? ^^

[foundsheep]

A company can have multiple pieces of equipment from different manufacturers. And each manufacturer has several different models of equipment. Manufacturer table will hold manufacturer company name, address, etc. While the equipment will have model name, description, etc. Does that logic make sense? I've had to tweak my db a few times, so by no means is it the end all be all.

[SheSaidImaPregy]

Then no it isn't dependant on anything and should be populated on page_load, or onselectedindexchanged of dropdownlist1