Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2009-05-08
13
Medium Priority
?
839 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:Howard Bash
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 96

Expert Comment

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

Author Comment

by:Howard Bash
ID: 24344112
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
ID: 24345253
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 1

Author Comment

by:Howard Bash
ID: 24345436
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
ID: 24345994
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:Howard Bash
ID: 24346745
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
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24348155
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:Howard Bash
ID: 24354234
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
ID: 24355366
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:Howard Bash
ID: 24356003
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
ID: 24357493
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:Howard Bash
ID: 24358240
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 2000 total points
ID: 24358397
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

661 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