Filter records using Join query in ASP.Net using C# and VB.Net

Hi chetan,

Check this example. Now please take its reference and correct your code.

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

HTML

Name:
<asp:TextBox runat="server" ID="txtName" />
<br />
Country:
<asp:TextBox ID="txtCountry" runat="server" />
<br />
EmployeeID:
<asp:TextBox ID="txtId" runat="server" />
<br />
<asp:Button Text="Search" runat="server" OnClick="Search" />
<br />
<asp:GridView runat="server" ID="gvDetails" />

Namespaces

C#

using System.Configuration;
using System.Data;
using System.Data.SqlClient;

VB.Net

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient

Code

C#

protected void Search(object sender, EventArgs e)
{
    string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    string query = @"SELECT DISTINCT e.FirstName + ' ' + e.LastName Name,c.Country FROM Employees e
                    INNER JOIN Orders o ON o.EmployeeID = e.EmployeeID
                    INNER JOIN Customers c ON c.CustomerID = o.CustomerID
                    WHERE e.FirstName LIKE @Name + '%' OR c.Country = @Country AND o.EmployeeID = @Id
                    ORDER BY Name";
    SqlCommand cmd = new SqlCommand(query);
    using (SqlConnection con = new SqlConnection(conString))
    {
        using (SqlDataAdapter sda = new SqlDataAdapter())
        {
            cmd.Connection = con;
            sda.SelectCommand = cmd;
            cmd.Parameters.AddWithValue("@Name", txtName.Text.Trim());
            cmd.Parameters.AddWithValue("@Country", txtCountry.Text.Trim());
            cmd.Parameters.AddWithValue("@Id", txtId.Text.Trim());
            using (DataTable dt = new DataTable())
            {
                sda.Fill(dt);
                gvDetails.DataSource = dt;
                gvDetails.DataBind();
            }
        }
    }
}

VB.Net

Protected Sub Search(ByVal sender As Object, ByVal e As EventArgs)
    Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Dim query As String = "SELECT DISTINCT e.FirstName + ' ' + e.LastName Name,c.Country FROM Employees e"
    query += " INNER JOIN Orders o ON o.EmployeeID = e.EmployeeID"
    query += " INNER JOIN Customers c ON c.CustomerID = o.CustomerID"
    query += " WHERE e.FirstName LIKE @Name + '%' OR c.Country = @Country AND o.EmployeeID = @Id"
    query += " ORDER BY Name"
    Dim cmd As SqlCommand = New SqlCommand(query)
    Using con As SqlConnection = New SqlConnection(conString)
        Using sda As SqlDataAdapter = New SqlDataAdapter()
            cmd.Connection = con
            sda.SelectCommand = cmd
            cmd.Parameters.AddWithValue("@Name", txtName.Text.Trim())
            cmd.Parameters.AddWithValue("@Country", txtCountry.Text.Trim())
            cmd.Parameters.AddWithValue("@Id", txtId.Text.Trim())
            Using dt As DataTable = New DataTable()
                sda.Fill(dt)
                gvDetails.DataSource = dt
                gvDetails.DataBind()
            End Using
        End Using
    End Using
End Sub

Screenshot