Solved

How to build a GridView with a combobox of ref table values

Posted on 2009-05-08
13
831 Views
Last Modified: 2013-11-26
How do I build a GridView with a combobox of ref table values and the row's selected indes is a foreign key column from ref table?  I have a table with another's foreign key (the ref table).  I want that foreign key to display as the associated reference table's value.  But,  I want to use a combobox to allow edit of the row's ref value and the assocatied update with that newly selected key.  I cannot seem to wire this up with sqldatasource, sql server, and vs2005.

<asp:GridView ID="GridView2" runat="server" DataSourceID="SqlDataSourceClientLogin"  DataKeyNames="pClientGuid" AutoGenerateColumns="False"  

                   BackColor="White" BorderColor="#999999" BorderStyle="None" BorderWidth="1px" CellPadding="3" GridLines="Vertical" AllowPaging="True" 

                   Height="647px">      

                          

                <Columns>                                

                  <asp:BoundField DataField="pClientGuid" HeaderText="ClientGuid" InsertVisible="False"  />
 

                  <asp:BoundField DataField="PhoneNumberGuid" HeaderText="PhoneNumberGuid"  ReadOnly="True" />  

                  

                  <asp:BoundField DataField = "pPWD" HeaderText="Password" />  

                  <asp:BoundField DataField = "pSalutation" HeaderText="Salutation" />  

                  <asp:BoundField DataField = "pFName" HeaderText="First Name" />  

                  <asp:BoundField DataField = "pLName" HeaderText="Last Name" />  

                  <asp:BoundField DataField = "pTitle" HeaderText="Title" />  

                  <asp:BoundField DataField = "pMgmtLevel" HeaderText="MgmtLevel" />  

                  <asp:BoundField DataField = "pEmail" HeaderText="Email" />                                                    
 

                  <asp:TemplateField HeaderText="Business Category Name">

                      <ItemTemplate>

                          <asp:DropDownList ID="drpdwnBusCat" DataSourceID="SqlDataSourceBusCat" DataTextField="BusnCategoryName" DataValueField="CategoryGUID"  runat="server">

                          </asp:DropDownList>

                      </ItemTemplate>

                      <FooterStyle Width="50px" />

                  </asp:TemplateField>

                    

                  <asp:TemplateField HeaderText="Business SubCategory Name">

                      <ItemTemplate>

                          <asp:DropDownList ID="DropDownList2"  DataSourceID="SqlDataSourceBusSubCat" DataTextField="BusnSubCategoryName"  DataValueField="BusnSubCategoryName" runat="server">

                          </asp:DropDownList>

                      </ItemTemplate>

                  </asp:TemplateField> 

                                          

                </Columns>

        

                <FooterStyle BackColor="#CCCCCC" ForeColor="Black" />

                <RowStyle BackColor="#EEEEEE" ForeColor="Black" />

                <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />

                <SelectedRowStyle BackColor="#008A8C" Font-Bold="True" ForeColor="White" />

                <HeaderStyle BackColor="#000084" Font-Bold="True" ForeColor="White" />

                <AlternatingRowStyle BackColor="Gainsboro" />  

             

            </asp:GridView>

        

      <asp:Button ID="cmdSaveChanges" runat="server" Text="Show Footer Editor" /><br />

      

      </ContentTemplate>

      

            </cc1:TabPanel>

      

        </cc1:TabContainer>

     

    </div>
 

    SQLDatasource<br />

    

    <asp:SqlDataSource ID="SqlDataSourceClientLogin" runat="server" 

      ConnectionString="<%$ ConnectionStrings:_DSN %>" 

      SelectCommand="SELECT ClientGuid as pClientGuid,  PhoneNumberGuid, PWD as pPWD, Salutation as pSalutation, FName as pFName, LName as pLName, Title as pTitle, MgmtLevel as pMgmtLevel, Email as pEmail FROM ClientLogin" 

      UpdateCommandType="StoredProcedure" 

      UpdateCommand="AddUpdateClient"      

      DeleteCommand="DeleteClient"

      DeleteCommandType="StoredProcedure"

    >     

      

        <UpdateParameters>

            <asp:Parameter  Name="pClientGuid" Direction="Input" DefaultValue="2e0d45b4-8fce-4c31-a283-d28beb23d15a"/>      

            <asp:Parameter  Name="pPWD" Type="String" Size="50" Direction="Input" DefaultValue="123" ConvertEmptyStringToNull="true" />      

            <asp:Parameter  Name="pSalutation" Type="String" Size="10" Direction="Input" DefaultValue="Mr." ConvertEmptyStringToNull="true"/>      

            <asp:Parameter  Name="pFName" Type="String" Size="50" Direction="Input" DefaultValue="Ig" ConvertEmptyStringToNull="true"/>      

            <asp:Parameter  Name="pLName" Type="String" Size="50" Direction="Input" DefaultValue="Bashevkin" ConvertEmptyStringToNull="true"/>      

            <asp:Parameter  Name="pTitle" Type="String" Size="50" Direction="Input" DefaultValue="Bozo" ConvertEmptyStringToNull="true"/>      

            <asp:Parameter  Name="pMgmtLevel" Type="String" Size="50" Direction="Input" DefaultValue="King" ConvertEmptyStringToNull="true"/>      

            <asp:Parameter  Name="pEmail" Type="String" Size="80" Direction="Input" DefaultValue="hungryforbreak@yahoo.com" ConvertEmptyStringToNull="true"/>      

            <asp:Parameter  Name="pWorkedOnClientGuid" Type="String" Size="36" Direction="Output" />            

        </UpdateParameters>          

        

        <DeleteParameters>

            <asp:Parameter Name="pClientGuid" Direction="Input" /> 

        </DeleteParameters>

    

    </asp:SqlDataSource>
 

    <asp:SqlDataSource ID="SqlDataSourceBusCat" runat="server" 

      ConnectionString="<%$ ConnectionStrings:_DSN %>" 

      SelectCommand="select [CategoryGUID], [BusnCategoryName] from [BusnCategories]"

      SelectCommandType="Text" />          

      

    <asp:SqlDataSource ID="SqlDataSourceBusSubCat" runat="server" 

      ConnectionString="<%$ ConnectionStrings:_DSN %>" 

      SelectCommand="select [SubCategoryGUID], [CategoryGUID], [BusnSubCategoryName], [BusnSubCategoryDescr] from [BusnSubCategories]"

      SelectCommandType="Text" />

Open in new window

0
Comment
Question by:hbash
  • 7
  • 6
13 Comments
 
LVL 96

Expert Comment

by:Bob Learned
Comment Utility
I don't see where you defined the DropDownList...
0
 
LVL 1

Author Comment

by:hbash
Comment Utility
Please check lines 18-24 snf 26-31 and their associated data sources are listed on lines 83-91 .
0
 
LVL 96

Expert Comment

by:Bob Learned
Comment Utility
1) That DropDownList is bound to SqlDataSourceBusCat, which selects from BusnCategories.

2) You are displaying and getting the values from BusnSubCategoryName.

3) Is the "ref table" defined in SqlDataSourceBusSubCat--BusnSubCategories?
0
 
LVL 1

Author Comment

by:hbash
Comment Utility
There are two drop downs in each "row".

<asp:TemplateField HeaderText="Business Category Name">
                      <ItemTemplate>
                          <asp:DropDownList ID="drpdwnBusCat" DataSourceID="SqlDataSourceBusCat" DataTextField="BusnCategoryName" DataValueField="CategoryGUID"  runat="server">
                          </asp:DropDownList>
                      </ItemTemplate>
                      <FooterStyle Width="50px" />
                  </asp:TemplateField>
                   
                  <asp:TemplateField HeaderText="Business SubCategory Name">
                      <ItemTemplate>
                          <asp:DropDownList ID="DropDownList2"  DataSourceID="SqlDataSourceBusSubCat" DataTextField="BusnSubCategoryName"  DataValueField="BusnSubCategoryName" runat="server">
                          </asp:DropDownList>
                      </ItemTemplate>
                  </asp:TemplateField>
                                         
                </Columns>


Each has a different sqldatasource:

(for
<asp:SqlDataSource ID="SqlDataSourceBusCat" runat="server"
      ConnectionString="<%$ ConnectionStrings:_DSN %>"
      SelectCommand="select [CategoryGUID], [BusnCategoryName] from [BusnCategories]"
      SelectCommandType="Text" />          
     
    <asp:SqlDataSource ID="SqlDataSourceBusSubCat" runat="server"
      ConnectionString="<%$ ConnectionStrings:_DSN %>"
      SelectCommand="select [SubCategoryGUID], [CategoryGUID], [BusnSubCategoryName], [BusnSubCategoryDescr] from [BusnSubCategories]"
      SelectCommandType="Text" />

The first dropdown "drpdwnBusCat" uses the datasource="SqlDataSourceBusCat"
and the fields uses are: [CategoryGUID], [BusnCategoryName]


The second dropdown "DropDownList2" uses the datasource="SqlDataSourceBusSubCat"
and the fields it uses are: [SubCategoryGUID], [CategoryGUID], [BusnSubCategoryName], [BusnSubCategoryDescr]

I think you missed the second pair (second sqldatasoure SqlDataSourceBusSubCat and the dropdown "DropDownList2".
0
 
LVL 96

Expert Comment

by:Bob Learned
Comment Utility
1) I don't normally like reading through code to gain understanding, and you can see why.

2) Now that I understand how the DropDownList controls are defined, and how the data is arranged, I don't quite understand the problem that you are describing.

3) What are the table structures, and what are the foreign key columns?
0
 
LVL 1

Author Comment

by:hbash
Comment Utility
Each grid row has among other fields,  two values which are foreign keys from two reference tables.  When I display the grid with the dropdowns rendered for each row,  how can I have the dropdown display the value assocated with that row/fk guid?

The DDL for each table follows:
CREATE TABLE [dbo].[BusnCategories](
      [CategoryGUID] [uniqueidentifier] NOT NULL DEFAULT (newid()),
      [BusnCategoryName] [varchar](50) NULL
)

CREATE TABLE [dbo].[BusnSubCategories](
      [SubCategoryGUID] [uniqueidentifier] NOT NULL CONSTRAINT [DF__BusnSubCa__SubCa__32767D0B]  DEFAULT (newid()),
      [CategoryGUID] [uniqueidentifier] NOT NULL CONSTRAINT [DF__BusnSubCa__Categ__336AA144]  DEFAULT (newid()),
      [BusnSubCategoryName] [nchar](50) NULL,
      [BusnSubCategoryDescr] [varchar](200) NULL
)


0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 96

Expert Comment

by:Bob Learned
Comment Utility
I hate struggling to understand a question!!

BusnCategories ==> BusnSubCategories
     Foreign key = CategoryGUID

From the sub-category DropDownList, are you wanting to display the category value?
0
 
LVL 1

Author Comment

by:hbash
Comment Utility
Ok.  Perhaps I can generalize the problem and restate it.

I have a resultset I display in the Gridview.  The resultset has a column which is a fk to a reference table of values.  I would like to display the grid with the "fk" column displayed as a dropdown which the "fk"'s associated value displayed as the selectedvalue and the dropdown populated with all the values that the reference table contains.  

I want to allow the user to:

1. See the value selected in the dropdown per row
2. Allow the user to edit the row including the dropdown and to change this and save out the values and this new "fk".




0
 
LVL 96

Expert Comment

by:Bob Learned
Comment Utility
If you want to display values, then usually you would bind the DropDownList to a data source (which you have done).  Then, you would set the DataTextField to a field that is the display text, and then DataValueField to a different field that would be the foreign key value.  When a value is selected from the DropDownList, you can get the selected foreign key value from the DropDownList.SelectedValue property.
0
 
LVL 1

Author Comment

by:hbash
Comment Utility
How do I get the value displayed that is the value associated with each row (setting the selected value of the dropdown)?

I get back the fk in the row,  but want the value from the ref table displayed?

Do set a sqldatasource for the dropdown to select "fkName", value from table and then somehow use the "fk" in the row as the selectedValue for that dropdown?  Is so how?  Do I something like SelectedValue="<% Bind('fkName') %>" ?  But the bind needs to refer to the fk in the grid's datasource...

Thanks.

0
 
LVL 96

Expert Comment

by:Bob Learned
Comment Utility
I would think that the SqlDataSource bound to the DropDownList needs to have that column's values.  That would normally suggest an inner join in the query to pull that information from the reference table.
0
 
LVL 1

Author Comment

by:hbash
Comment Utility
Ok.  So, then the selected value for the above should not be the fkName, but should be the Value associated with that ref table key?  Also,  is there an "itemdata"  kind of property that I can use with the drop down so that if a user modifies the row,  I can write out the fk?  If not,  I would need to get that value in code and then do the row update.

Thanks.
0
 
LVL 96

Accepted Solution

by:
Bob Learned earned 500 total points
Comment Utility
If you are thinking VB6 ComboBox and "ItemData", then that is what the SelectedValue would be.  You can attach to the DropDownList.SelectedIndexChanged event, and then write some code to update the row value.  In order to get that event to fire, you would need to set DropDownList.AutoPostBack = True.

Example:

Handling Dropdown list inside Gridview with Autopostback
http://weblogs.asp.net/vikram/archive/2008/04/17/handling-dropdown-list-inside-gridview-with-autopostback.aspx
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now