Tips on Gridview displaying data from multiple tables

• • Reply

  12 years ago

  by kumar k

  kumar

  , India

  Joined 13 years ago

  Hi, I am looking to set up a gridview which takes data from 2 or more tables. The database at the minute has the following tables.

   Main operational table:

   Item  (pk_item_id, fk_location_id, fk_type_id, item_description)

   Two lookup tables

   Location (pk_location_id, loca_description)

  Type (pk_type_id, type_description)

   I want to get this working before I add more tables and columns to the gridview.

   I want the gridview to show the following columns:

  pk_item_id, loca_description, type_description 

   This I have got working ok. But now what I want to be able to do is when I choose the edit button is to be able to change the values for the loca_description and type_description by using two dropdownlists. These two lists will be populated from the lookup tables. Is this possible? If so what would be a good way to tackle it? Im not even looking for actual code just a brief description on the controls you would use if you were to try such a thing.

   With this project I have been landed in it a bit because I am new to asp.net and not given a lot of time to get up to speed.

   Any tips or suggestions would be greatly appreciated.

  Report abuse
• • Reply

  12 years ago

  by kumar k

  kumar

  , India

  Joined 13 years ago

  Ok I have done a bit of research and got a bit farther with what I want to do. Here is my code so far. I have got the gridview doing what I want except for when I try to update it. I cant find a way to bind the selected value from the dropdownlist so that I can call it from the update statement. I have put in ???????? where I need to try and call a bind variable.

   Can anyone see how I can bind the dropdown to a bind variable so the update statement gets the selected value from the dropdownlist?

  <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1" AllowPaging="True" AllowSorting="True" AutoGenerateEditButton="True" DataKeyNames="pk_as_item_id">

  <Columns>

   

  <asp:BoundField ReadOnly="true" DataField="pk_as_item_id" HeaderText="ID" SortExpression="pk_as_item_id" />

   

  <asp:BoundField DataField="description" HeaderText="Description" SortExpression="description" />

   

  <asp:TemplateField HeaderText="Location" SortExpression="loca_description" >

  <EditItemTemplate>

  <asp:DropDownList ID="ddl_location" runat="server" DataSourceID="SqlDataSource2" DataValueField="pk_as_loca_id" DataTextField="loca_description" >

  </asp:DropDownList>

  </EditItemTemplate>

  <ItemTemplate>

  <asp:Label ID="lbl_location" runat="server" Text='<%# Bind("loca_description") %>'></asp:Label>

  </ItemTemplate>

  </asp:TemplateField>

   

  </Columns>

  </asp:GridView>

   

  <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Personal %>"

  SelectCommand="SELECT [pk_as_item_id], [description], [fk_as_loca_id], [pk_as_loca_id], [loca_description] FROM [ITEMS], [LOCATIONS] WHERE [fk_as_loca_id] = [pk_as_loca_id]"

  UpdateCommand="UPDATE [ITEMS] SET [pk_as_item_id] = @pk_as_item_id, [description] = @description, [fk_as_loca_id] = ???????  WHERE [pk_as_item_id] = @pk_as_item_id">

  </asp:SqlDataSource>

   

  <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:Personal %>"

  SelectCommand="SELECT [pk_as_loca_id], [loca_description] FROM [LOCATIONS]">

  </asp:SqlDataSource>

  Report abuse
• • Reply

  12 years ago

  by kumar k

  kumar

  , India

  Joined 13 years ago

  Ok so turns out im an idiot. I just had to bind it to the selectedvalue attribute. This didnt come up in the intellisense so I totally forgot about it. Shows my lack of experince in asp.net. Anyway just thought I would put this up in case anyone else had a similar problem.

  Report abuse