How to search in multiple tables using one TextBox and display results in multiple GridView in ASP.Net

Hi @yunus,

As i understood from your problem definition.

1. You have three tables.
2. You have three gridviews to show up these table details.
3. You have a text box and search button to serach product from all those three tables.and it should be displayed.

I have a created a small snippet as per you requirements.

Try this..

Sql Code

CREATE PROCEDURE Products_SearchProducts
	@SearchTerm VARCHAR(100)
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @tbl TABLE
	(
		Id INT NOT NULL,
		Name VARCHAR(50) NOT NULL,
		Details VARCHAR(50) NOT NULL
	)
		INSERT INTO @tbl
		SELECT Id
			,Name
			,Details
		FROM Products_Table_A
		WHERE Name LIKE @SearchTerm + '%'

		INSERT INTO @tbl
		SELECT Id
			,Name
			,Details
		FROM Products_Table_B
		WHERE Name LIKE @SearchTerm + '%'

		INSERT INTO @tbl
		SELECT Id
			,Name
			,Details
		FROM Products_Table_C
		WHERE Name LIKE @SearchTerm + '%'


		SELECT Id
			,Name
			,Details
		FROM @tbl
END
GO

Html Code:-

 <div>
        <asp:TextBox ID="txtSearchTerm" runat="server">
        </asp:TextBox><br />
        <asp:Button Text="Search" runat="server" OnClick="Search" />
    </div>
    <div>
        <asp:GridView ID="gvProducts_First" runat="server">
        </asp:GridView>
        <br />
        <asp:GridView ID="gvProducts_Second" runat="server">
        </asp:GridView>
        <br />
        <asp:GridView ID="gvProducts_Third" runat="server">
        </asp:GridView>
    </div>
    <div>
        <asp:GridView ID="gvSearchResults" runat="server">
        </asp:GridView>
    </div>

C# Code:

  SqlConnection con;
        SqlDataAdapter da;
        DataTable dt;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!this.IsPostBack)
            {
                this.gvProducts_First.DataSource = this.GetData("SELECT Id,Name,Details FROM Products_Table_A");
                this.gvProducts_First.DataBind();
                this.gvProducts_Second.DataSource = this.GetData("SELECT Id,Name,Details FROM Products_Table_B");
                this.gvProducts_Second.DataBind();
                this.gvProducts_Third.DataSource = this.GetData("SELECT Id,Name,Details FROM Products_Table_C");
                this.gvProducts_Third.DataBind();
            }
        }

        protected void Search(object sender, EventArgs e)
        {
            con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConString"].ConnectionString);
            SqlCommand cmd = new SqlCommand("Products_SearchProducts", con);
            cmd.Parameters.AddWithValue("@SearchTerm", this.txtSearchTerm.Text.Trim());
            cmd.CommandType = CommandType.StoredProcedure;
            da = new SqlDataAdapter(cmd);
            dt = new DataTable();
            da.Fill(dt);
            this.gvSearchResults.DataSource = dt;
            this.gvSearchResults.DataBind();
        }

        private DataTable GetData(string query)
        {
            con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConString"].ConnectionString);
            da = new SqlDataAdapter(query, con);
            dt = new DataTable();
            da.Fill(dt);
            return dt;
        }

Namespaces:

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

Warm Regards:-

Hi @yunus,

as you said i have modified my sp and codes as per your need.

please have look.

SQL

ALTER PROCEDURE [dbo].[Products_SearchProducts]
	@SearchTerm VARCHAR(100)
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @tbl TABLE
	(
		Id INT NOT NULL,
		Name VARCHAR(50) NOT NULL,
		Details VARCHAR(50) NOT NULL
	)
		INSERT INTO @tbl
		SELECT Id
			,Name
			,Details
		FROM Products_Table_A
		WHERE Name LIKE @SearchTerm + '%' OR Details LIKE @SearchTerm + '%'

		INSERT INTO @tbl
		SELECT Id
			,Name
			,Details
		FROM Products_Table_B
		WHERE Name LIKE @SearchTerm + '%' OR Details LIKE @SearchTerm + '%'

		INSERT INTO @tbl
		SELECT Id
			,Name
			,Details
		FROM Products_Table_C
		WHERE Name LIKE @SearchTerm + '%' OR Details LIKE @SearchTerm + '%'
		
		SELECT Id
			,Name
			,Details
		FROM @tbl
END

Html

Page1

<div>
        <asp:TextBox ID="txtSearchTerm" runat="server"></asp:TextBox>
        <br />
        <asp:Button Text="Search" runat="server" OnClick="Search" />
    </div>
    <div>
        <asp:GridView ID="gvProducts_First" runat="server">
        </asp:GridView>
        <br />
        <asp:GridView ID="gvProducts_Second" runat="server">
        </asp:GridView>
        <br />
        <asp:GridView ID="gvProducts_Third" runat="server">
        </asp:GridView>
    </div>

Page2(SearchProduct)

 <div>
        <asp:GridView ID="gvSearchResults" runat="server">
        </asp:GridView>
    </div>

C# COding Page 1

 SqlConnection con;
        SqlDataAdapter da;
        DataTable dt;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!this.IsPostBack)
            {
                this.gvProducts_First.DataSource = this.GetData("SELECT Id,Name,Details FROM Products_Table_A");
                this.gvProducts_First.DataBind();
                this.gvProducts_Second.DataSource = this.GetData("SELECT Id,Name,Details FROM Products_Table_B");
                this.gvProducts_Second.DataBind();
                this.gvProducts_Third.DataSource = this.GetData("SELECT Id,Name,Details FROM Products_Table_C");
                this.gvProducts_Third.DataBind();
            }
        }

        protected void Search(object sender, EventArgs e)
        {
            Response.Redirect("~/SearchProduct.aspx?SearchTerm=" + this.txtSearchTerm.Text.Trim());
        }

        private DataTable GetData(string query)
        {
            con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConString"].ConnectionString);
            da = new SqlDataAdapter(query, con);
            dt = new DataTable();
            da.Fill(dt);
            return dt;
        }

C# Code Page 2

 protected void Page_Load(object sender, EventArgs e)
        {
            if (!this.IsPostBack)
            {
                string searchTerm = this.Request.QueryString["SearchTerm"];
                SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConString"].ConnectionString);
                SqlCommand cmd = new SqlCommand("Products_SearchProducts", con);
                cmd.Parameters.AddWithValue("@SearchTerm", searchTerm);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                da.Fill(dt);

                this.gvSearchResults.DataSource = dt;
                this.gvSearchResults.DataBind();
            }
        }