You must Sign In to post a response.
  • Category: ASP.NET

    Combine 2 table data and fetch to Gridview dropdown list in ASP.net

    Hi,

    I have 2 table. Below are the table name. I want to match the employeeId from the both table and fetch the data to Gridview and particular one field should be in dropdown list within Gridview.

    Employee...... (EmpID, EmpName, EmpAdd)
    EmployeeSalary.....(EmpID, JobTitle, Salary)

    Result should be like below.

    EmpID EmpName, JobTitle, Salary.... these 4 fields need to be shown in gridview. Here JobTitle should be in dropdown within gridview. How can i do this.
  • #766421
    you need to use JOIN in SQL and get the values from table, SQL joins are used to combine rows from two or more tables. I think inner join will help you more
    first you need to fill all empID in gridview dropdownlist and on selected index changed event/ItemCommand event of dropdownlist/gridview you need to fire below query and get result in dataset
    see below SQL query snippet

    SELECT a.EmpID, a.EmpName, a.EmpAddFROM , b.JobTitle, b.Salary
    From Employee a,
    INNER JOIN EmployeeSalary b
    ON a.EmpID=b.EmpID;

    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #766428
    Hi

    you can go through this code



    CREATE TABLE EMPLOYEE
    (
    EmpId int primary key identity(1,1),
    EmpName varchar(50),
    EmpAdd varchar(50)
    )


    CREATE TABLE EmployeeSalary
    (
    EmpId int primary key identity(1,1),
    JobTitle varchar(50),
    Salary decimal(18,2)
    )


    SELECT A.EmpId,A.EmpName,A.EmpAdd,B.JobTitle,B.Salary FROM EMPLOYEE A ,EmployeeSalary B WHERE A.EmpId=B.EmpId

    --If you need inner table based joins use this

    SELECT A.EmpId,A.EmpName,A.EmpAdd,B.JobTitle,B.Salary FROM EMPLOYEE A INNER JOIN EmployeeSalary B ON A.EmpId=B.EmpId

    --If you need left table based joins use this

    SELECT A.EmpId,A.EmpName,A.EmpAdd,B.JobTitle,B.Salary FROM EMPLOYEE A LEFT JOIN EmployeeSalary B ON A.EmpId=B.EmpId

    --If you need right table based joins use this

    SELECT A.EmpId,A.EmpName,A.EmpAdd,B.JobTitle,B.Salary FROM EMPLOYEE A RIGHT JOIN EmployeeSalary B ON A.EmpId=B.EmpId


    Name : Dotnet Developer-2015
    Email Id :kumaraspcode2009@gmail.com

    'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

  • #766436
    Hi,

    If you want to show the result based on 2 tables data then you need to use Join condition in SQL Server, In SQL Server we have different types of joins available based on your requirement you can use respective join.

    Ex:

    SELECT E.EmpID, E.EmpName, ES.JobTitle, ES.Salary
    FROM Employee E,
    EmployeeSalary ES
    WHERE E.EmpID=ES.EmpID

    using above query you can get the employee records from database, bind all those records into gridview, coming to your second query based on each record you need to bind the JobTitle into dropdownlist against each row of gridview.

    for that you need to make one more query and pass the EMPId as input parameter,


    SELECT ES.EmpID, ES.JobTitle
    FROM EmployeeSalary ES
    WHERE ES.EmpID=@EmpID


    onrowdatabound event of gridview pass the empid as input parameter and bind the JobTitle into dropdownlist, refer below sample.


    Protected void gv_OnRowDataBound(object sender, GridViewRowEventArgs e)
    {
    if(e.Row.RowType==DataControlRowType.DataRow)
    {
    int EmpId= Convert.ToInt32(((Lable)e.Row.FindControl("lblEmpId")).Text.ToString());
    DataSet ds=//pass the EmpId as input parameter to above query and get the data from database and store it in DataSet.
    ddlJobTitle.DataSource=ds;
    ddlJobTitle.DataTextField="JobTitle";
    ddlJobTitle.DataValueField="EmpId";
    ddlJobTitle.DataBind();
    }
    }


    Try something like above to achieve your goal, hope this will helpful to you. If you need any more clarifications revert to me.

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #766450
    Hi,
    Please find demo code:

    .aspx page:
    <-asp:GridView runat="server" ID="gvDemo" AutoGenerateColumns="true" OnRowDataBound="gvDemo_RowDataBound" >
    <-Columns>
    <-asp:TemplateField>
    <-ItemTemplate>
    <-asp:DropDownList runat="server" ID="DemoDDL" AutoPostBack="false"/>
    <-/ItemTemplate>
    <-/asp:TemplateField>
    <-/Columns>
    <-/asp:GridView>
    .cs page:
    DataTable dt = new DataTable();
    protected void Page_Load(object sender, EventArgs e)
    {
    IDataReader reader = null;
    SqlConnection con = new SqlConnection("Data Source=XYZ;Initial Catalog=DBNAME;User ID=USERNAME;Password=USERPWD");
    con.Open();
    SqlDataAdapter a = new SqlDataAdapter("SELECT ES.EmpID, ES.JobTitle FROM EmployeeSalary ES WHERE ES.EmpID=@YourDesiredEmpID", con);
    a.Fill(dt);
    gvDemo.DataSource = dt;
    gvDemo.DataBind();
    }
    protected void gvDemo_RowDataBound(object sender, GridViewRowEventArgs e)
    {
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
    DropDownList ddl = (DropDownList)e.Row.FindControl("DemoDDL");
    ddl.DataSource = dt;
    ddl.DataTextField = "JobTitle";
    ddl.DataValueField = "EmpId";
    ddl.DataBind();
    }
    }

  • #766609
    Hi Gopi,

    Hi

    Can you try below query to combine two table


    Select E. Empid,E. EmpName,ES. JobTitle,ES.Salary from Employee as E inner join EmployeeSalary as ES on E.Empid=ES.EmpID

  • #766610
    Hi Gopi,

    I would go with below simple query however, Prasad and Jaikumar already provided a great and illustrated examples for your need.

    SELECT EmpID, EmpName, JobTitle, Salary from Employee, EmployeeSalary WHERE Employee.EmpID = EmployeeSalary.EmpID;

    Thanks for posting. As all answers are valid, please choose a answer as best answer and help us closing this thread.

    -------------
    Glad to be,
    John Bhatt
    Editor - DNS Forums
    https://www.pyarb.com

  • #766611
    First you can make DB call using ADO.net and get the particular records. In the Data retrieve part you can use the following SQL Query to join two table and get the result as you expect.

    Select * from
    from Employee
    inner join EmployeeSalary on Employee.Empid=EmployeeSalary.EmpID

    Now you can get the list of records. Just bind the data source in to your grid view and display all the values in your grid.

    Now your second point. for job title place the Drop down control in in the item template. Then handle the event "MyGridView_RowDataBound". Now you have to make another one DB call get the unique Job title and bind in the drop down. For this your can use the query

    select DISTINCT JobTitle from EmployeeSalary

    By Nathan
    Direction is important than speed

  • #766633
    Hi,

    You have to use the join query for combing the both tables with matched records be like given below.

    SELECT a.EmpID, a.EmpName, a.EmpAddFROM , b.JobTitle, b.Salary
    From Employee a,
    JOIN EmployeeSalary b ON a.EmpID=b.EmpID

    This query will provide the some results. You have to store the results in Dataset or DataTable in .net and have to Load the details to Gridview Be Like given below.

    Void GridViewLoad()
    {
    DataTable dt = new DataTable();
    dt.Load(EmpDetails); // EmpDetails is Results of above Query.
    GridView.DataSource = dt;
    GridView.DataBind();
    }

  • #766634

  • #766643
    Refer below code to display the data in gridview,

    private void BindGridView()
    {
    string strCon = ConfigurationManager.ConnectionStrings["CConnectionString"].ToString();
    SqlConnection conn = new SqlConnection(strCon);
    conn.Open();
    SqlDataAdapter da = null;
    DataSet ds = new DataSet();
    try
    {
    string strSql = "SELECT Emp.EmpID, Emp.EmpName, ESal.JobTitle, ESal.Salary FROM Employee Emp, EmployeeSalary ESal WHERE Emp.EmpID=ESal.EmpID";
    da = new SqlDataAdapter(strSql, conn);
    da.Fill(ds, "SampleTable");

    GridView2.DataSource = ds.Tables["SampleTable"].DefaultView;
    GridView2.DataBind();

    }
    catch (Exception ex)
    {
    lblError.Text = "Error!! <br>+" + ex.Message.ToString();
    }
    finally
    {
    ds.Dispose();
    da.Dispose();
    conn.Close();
    conn.Dispose();
    }
    }


    Regards,
    Asheej T K
    Microsoft MVP[ASP.NET/IIS]
    DotNetSpider MVM

  • #766677
    Hi All,

    Thanks for your reply. I am struggled to display the record in dropdownlist in gridview. For ex. there is more records after matching with both tables, now i want to display only one (First record) record in gridview and by displaying the other records once i select from dropdownlist.

    -----------------------------------------------------------------------------
    Regards,
    Gopi A.
    +91 9894315571
    Skype:gopi.net
    http://asaigopi-dotnet.blogspot.in/

  • #766678
    Hi

    if you need gridview bind based on Dropdownlist means try this code

    client side


    <asp:DropDownList ID="Dtp1" runat="server" AutoPostBack="true" OnSelectedIndexChanged="Dtp1_SelectedIndexChanged" ></asp:DropDownList>
    <asp:Button ID="BtnSubmit" runat="server" Text="Click" OnClick="BtnSubmit_Click" />
    <asp:GridView ID="grd1" runat="server" >
    </asp:GridView>


    This code represent for bind the EmpId in your ddl control


    SqlConnection sqlcon = new SqlConnection("Data Source=PCNAME;Initial Catalog=DBNAME;Integrated Security=True;");
    sqlcon.Open();
    SqlDataAdapter sqladp = new SqlDataAdapter("SELECT Id,EMPID FROM TblEmployee", sqlcon);
    sqladp.Fill(Dt);
    Dtp1.DataSource = Dt;
    Dtp1.DataTextField = "EMPID";
    Dtp1.DataValueField = "Id";
    Dtp1.DataBind();


    This code Represent for which one item selected in ddl corresponding data binding in gridview


    SqlConnection sqlcon = new SqlConnection("Data Source=PCNAME;Initial Catalog=DBNAME;Integrated Security=True;");
    sqlcon.Open();
    SqlDataAdapter sqladp = new SqlDataAdapter("SELECT Id,EmpName,EmpGender,EMPID,EMPIDCHAR,DeptId,DeptName from TblEmployee,Dept where EMPID=DeptID and EMPID="+ Convert.ToInt32(Dtp1.SelectedItem.Text), sqlcon);
    sqladp.Fill(Dt);
    grd1.DataSource = Dt;
    grd1.DataBind();

    Name : Dotnet Developer-2015
    Email Id :kumaraspcode2009@gmail.com

    'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

  • #766680
    Hi Gopi,

    As per my understanding you want to display the data in to gridview based on dropdown selection right?

    If that is your requirement then OnSelectedIndex changed event of gridview you can bind your gridview, while binding gridview pass the dropdown selecetedvalue as input parameter,


    Protected void ddl_SelectedIndexChanged(object sender, EventArgs e)
    {
    Bind_GridView();
    }
    Protected void Bind_GridView()
    {
    DataSet ds=//pass dropdown selected value as input parameter and get data from database.
    gv.DataSource=ds;
    gv.DataBind();
    }


    If you still have any questions please let us know.

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/


Sign In to post your comments