• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 179
  • Last Modified:

How do I bind a database query to a label using datasets in ASP 2.0 with C# in VS 2005?

I am new to VS 2005, ASP 2.0, and C#.  I have not been able to figure out how to do this so I am sorry if this is a newbie question and I missed it somewhere, Anyway on to the question.

I have a gridview control for a website I am working on.  I have setup a template for one of the columns in the gridview.

My table layout has three fields:
categoryid (int), categoryname(varchar), parentid(int)
to allow for a nested category table.

Those categories with a null parentid are a root category.  I have my EditItemTemplate working fine with a dropdownlist for the parentid.  It is able to grab the categoryname for the parentid and display that and allow the user to select a different category if they choose.

I have a problem with my ItemTemplate.  I am trying to use a label control instead of a dropdown control.  Right now I have it just displaying the parentid.  Is what I need to do is have it perform a lookup against the database to grab the categoryname of this parentid and display the categoryname instead.  I see in edit DataBindings I can apply a Custom binding to the label.  I figure I probably need to create some sort of custom expression or class to do this.  Unfortunately since I am new to C#, ASP and VS 2005 I have not been able to figure out a way to do this.  I would appreciate an explanation of how to do this or a link to a tutorial on how this is done.  I am using datasets to make queries against the DB.  Below is the code for my gridview control.


    <asp:GridView ID="categoryeditlist" runat="server" AutoGenerateColumns="False" DataKeyNames="categoryid" DataSourceID="ObjectDataSource2" AllowPaging="True" AllowSorting="True" PageSize="50">
        <Columns>
            <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
            <asp:BoundField DataField="categoryname" HeaderText="Category Name" SortExpression="categoryname" />
            <asp:TemplateField HeaderText="Parent Category">
                <EditItemTemplate>
                    <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="ObjectDataSource3"
                        DataTextField="categoryname" DataValueField="categoryid"  SelectedValue='<%# Eval("parentid") %>' AppendDataBoundItems="True">
                        <asp:ListItem Text="Select a Parent Category" Value=""/>
                    </asp:DropDownList><asp:ObjectDataSource ID="ObjectDataSource3" runat="server" DeleteMethod="Delete"
                        InsertMethod="Insert" OldValuesParameterFormatString="original_{0}" SelectMethod="GetParentCategoriesSelected"
                        TypeName="netjaxerTableAdapters.categoryTableAdapter" UpdateMethod="Update">
                        <DeleteParameters>
                            <asp:Parameter Name="Original_categoryid" Type="Int32" />
                        </DeleteParameters>
                        <UpdateParameters>
                            <asp:Parameter Name="categoryname" Type="String" />
                            <asp:Parameter Name="parentid" Type="Int32" />
                            <asp:Parameter Name="Original_categoryid" Type="Int32" />
                        </UpdateParameters>
                        <InsertParameters>
                            <asp:Parameter Name="categoryname" Type="String" />
                            <asp:Parameter Name="parentid" Type="Int32" />
                        </InsertParameters>
                    </asp:ObjectDataSource>
                </EditItemTemplate>
                <ItemTemplate>

                    <asp:Label ID="Label1" runat="server" Text='<%# Eval("parentid") %>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
        </Columns>
       
    </asp:GridView>
    <asp:ObjectDataSource ID="ObjectDataSource2" runat="server" DeleteMethod="Delete"
        InsertMethod="Insert" OldValuesParameterFormatString="original_{0}" SelectMethod="GetCategories"
        TypeName="netjaxerTableAdapters.categoryTableAdapter" UpdateMethod="Update">
        <DeleteParameters>
            <asp:Parameter Name="Original_categoryid" Type="Int32" />
        </DeleteParameters>
        <UpdateParameters>
            <asp:Parameter Name="categoryname" Type="String" />
            <asp:Parameter Name="parentid" Type="Int32" />
            <asp:Parameter Name="Original_categoryid" Type="Int32" />
        </UpdateParameters>
        <InsertParameters>
            <asp:Parameter Name="categoryname" Type="String" />
            <asp:Parameter Name="parentid" Type="Int32" />
        </InsertParameters>
    </asp:ObjectDataSource>


Thanks very much for any help.
0
neuroglyph
Asked:
neuroglyph
  • 2
  • 2
1 Solution
 
neuroglyphAuthor Commented:
Also here is how some sample data looks now:

CateogyryID Category Parent Category
1                 Pets
2                 Dogs                          1
3                 Cats                           1
4                 Birds                          1


What I would like:

CateogyryID Category Parent Category
1                 Pets
2                 Dogs                        Pets
3                 Cats                         Pets
4                 Birds                        Pets

Also I don't want to get an error message if it encounters a null parentid.  So if I need to handle an exception I would appreciate an explanation on how to do that.

0
 
GavinMannionCommented:
The easiest way by far is to return the Parent Name in your original query.

You other option would be to make a new call to the database when you change views. If you only need this one extra field I would highly recommend doing it in your original call.
0
 
neuroglyphAuthor Commented:
Thanks, I should have thought of that.  

Anyway is what I did is an Outer Self Join on the table and that worked.  Unfortunately the pregenerated insert, update, and delete queries had to be modified.  Maybe it was too complex for VS to create them properly.  Once I edited those queries by hand everything worked out fine.  Thanks for the help. Below is the SQL query I ended up using if anyone is interested.

SELECT     category_1.categoryid, category_1.categoryname, category_1.parentid, category_2.categoryname AS parentname,
                      category_2.categoryid AS Expr1
FROM         category AS category_1 LEFT OUTER JOIN
                      category AS category_2 ON category_1.parentid = category_2.categoryid
0
 
GavinMannionCommented:
Glad to help :)
0

Featured Post

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.

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