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.
"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:
- equipmentID (unique or Primary)
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'.
That says that there is no column in your table with the name of mfgID
Au Contrair ... there is a mfgID in both Manuf and Equip. Which table is the error referring to?
doesn't say. Could be either.
Ok, here is what I did. I removed ...
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.
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.
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.
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?
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.
Appendix A to my last post.
My table structure:
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.
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
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.
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.
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:
SerialNumber (doesn't need to be selected)
Manufacturer Table (Manufacturers)
Company (this will be the first piece of text in the Equipment ddl)
Equipment Table (Equipment)
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.
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
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? ^^
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.
Then no it isn't dependant on anything and should be populated on page_load, or onselectedindexchanged of dropdownlist1