如何从数据库中搜索两个文本框并在gridview上显示 [英] How to search on two text boxes from a database and display on gridview
问题描述
我在asp页面上有两个文本框和一个搜索按钮。用户可以通过在任何搜索框中输入文本进行搜索。我正在使用gridview与项目模板并绑定数据集。请告诉我如何按文本框搜索数据。例如:用户可以通过在所有2个文本框或3个文本框中输入数据进行搜索。请为我提供asp.net的任何代码或建议c#
我尝试过:
I have 2 text boxes on an asp page and one search button. Users can search by entering text in any of the search boxes. I am using gridview with item template and bind with dataset. please tell me how to search data by textbox. for ex: user can search by entering data in all 2 text boxes or 3 text boxes. please provide me any code or suggestion for asp.net c#
What I have tried:
protected void SearchButton_Click(object sender, EventArgs e)
{
string str = "Select [ITEM No#], [Company Name], [Discipline Required], [Service Description], Institution, [Award Date] from PSP_Report where ([ITEM NO#] like '%' + @search + '%' [Company Name] like '%' + @search1 + '%')";
SqlCommand xp = new SqlCommand(str, con);
xp.Parameters.Add("@search", SqlDbType.NVarChar).Value = RefNo.Text;
xp.Parameters.Add("@search1", SqlDbType.NVarChar).Value = RefNo.Text;
con.Open();
xp.ExecuteNonQuery();
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = xp;
DataSet ds = new DataSet();
da.Fill(ds, "ITEM No#");
Search.DataSource = ds;
Search.DataBind();
con.Close();
}
推荐答案
如果我这样做,那么我会有一些这样的代码:
If I was doing this then I'd have some code like this:
protected void Search_Click(object sender, EventArgs e)
{
var constr = ConfigurationManager.ConnectionStrings["ConnectToDB"].ConnectionString;
var sql = new StringBuilder();
sql.Append("Select [ITEM No#], [Company Name], [Discipline Required], [Service Description], Institution, [Award Date]");
sql.Append(" from PSP_Report ");
using (var con = new SqlConnection(constr))
{
using (var xp = new SqlCommand())
{
xp.Connection = con;
var andRequired = false;
var whereAdded = false;
AddASearchItem(ref whereAdded, ref andRequired, sql, Search1.Text, "[ITEM NO#]", 1, xp);
AddASearchItem(ref whereAdded, ref andRequired, sql, Search2.Text, "[Company Name]", 2, xp);
AddASearchItem(ref whereAdded, ref andRequired, sql, Search3.Text, "[Discipline Required]", 3, xp);
xp.CommandText = sql.ToString();
con.Open();
var da = new SqlDataAdapter {SelectCommand = xp};
var ds = new DataSet();
da.Fill(ds);
GridView1.DataSource = ds.Tables[0];
GridView1.DataBind();
con.Close();
}
}
}
protected void AddASearchItem(ref bool whereAdded, ref bool andRequired, StringBuilder s,
string searchText, string colName, int item, SqlCommand cmd)
{
//Add a search item to the string builder determining whether or not the words 'WHERE' or 'AND'
//need to be added. Add an appropriate parameter to the sql command.
if (string.IsNullOrEmpty(searchText)) return;
if (!whereAdded)
{
s.Append(" where ");
whereAdded = true;
}
else
if (andRequired) s.Append(" AND");
s.Append(string.Format(" {0} LIKE @search{1}", colName,item));
cmd.Parameters.AddWithValue("@search" + item.ToString(), "%" + searchText + "%");
andRequired = true;
}
注意事项:
1 - 我用StringBuilder动态构建sql命令文本。这比连接字符串要好(因为字符串是不可变的)
2 - 我有一个小函数,只有在相关的搜索文本框中有内容时才会将搜索项添加到SQL上。
3 - 如果需要将WHERE或AND添加到SQL中,我需要解决这个问题。
4 - 如果搜索框中没有任何内容,则会返回所有记录
Points to note:
1 - I've used a StringBuilder to dynamically build up the sql command text. This is better than concatenating strings (because strings are immutable)
2 - I have a little function that only adds search items onto the SQL if the relevent search textbox has something in it.
3 - I need to work out if the words " WHERE " or " AND " need to be added to the SQL.
4 - If none of the search boxes have anything in them then all records will be returned
这篇关于如何从数据库中搜索两个文本框并在gridview上显示的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!