You wrote this query:
SELECT ClientName
FROM Clients
WHERE (@ColumnName = @Criteria)
it works as a stored procedure, if yes, then where you are using stored procedure name,
instead you are using QRY,
secondly, dont use direct sql query for search, because any other database expert can delete your data from search bar, just by write delete % x% in your search box.
so, try to use stored procedure and call it,
MY STORED PROCEDURE:
USE [ITAccessories]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[sp_searchUser]
@user_name nvarchar(50),
@user_code nvarchar(50),
@dept nvarchar(50)
as begin
-- sp_searchUser null, null, 'dril'
SELECT [UserID]
,[UserCode]
,[Name]
,[PFCode]
,[Email]
,[CellNo]
,[PositionID]
,d.DepartmentName
,b.BranchName
,c.CompanyName
FROM [ITAccessories].[dbo].[Users] u
LEFT outer join Departments d on u.DepartmentID = d.DepartmentID
LEFT outer join Branches b on u.BranchID = b.BranchID
LEFT outer join Companies c on c.CompanyID = b.CompanyID
where u.[Name] like '%' + @user_name +'%'
or d.DepartmentName like '%' + @dept + '%'
or u.PFCode like '%' + @user_code + '%'
end
c# code:
protected void btnsearch_Click(object sender, EventArgs e)
{
string srch = txtsearch.Text;
Repeater1.DataSource = bm.GetUserInfo(srch);
Repeater1.DataBind();
}
called function...
public DataTable GetUserInfo(string name)
{
SqlConnection conn = null;
DataTable tb = null;
SqlParameter[] Para = new SqlParameter[3];
Para[0] = new SqlParameter("@user_name", name);
Para[1] = new SqlParameter("@user_code", name);
Para[2] = new SqlParameter("@dept", name);
try
{
conn = new SqlConnection(GetConnectionString());
DataSet ds = SqlHelper.ExecuteDataset(conn, CommandType.StoredProcedure, "sp_searchUser", Para);
tb = ds.Tables[0];
return tb;
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
}
Hope u understand it, if works for you, then plz accept it as answer n assign rates.
thnx