文本框上的搜索查询仅显示datagridview上的空白行。 [英] Search query on textbox only shows blank rows on datagridview.

问题描述

下面的图片。



数据加载

 public void LoadData（） 
 {
 SqlConnection con = new SqlConnection（Data Source = GH0ST; Initial Catalog = InventoryProjectDB; Integrated Security = True）; 
 SqlDataAdapter sda = new SqlDataAdapter（@SELECT * FROM [dbo]。[PRODUCT]，con）; 
 DataTable dt = new DataTable（）; 
 sda.Fill（dt）; 
 dataGridView1.Rows.Clear（）; 
 foreach（dt.Rows中的DataRow项目）
 {
 int ar = dataGridView1.Rows.Add（）; 
 dataGridView1.Rows [ar] .Cells [0] .Value = item [PID]。ToString（）; 
 dataGridView1.Rows [ar] .Cells [1] .Value = item [PName]。ToString（）; 
 dataGridView1.Rows [ar] .Cells [2] .Value = item [PPrice]。ToString（）; 
 if（（bool）item [PStatus]）
 {
 dataGridView1.Rows [ar] .Cells [3] .Value =In Stock; 
} 
其他
 {
 dataGridView1.Rows [ar] .Cells [3] .Value =缺货; 
} 
 dataGridView1.Rows [ar] .Cells [4] .Value = item [PQTY]。ToString（）; 
 dataGridView1.Rows [ar] .Cells [5] .Value = item [PDateAdded]。ToString（）; 
} 
} 
 
 private void MAIN_Load（object sender，EventArgs e）
 {
 
 DateTimePicker1.Value = DateTime.Now; 
 cbx_Status.SelectedIndex = 0; 
 LoadData（）; 
 
} 

TextChanged代码

< pre lang =c＃> private void bx_Search_TextChanged（ object sender，EventArgs e）
{
SqlConnection con = new SqlConnection（ 数据源= GH0ST;初始目录= InventoryProjectDB;集成安全性=真）;
con.Open（）;
SqlDataAdapter sda = new SqlDataAdapter（ @ SELECT * FROM [dbo]。[PRODUCT] PNAME LIKE'％ + bx_Search.Text + ％'，con）;
DataTable dt = new DataTable（）;
sda.Fill（dt）;
dataGridView1.DataSource = dt;
con.Close（）;
}

查询前：

[ IMG01 ]

查询后：

[ IMG02 ]



我尝试了什么：



我是卡住了，我真的需要完成这个项目。

解决方案

有时将 .DataSource 设置为 Null 首先：

 dataGridView1.DataSource = null; 

另见示例： DatGridView过滤用户控制 [ ^ ]

一些提示：



1.

看看 LoadData 和 bx_Search_TextChanged 例程。在两者中你创建一个连接，命令等。

编程的主要规则是： 如果一段代码被使用了两次（或更多） - 更改它进入子程序！



2.

永远不要使用串联字符串作为查询！这是 SQL注入的常见原因[ ^ ]。最好的方法是使用参数化查询 [ ^ ]。



3 。

使用DataTable的计算列将比较结果PStatus改为true / false。



最后，你的

  public   void  LoadData（ string  search =   *）
 {
 SqlConnection con =  new  SqlConnection（ 数据源= GH0ST;初始目录= InventoryProjectDB;集成安全性=真）; 
  string  sql =   SELECT * FROM [ DBO] [产物]。 
  if （search！=  string  .Empty&& search！=   *）sql =  string  .Format（< span class =code-string>  {0} WHERE PName赞'％{1}％'，sql，search）; 
 SqlDataAdapter sda =  new  SqlDataAdapter（sql，con）; 
 DataTable dt =  new  DataTable（）; 
 sda.Fill（dt）; 
  //  添加表达式列！ 
 DataColumn expressionColumn =  new  DataColumn（  Stock， typeof （ string ），  IIf（PStatus = true，'有库存'，'缺货'））; 
 dt.Columns.Add（expressionColumn）; 
 expressionColumn.SetOrdinal（ 4 ）; 
 dataGridView1.DataSource = dt; 
} 
} 
 
  private   void  bx_Search_TextChanged（  object  sender，EventArgs e）
 {
 LoadData（bx_Search.Text）
} 

详情请见：

创建表达式列| Microsoft Docs [ ^ ]

DataColumn.Expression属性 [ ^ ]

PICTURE BELOW.

Data Loaded

public void LoadData()    
		{
			SqlConnection con = new SqlConnection("Data Source=GH0ST;Initial Catalog=InventoryProjectDB;Integrated Security=True");
			SqlDataAdapter sda = new SqlDataAdapter(@"SELECT * FROM [dbo].[PRODUCT]", con);
			DataTable dt = new DataTable();
			sda.Fill(dt);
			dataGridView1.Rows.Clear();
			foreach (DataRow item in dt.Rows)
			{
				int ar = dataGridView1.Rows.Add();
				dataGridView1.Rows[ar].Cells[0].Value = item["PID"].ToString();
				dataGridView1.Rows[ar].Cells[1].Value = item["PName"].ToString();
				dataGridView1.Rows[ar].Cells[2].Value = item["PPrice"].ToString();
				if ((bool)item["PStatus"])
				{
					dataGridView1.Rows[ar].Cells[3].Value = "In Stock";
				}
				else
				{
					dataGridView1.Rows[ar].Cells[3].Value = "Out of Stock";
				}
				dataGridView1.Rows[ar].Cells[4].Value = item["PQTY"].ToString();
				dataGridView1.Rows[ar].Cells[5].Value = item["PDateAdded"].ToString();
			}
		}

		private void MAIN_Load(object sender, EventArgs e)
		{
			
			DateTimePicker1.Value = DateTime.Now;
			cbx_Status.SelectedIndex = 0;
			LoadData();

		}

TextChanged code

private void bx_Search_TextChanged(object sender, EventArgs e)
		{
			SqlConnection con = new SqlConnection("Data Source=GH0ST;Initial Catalog=InventoryProjectDB;Integrated Security=True");
			con.Open();
			SqlDataAdapter sda = new SqlDataAdapter(@"SELECT * FROM [dbo].[PRODUCT] WHERE PNAME LIKE '%" + bx_Search.Text + "%' ", con);
			DataTable dt = new DataTable();
			sda.Fill(dt);
			dataGridView1.DataSource = dt;
			con.Close();
}

Before query:
[IMG01]
After query:
[IMG02]

What I have tried:

I am stuck and i really need to finish this project.

解决方案

Sometimes it helps to set the .DataSource to Null first:

dataGridView1.DataSource = null;

Also see example here: DatGridView Filtering User Control[^]

Few tips:

1.
Take a look at LoadData and on bx_Search_TextChanged routine. In both you create a connection, command, etc.
The main rule of programming is: if a piece of code is used twice (or more) - change it into subroutine!

2.
Never use concatened string as a query! This is a common reason of SQL Injection[^]. The best way is to use parameterized queries[^].

3.
Use computed column of DataTable to get result of comparison "PStatus" to true/false.

Finally, your

public void LoadData(string search="*")    
{
    SqlConnection con = new SqlConnection("Data Source=GH0ST;Initial Catalog=InventoryProjectDB;Integrated Security=True");
    string sql = "SELECT * FROM [dbo].[PRODUCT]";
    if(search!=string.Empty && search!="*") sql = string.Format("{0} WHERE PName Like '%{1}%'", sql, search);
    SqlDataAdapter sda = new SqlDataAdapter(sql, con);
    DataTable dt = new DataTable();
    sda.Fill(dt);
    //add expression column!
    DataColumn expressionColumn = new DataColumn("Stock", typeof(string), "IIf(PStatus=true,'In Stock','Out of Stock')");
    dt.Columns.Add(expressionColumn);
    expressionColumn.SetOrdinal(4);
    dataGridView1.DataSource = dt;
    }
}

private void bx_Search_TextChanged(object sender, EventArgs e)
{
    LoadData(bx_Search.Text)
}

For further details, please see:
Creating Expression Columns | Microsoft Docs[^]
DataColumn.Expression Property [^]

这篇关于文本框上的搜索查询仅显示datagridview上的空白行。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！