neuroglyph
asked on
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
<Columns>
<asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
<asp:BoundField DataField="categoryname" HeaderText="Category Name" SortExpression="categoryna
<asp:TemplateField HeaderText="Parent Category">
<EditItemTemplate>
<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="ObjectDataSo
DataTextField="categorynam
<asp:ListItem Text="Select a Parent Category" Value=""/>
</asp:DropDownList><asp:Ob
InsertMethod="Insert" OldValuesParameterFormatSt
TypeName="netjaxerTableAda
<DeleteParameters>
<asp:Parameter Name="Original_categoryid"
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="categoryname" Type="String" />
<asp:Parameter Name="parentid" Type="Int32" />
<asp:Parameter Name="Original_categoryid"
</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" OldValuesParameterFormatSt
TypeName="netjaxerTableAda
<DeleteParameters>
<asp:Parameter Name="Original_categoryid"
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="categoryname" Type="String" />
<asp:Parameter Name="parentid" Type="Int32" />
<asp:Parameter Name="Original_categoryid"
</UpdateParameters>
<InsertParameters>
<asp:Parameter Name="categoryname" Type="String" />
<asp:Parameter Name="parentid" Type="Int32" />
</InsertParameters>
</asp:ObjectDataSource>
Thanks very much for any help.
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
<Columns>
<asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
<asp:BoundField DataField="categoryname" HeaderText="Category Name" SortExpression="categoryna
<asp:TemplateField HeaderText="Parent Category">
<EditItemTemplate>
<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="ObjectDataSo
DataTextField="categorynam
<asp:ListItem Text="Select a Parent Category" Value=""/>
</asp:DropDownList><asp:Ob
InsertMethod="Insert" OldValuesParameterFormatSt
TypeName="netjaxerTableAda
<DeleteParameters>
<asp:Parameter Name="Original_categoryid"
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="categoryname" Type="String" />
<asp:Parameter Name="parentid" Type="Int32" />
<asp:Parameter Name="Original_categoryid"
</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" OldValuesParameterFormatSt
TypeName="netjaxerTableAda
<DeleteParameters>
<asp:Parameter Name="Original_categoryid"
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="categoryname" Type="String" />
<asp:Parameter Name="parentid" Type="Int32" />
<asp:Parameter Name="Original_categoryid"
</UpdateParameters>
<InsertParameters>
<asp:Parameter Name="categoryname" Type="String" />
<asp:Parameter Name="parentid" Type="Int32" />
</InsertParameters>
</asp:ObjectDataSource>
Thanks very much for any help.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
Glad to help :)
ASKER
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.