Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 846
  • Last Modified:

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

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
Howard Bash
Asked:
Howard Bash
  • 7
  • 6
1 Solution
 
Bob LearnedCommented:
I don't see where you defined the DropDownList...
0
 
Howard BashSenior Software EngineerAuthor Commented:
Please check lines 18-24 snf 26-31 and their associated data sources are listed on lines 83-91 .
0
 
Bob LearnedCommented:
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Howard BashSenior Software EngineerAuthor Commented:
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
 
Bob LearnedCommented:
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
 
Howard BashSenior Software EngineerAuthor Commented:
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
 
Bob LearnedCommented:
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
 
Howard BashSenior Software EngineerAuthor Commented:
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
 
Bob LearnedCommented:
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
 
Howard BashSenior Software EngineerAuthor Commented:
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
 
Bob LearnedCommented:
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
 
Howard BashSenior Software EngineerAuthor Commented:
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
 
Bob LearnedCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now