Is it possible to have multiple dropdown boxes in one form that use the same database table in ASP.Net

jferg

SOLVED CLOSED ACTIVE ANSWERED
User: jferg
Posted: on Jul 22, 2016 12:05 PM
Forum: SQL Server
Answer: 1
Views: 3983

My form has two dropdown fields for unit type. They are UnitType and UnitTypeTwo. Here is the SelectCommand of the SqlDataSource:

SELECT v.venId, v.venNm, v.venStAdd, v.venUnitType, ut.unitType, v.venUnit, 
v.venUnitTypeTwo, ut2.unitType, v.venUnitTwo, v.venCity, v.venStateAbbr, 
v.venZip, v.venCountry, v.venPh, v.venCmnts, v.venActive 
FROM vendors v 
LEFT JOIN unitTypes ut 
ON v.venUnitType = ut.unitTypeId 
LEFT JOIN unitTypes ut2 
ON v.venUnitTypeTwo = ut2.unitType 
WHERE ([venId] = @venId)

Here is the ItemTemplate of the FormView:

<ItemTemplate>
    <asp:Label Text='<%# Eval("venId") %>' runat="server" ID="venIdLabel" Visible="false" /><br />
    <div class="row">
        <div class="col-md-3">
            <strong>Vendor</strong><br />
            <asp:Label Text='<%# Bind("venNm") %>' runat="server" ID="venNmLabel" /><br />
        </div>
        <div class="col-md-3">
            <strong>Address</strong><br />
            <asp:Label Text='<%# Bind("venStAdd") %>' runat="server" ID="venStAddLabel" /><br />
        </div>
        <div class="col-md-3">
            <strong>Unit Type</strong><br />
            <%--<asp:Label Text='<%# Bind("venUnitType") %>' runat="server" ID="venUnitTypeLabel" /><br />--%>
            <asp:Label Text='<%# Bind("ut.unitType") %>' runat="server" ID="venUnitTypeLabel" /><br />
        </div>
        <div class="col-md-3">
            <strong>Unit</strong><br />
            <asp:Label Text='<%# Bind("venUnit") %>' runat="server" ID="venUnitLabel" /><br />
        </div>
    </div>
    <div class="row">
        <div class="col-md-3">
            <strong>Unit Type</strong><br />
            <%--<asp:Label Text='<%# Bind("venUnitTypeTwo") %>' runat="server" ID="venUnitTypeTwoLabel" /><br />--%>
            <asp:Label Text='<%# Bind("ut2.unitType") %>' runat="server" ID="venUnitTypeTwoLabel" /><br />
        </div>
        <div class="col-md-3">
            <strong>Unit</strong><br />
            <asp:Label Text='<%# Bind("venUnitTwo") %>' runat="server" ID="venUnitTwoLabel" /><br />
        </div>
        <div class="col-md-3">
            <strong>City:</strong><br />
            <asp:Label Text='<%# Bind("venCity") %>' runat="server" ID="venCityLabel" /><br />
        </div>
        <div class="col-md-3">
            <strong>State</strong><br />
            <asp:Label Text='<%# Bind("venStateAbbr") %>' runat="server" ID="venStateAbbrLabel" /><br />
        </div>
    </div>
    <div class="row">
        <div class="col-md-3">
            <strong>Zip Code</strong><br />
            <asp:Label Text='<%# Bind("venZip") %>' runat="server" ID="venZipLabel" /><br />
        </div>
        <div class="col-md-3">
            <strong>Country</strong><br />
            <asp:Label Text='<%# Bind("venCountry") %>' runat="server" ID="venCountryLabel" /><br />
        </div>
        <div class="col-md-3">
            <strong>Phone</strong><br />
            <asp:Label Text='<%# Bind("venPh") %>' runat="server" ID="venPhLabel" /><br />
        </div>
        <div class="col-md-3">
            <strong>Active</strong><br />
            <asp:CheckBox Checked='<%# Bind("venActive") %>' runat="server" ID="venActiveCheckBox"
                Enabled="false" /><br />
        </div>
    </div>
    <div class="row">
        <div class="col-md-3">
            <strong>Comments</strong><br />
            <asp:Label Text='<%# Bind("venCmnts") %>' runat="server" ID="venCmntsLabel" /><br />
        </div>
    </div>
    <asp:LinkButton runat="server" Text="Edit" CommandName="Edit" ID="EditButton" CausesValidation="False"
        CssClass="cmdlinkpdg" />&nbsp;<%--<asp:LinkButton runat="server" Text="Delete" CommandName="Delete" ID="DeleteButton" CausesValidation="False" />&nbsp;--%><asp:LinkButton
            runat="server" Text="New" CommandName="New" ID="NewButton" CausesValidation="False" />
</ItemTemplate>

The first dropdown list works just fine until I modify the SelectCommand to include the value of the second unit type. How do I need to modify the FormView or the SelectCommand, or both - if this is possible?

Thanks, J.

Andrea

0 1

Reply
User: Andrea
Replied: on Jul 22, 2016 12:05 PM
Report

Hi @jferg, Please try the following [Solved] Use one AJAX UpdateProgress control for Multiple UpdatePanel on same page in ASP.Net It might help you. Cheers Andrea.

dharmend...

0 2

Reply
User: dharmendr
Replied: on Jul 25, 2016 08:44 AM
Report

Hi jferg,

Will you please explain your requirement in more details. If possible expalin with example.

jferg

0 3

Reply
User: jferg
Replied: on Jul 26, 2016 11:05 AM
Modified: on Jul 26, 2016 11:06 AM
Report

Hi dharmendr.

I have a simple query that joins two tables.

The first table is "vendors". This table contains two fields that are populated using data from another database table called "unitTypes" because each mailing address can contain two unit types (i.e. Building A, Floor 2).

The table "unitTypes" contains two fields, "unitId" and "unitType". unitId is stored in the "vendors" table in two fields, "venUnitType" and "venUnitTypeTwo".

Notice that in the SELECT statement of the query I have "v.venUnitType" and "v.venUnitTypeTwo", each of which store the integer associated with corresponding "unitType" found in the "unitTypes" table. So to display the value instead of the integer I joined to the "unitTypes" table twice and added "ut.unitType" and "ut2.unitType". These two fields provide the value instead of the integer.

The query works just fine. But the issue occurs when I try to modify the ItemTemplate of the formview to display the values instead of the integers. The issue is because both unit type fields get their data from the same source table and field. So, I assumed I should use the table alias in the data binding of the label, but that's when it stops working. Here are the two Labels:

<asp:Label Text='<%# Bind("ut.unitType") %>' runat="server" ID="venUnitTypeLabel" />

<asp:Label Text='<%# Bind("ut2.unitType") %>' runat="server" ID="venUnitTypeTwoLabel" />

As a short-term solution I added a third table that contains unit types as well. I just gave it a different name and titled the fields differently. Then I modified the query and labels of the ItemTemplate. It works great, but it's not a very elegant solution.

Any help is greatly appreciated.

Thanks,

jferg

dharmend...

0 4

Reply
ANSWER
User: dharmendr
Replied: on Jul 27, 2016 05:13 AM
Report

Your query should be like below.

SELECT v.venId, v.venNm, v.venStAdd, v.venUnitType, ut.unitType as unitType1, v.venUnit,
v.venUnitTypeTwo, ut2.unitType as unitType2, v.venUnitTwo, v.venCity, v.venStateAbbr,
v.venZip, v.venCountry, v.venPh, v.venCmnts, v.venActive
FROM vendors v
LEFT JOIN unitTypes ut ON v.venUnitType = ut.unitTypeId
LEFT JOIN unitTypes ut2 ON v.venUnitTypeTwo = ut2.unitType
WHERE ([venId] = @venId)

And code like below.

<asp:Label Text='<%# Bind("unitType1") %>' runat="server" ID="venUnitTypeLabel" /> 
<asp:Label Text='<%# Bind("unitType2") %>' runat="server" ID="venUnitTypeTwoLabel

What you have to do is give alias for the column so that this will be unique name to bind to the FormView and call with alias name to bind.

Is it possible to have multiple dropdown boxes in one form that use the same database table in ASP.Net

Company

Explore

Follow us

Contact Us