搜索mysql数据库,在datagridview中显示 [英] Search mysql database, display in datagridview
问题描述
嗨。
我需要搜索mysql表并在datagridview中显示结果。
我的代码到目前为止:
Hi.
I need to search mysql table and display results in datagridview.
My code so far:
MySqlConnection con = new MySqlConnection("server=********;database=t1;user=*****;password=*******");
string searchQuery = "SELECT * FROM 'podatki' CONCAT('id') LIKE '%" + valueToSearch + "%'";
MySqlCommand command = new MySqlCommand(searchQuery, con);
MySqlDataAdapter adapter = new MySqlDataAdapter(command);
DataTable table = new DataTable();
adapter.Fill(table);
dataGridView1.DataSource = table;
错误信息:
error message:
Additional information: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''podatki' CONCAT('id') LIKE '%123%'' at line 1
我不能让它工作。有没有办法做到这一点?
这样可行,但我需要更多行而不仅仅是id。
示例:id,name,姓氏...
I cant get it to work. is there any way to do this?
This works, but i need more rows not just id.
Example: id,name,surname...
"SELECT * FROM podatki WHERE id LIKE '%" + valueToSearch + "%'";
Thx,求助。
Thx, for your help.
推荐答案
尝试:
Try:
"SELECT * FROM podatki CONCAT(id) LIKE '%" + valueToSearch + "%'"
或使用反引号字符:
Or use the "backtick" character:
"SELECT * FROM `podatki` CONCAT(`id`) LIKE '%" + valueToSearch + "%'"
但是我很喜欢你不应该这样做:不要连接字符串来构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度,这可能会破坏您的整个数据库。请改用参数化查询。
But I'd stringlyy suggest that you shouldn't do it like that: Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.
string searchQuery = "SELECT * FROM podatki WHERE id LIKE '%' + @VL + '%'";
MySqlCommand command = new MySqlCommand(searchQuery, con);
command.Parameters.AddWithValue("@VL", valueToSearch);
可能工作得更好......
Might work better...
对于我上面的评论......以下是解决方案
对于数据表中的每一行
For the comments I made above ... Here are the solutions
For each row in the datatable
For Each dr As DataRow In dtnew.Rows
DataGridView1.Rows.Add(dr("col1"), dr("Col2"), dr("Col3"), dr("Col4"), dr("Col5"), dr("Col6"), dr("Col7"), dr("Col8"))
Next
参数化查询如下所示
Parameterized Query is something like below
Dim cm As New iDB2Command("SELECT * FROM TABLE Where Column=@PrmColumnval", cn)
cm.Parameters.Add("@PrmColumnval", iDB2DbType.iDB2VarChar).Value = Value
您可以将这些转换为C#。
You can convert these to C#.
这篇关于搜索mysql数据库,在datagridview中显示的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!