Solved

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

Posted on 2009-05-08
13
833 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
  • 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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 500 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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

770 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