CODEWITHSUNDEEP
asp.netgridviewsqldatasource
Moran Talmon
Moran Talmon

Reputation: 595

display field in gridview from another sqldatasource

I am trying to implement a gridview field with data from another table, one of the fields in the table called "LocationID" is a foreign key to the Locations table. I don't want to display the number on the gridview I want to see only the address field from the Locations table and edit the value using a dropdownlist control.

this is the main table in use with the gridview and sqldatasource

Net-Items

 [ID],[Name]
,[Model]
,[SerialNumber]
,[Company]
,[PurchaseDate]
,[PurchasePrice]
,[MonthPrice]
,[CommitmentPrice]
,[Status]
,[CommitmentDate]
,[Free]
,[TillDate]
,[LocationID]

and this is the secondary table

Net-Locations

[ID]
,[ContactName]
,[Address]
,[City]

thanks

Upvotes: 2

Views: 4579

Answers (3)

Saanch
Saanch

Reputation: 1844

Please try the below code.

    <%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
    <title></title>
    </head>
    <body>
    <form id="form1" runat="server">
    <h3>Items Details</h3>
    <div>
        <asp:SqlDataSource ID="sds_Items" runat="server" 
            ConnectionString="<%$ ConnectionStrings:Local %>" 
            ProviderName="<%$ ConnectionStrings:Local.ProviderName %>" 
            SelectCommand="select * from Items"></asp:SqlDataSource>
        <asp:SqlDataSource ID="sds_Location" runat="server" 
            SelectCommand="select * from location" 
            ConnectionString="<%$ ConnectionStrings:Local %>" 
            ProviderName="<%$ ConnectionStrings:Local.ProviderName %>"></asp:SqlDataSource>
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
            DataSourceID="sds_Items" AutoGenerateEditButton="True" ShowFooter="True">
            <Columns>
            <asp:TemplateField HeaderText="ID" >
                <itemtemplate>
                    <asp:Label ID="ID" runat="server" Text='<%# Bind("ID") %>'></asp:Label>
                </itemtemplate>
                <edititemtemplate>
                    <asp:TextBox ID="txtID" runat="server" Text='<%# Bind("ID") %>' Width="98%" MaxLength="6"></asp:TextBox>
                </edititemtemplate>
                <footertemplate>
                   <asp:TextBox ID="txtNewID" runat="server" Text='<%# Bind("ID") %>' Width="98%" MaxLength="6"></asp:TextBox>
                </footertemplate>
                <itemstyle width="80px" /> 
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Location" >
                <ItemTemplate>
                    <asp:Label ID="lblLocation" runat="server" Text='<%# LookupLocation(DataBinder.Eval(Container.DataItem, "LocationID")) %>'></asp:Label> 
                </ItemTemplate>
                <EditItemTemplate>
                    <asp:DropDownList ID="ddlLocation" runat="server" DataSourceID="sds_Location" DataTextField="ID" DataValueField="ID" SelectedValue='<%#Bind("LocationID")%>' Width="98%">
                    </asp:DropDownList> 
                </EditItemTemplate>
                <FooterTemplate>
                    <asp:DropDownList ID="ddlNewLocation" runat="server" DataSourceID="sds_Location" DataTextField="Address" DataValueField="ID" SelectedValue='<%#Bind("LocationID")%>' Width="95%">
                    </asp:DropDownList> 
                </FooterTemplate>
               <ItemStyle Width="25%" /> 
           </asp:TemplateField> 
            </Columns>
        </asp:GridView>
    </div>
    </form>
    </body>
    </html>





using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Collections;
using System.Data;

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected string LookupLocation(object idObj)
    {
        if (string.IsNullOrEmpty(idObj.ToString()))
            return null;

        // store the permissionID passed as an input parameter
        string LocationId = idObj.ToString();

        // find the corresponding name
        IEnumerator enumos = sds_Location.Select(new DataSourceSelectArguments()).GetEnumerator();
        while (enumos.MoveNext())
        {
            DataRowView row = enumos.Current as DataRowView;

            if ((string)row["ID"].ToString() == LocationId)
                return string.Concat(row["Address"].ToString());
        }

        return LocationId;
    }
}

Upvotes: 4

Saanch
Saanch

Reputation: 1844

Create a separate SQL Datasource and query the Location table details what ever you want in that table.

<edititemtemplate>
    <asp:DropDownList ID="ddl_Address" runat="server" DataSourceID="sqlDS_Location" SelectedValue='<%# Bind("Address") %>' DataTextField="Address" DataValueField="Address">
    </asp:DropDownList>                          
</edititemtemplate>

Put the above code in the gridview columns.

Upvotes: 0

Kirill
Kirill

Reputation: 3088

Please consider the following tutorial on how to configure the SqlDatasource control. Especially the Step 4: Using a Custom SQL Statement or Stored Procedure.

Upvotes: 2

Related Questions