Getting error while search name in the textbox using bootstrap in ASP.NET C#

Question

I am using gridview for reports and it contains more than 10000 (ten thousands) records.

i am using one textbox for searching and multiple column values such as (CustNo,CustName,Department,MaterialName,Salary and DateofJoining)

All search has to be done in one single search box and filtering the record and then will be showing in gridview.

Here, i am getting the error while i'm entering the name or salary or doj..

i am using below link for reference.

https://www.aspsnippets.com/questions/165369/How-to-use-BootStrap-AutoComplete-TextBox-with-multiple-column-names-using-Bootstrap-typeahead-Plugin-in-ASPnet/

based on above i tried the below code.

stored procedure

SELECT Id,CustNo FROM View_Cust_Details
WHERE CustNo LIKE @Sp_SearchText + '%'
UNION ALL 
SELECT Id,CustName FROM View_Cust_Details
WHERE CustName LIKE @Sp_SearchText + '%'
UNION ALL 
SELECT Id,Department FROM View_Cust_Details
WHERE Department LIKE @Sp_SearchText + '%'
UNION ALL 
SELECT Id,MaterialName FROM View_Cust_Details
WHERE MaterialName LIKE @Sp_SearchText + '%'
UNION ALL 
SELECT Id,Salary FROM View_Cust_Details
WHERE Salary LIKE @Sp_SearchText + '%'
UNION ALL 
SELECT Id,DateofJoining FROM View_Cust_Details
WHERE DateofJoining LIKE @Sp_SearchText + '%'

CS.aspx

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



    


    
    
        
        
        
        
        
        
        
    
    
        Enter search term:
        
        

        
        
    
    
	
                            
                                
                                    
                                        
                                           CustNo
                                    
                                    
                                        
                                    
                                
                                 
                                    
                                        
                                           CustNo
                                    
                                    
                                        
                                    
                                
                                  
                                        
                                           Department
                                    
                                    
                                        
                                    
                                
                                
                                    
                                        
                                            MaterialName
                                    
                                    
                                        
                                    
                                
                                 
                                    
                                       
                                            Salary
                                    
                                    
                                        
                                    
                                
                                 
                                    
                                       
                                            DateOfJoining

customer.aspx.cs

using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
    [System.Web.Services.WebMethod]
    public static string[] GetSections(string prefix)
    {
        List sections = new List();
        using (SqlConnection conn = new SqlConnection())
        {
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText = "Sp_Get_Cust_Details";
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@Sp_TYPE", "SearchTxT_CustDetails");
                cmd.Parameters.AddWithValue("@Sp_SearchText", prefix);
                cmd.Connection = conn;
                conn.Open();
                using (SqlDataReader sdr = cmd.ExecuteReader())
                {
                    while (sdr.Read())
                    {
                        sections.Add(string.Format("{0}-{1}", sdr["Id"], sdr["CustNo"]));
                    }
                }
                conn.Close();
            }
        }
        return sections.ToArray();
    }
    protected void Submit(object sender, EventArgs e)
    {
        string Id = Request.Form[txtSearch.UniqueID];
        string CustNo = Request.Form[hfSectionId.UniqueID];
        //call grid and filter the grid view based on text enter search
    }
}

How to use all the fields in one single search text box and filter row in gridview based on the search..

Accepted Answer

Hi @Gani_tpt,

Here, i am getting the error while i'm entering the name or salary or doj..

Do you mean that only these fields will go wrong?

I tested your code and found several issues.

If you check your html, you will find that there is a line missing .

And the ajax url part, use the name you created when you created the aspx.

Another thing to note is that because the string type cannot be converted to the int type, you need to make a judgment.

The following is an example of my test for your reference.

CREATE TABLE [dbo].[View_Cust_Details]
(
	[Id] INT NOT NULL PRIMARY KEY, 
    [CustNo] INT NULL, 
    [CustName] NVARCHAR(50) NULL, 
    [Department] NVARCHAR(50) NULL, 
    [MaterialName] NVARCHAR(50) NULL, 
    [Salary] INT NULL, 
    [DateofJoining] NVARCHAR(50) NULL
)

CREATE PROCEDURE Sp_Get_Cust_Details
	@Sp_SearchText NVARCHAR(50)
AS
if ISNUMERIC(@Sp_SearchText)=1
    Begin
	SELECT Id,CustNo FROM View_Cust_Details
	WHERE CustNo LIKE @Sp_SearchText + '%'
	UNION ALL 
	
	SELECT Id,Salary FROM View_Cust_Details
	WHERE Salary LIKE @Sp_SearchText + '%'
    End
Else
    Begin
	SELECT Id,CustName FROM View_Cust_Details
	WHERE CustName LIKE @Sp_SearchText + '%'
	UNION ALL 
	SELECT Id,Department FROM View_Cust_Details
	WHERE Department LIKE @Sp_SearchText + '%'
	UNION ALL 
	SELECT Id,MaterialName FROM View_Cust_Details
	WHERE MaterialName LIKE @Sp_SearchText + '%'
	UNION ALL 
	SELECT Id,DateofJoining FROM View_Cust_Details
	WHERE DateofJoining LIKE @Sp_SearchText + '%'
    End   
GO

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="WebForm.WebForm1" %>



    
    
    
    
    
    
    
    


    
        
            Enter search term:
        
            

            
            
        
        
            
                
                    
                        
                            
                                CustNo
                        
                        
                            
                        
                    
                    
                        
                            
                                CustName
                        
                        
                            
                        
                    
                    
                        
                            
                                Department
                        
                        
                            
                        
                    
                    
                        
                            
                                MaterialName
                        
                        
                            
                        
                    
                    
                        
                            
                                Salary
                        
                        
                            
                        
                    
                    
                        
                            
                                DateOfJoining

protected void Page_Load(object sender, EventArgs e)
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand("SELECT * from View_Cust_Details"))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.Connection = con;
                sda.SelectCommand = cmd;
                using (DataTable dt = new DataTable())
                {
                    sda.Fill(dt);
                    ViewState["dirState"] = dt;
                    gvcustomer.DataSource = dt;
                    gvcustomer.DataBind();
                }
            }
        }
    }
}
[System.Web.Services.WebMethod]
public static string[] GetSections(string prefix)
{
    List sections = new List();
    using (SqlConnection conn = new SqlConnection())
    {
        conn.ConnectionString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlCommand cmd = new SqlCommand())
        {
            cmd.CommandText = "Sp_Get_Cust_Details";
            cmd.CommandType = CommandType.StoredProcedure;
            //cmd.Parameters.AddWithValue("@Sp_TYPE", "SearchTxT_CustDetails");
            cmd.Parameters.AddWithValue("@Sp_SearchText", prefix);
            cmd.Connection = conn;
            conn.Open();
            int i;
            bool bNum = int.TryParse(prefix, out i);
            if (bNum == true)
            {
                using (SqlDataReader sdr = cmd.ExecuteReader())
                {
                    while (sdr.Read())
                    {
                        sections.Add(string.Format("{0}-{1}", sdr["Id"], sdr["CustNo"]));
                    }
                }
            }
            else
            {
                using (SqlDataReader sdr = cmd.ExecuteReader())
                {
                    while (sdr.Read())
                    {
                        sections.Add(string.Format("{0}-{1}", sdr["Id"], sdr["CustName"]));
                    }
                }
            }
            conn.Close();
        }
    }
    return sections.ToArray();
}
protected void Submit(object sender, EventArgs e)
{
    string Bill_Id = Request.Form[txtSearch.UniqueID];
    string EmpNo = Request.Form[hfSectionId.UniqueID];            
    //call grid and filter the grid view based on text enter search
    if (ViewState["dirState"] != null)
    {
        DataTable dt = ViewState["dirState"] as DataTable;
        //make a clone of the datatable
        DataTable dtNew = dt.Clone();
        //search the datatable for the correct fields
        foreach (DataRow row in dt.Rows)
        {
            //add your own columns to be searched here
            if (row["CustNo"].ToString().Contains(Bill_Id) || row["CustName"].ToString().Contains(Bill_Id) || row["Department"].ToString().Contains(Bill_Id) || row["MaterialName"].ToString().Contains(Bill_Id) || row["Salary"].ToString().Contains(Bill_Id) || row["DateofJoining"].ToString().Contains(Bill_Id))
            {
                //when found copy the row to the cloned table
                dtNew.Rows.Add(row.ItemArray);
            }
        }
        //rebind the grid
        gvcustomer.DataSource = dtNew;
        gvcustomer.DataBind();
    }
}

Best regards,
Lan Huang

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer

your query should limit the number of rows returned. a 100 (10-20 more likely) should be more than enough.

you should set the limit in the sp, why return more rows than necessary.

Answer

There are several bugs in your source code. I'll focus on the current SQL error.

Error converting data type nvarchar to numeric.

I think you'll be interested in reading the docs so you can learn how UNION ALL works.

The definitions of the columns that are part of a UNION operation don't have to be the same, but they must be compatible through implicit conversion. When data types differ, the resulting data type is determined based on the rules for data type precedence.

Your SQL starts with CustNo which is an NVARCHAR type. The data type precedence thinks everything below is NVARCHAR and tries to implicitly convert NVARCHAR to a NUMERIC values when execution gets to the Salary column.

One simple approach is using TSQL to convert the search columns to an NVARCHAR. I used yyyy-MM-dd for the date. You'll need to figure out what date format works best for your users.

https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver16

Populate Test Data

DROP TABLE IF EXISTS View_Cust_Details;
GO

CREATE TABLE View_Cust_Details (
	Id				INT PRIMARY KEY IDENTITY(1,1),
	CustNo			NVARCHAR(50),
	CustName		NVARCHAR(50),
	Department		NVARCHAR(50),
	MaterialName	NVARCHAR(50),
	Salary			DECIMAL(18,2),
	DateOfJoining	DATE
)
GO

INSERT INTO View_Cust_Details (CustNo, CustName, Department, MaterialName, Salary, DateOfJoining)
VALUES	('A1234', 'Big Bird', 'Sesame street', 'Features', 50000.00, GETDATE()),
		('B2345', 'Oscar the grouch', 'Sesame street', 'Trash Cans', 60000.00, GETDATE()),
		('C3456', 'Bert', 'Sesame street', 'Letters', 70000.00, GETDATE());

GO

SELECT * FROM View_Cust_Details
GO

Procedure

DROP PROCEDURE IF EXISTS Sp_Get_Cust_Details;
GO

CREATE PROCEDURE Sp_Get_Cust_Details (
	@SearchText NVARCHAR(50)
)
AS
BEGIN
	SELECT	Id, 
			CustNo AS SearchText
	FROM View_Cust_Details
	WHERE CustNo LIKE '%' + @SearchText + '%'

	UNION ALL 

	SELECT	Id, 
			CustName AS SearchText
	FROM View_Cust_Details
	WHERE CustName LIKE '%' + @SearchText + '%'

	UNION ALL 

	SELECT	Id,
			Department AS SearchText
	FROM View_Cust_Details
	WHERE Department LIKE '%' + @SearchText + '%'

	UNION ALL 

	SELECT	Id,
			MaterialName  AS SearchText
	FROM View_Cust_Details
	WHERE MaterialName LIKE '%' + @SearchText + '%'

	UNION ALL 

	SELECT	Id,
			CAST(Salary AS NVARCHAR) AS SearchText 
	FROM View_Cust_Details   
	WHERE CAST(Salary AS NVARCHAR) LIKE '%' + @SearchText + '%' 

	UNION ALL

	SELECT	Id,
			CONVERT(NVARCHAR(10), DateOfJoining, 23) AS SearchText 
	FROM View_Cust_Details   
	WHERE CONVERT(NVARCHAR(10), DateOfJoining, 23) LIKE '%' + @SearchText + '%'
END
GO

Test the procedure

EXECUTE Sp_Get_Cust_Details '2025'
GO

Lastly T-SQL below highlights one of the bugs in your SQL when run in SSMS.

DECLARE @SearchText NVARCHAR(50) = '5'

SELECT	Id, 
		CustNo AS SearchText
FROM View_Cust_Details
WHERE CustNo LIKE '%' + @SearchText + '%'

UNION ALL 

SELECT	Id, 
		CustName AS SearchText
FROM View_Cust_Details
WHERE CustName LIKE '%' + @SearchText + '%'

UNION ALL 

SELECT	Id,
		Department AS SearchText
FROM View_Cust_Details
WHERE Department LIKE '%' + @SearchText + '%'

UNION ALL 

SELECT	Id,
		MaterialName  AS SearchText
FROM View_Cust_Details
WHERE MaterialName LIKE '%' + @SearchText + '%'

UNION ALL 

SELECT	Id,
		Salary AS SearchText 
FROM View_Cust_Details   
WHERE Salary LIKE '%' + @SearchText + '%' 

--UNION ALL

--SELECT	Id,
--		DateOfJoining AS SearchText 
--FROM View_Cust_Details   
--WHERE DateOfJoining LIKE '%' + @SearchText + '%'

Share via

Getting error while search name in the textbox using bootstrap in ASP.NET C#

2 additional answers

Your answer