Solved

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

Posted on 2009-05-08
13
832 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
 
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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
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…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

863 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

20 Experts available now in Live!

Get 1:1 Help Now