Search mysql database, display in datagridview

Question

0.00/5 (No votes)

See more:

Hi.

I need to search mysql table and display results in datagridview.
My code so far:

C#

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:

C#

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

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...

C#

"SELECT * FROM podatki WHERE id LIKE '%" + valueToSearch + "%'";

Thx, for your help.

Posted 28-Dec-15 2:45am

Member 12232207

Updated 28-Dec-15 4:06am

v5

Add a Solution

Comments

OriginalGriff 28-Dec-15 9:03am

Um.
MySqlDataAdapter.Fill has an overload which accepts a DataTable instead of a DataSet, and using absolute numbers without checking the collection contains enough items is not a good idea...
If you only want a single table of values, then Filling a DataTable is a better idea than a DataSet.

OriginalGriff 28-Dec-15 9:01am

"I cant get it to work" is not very helpful - what does it do that you didn;t expect, or not do that you did? Any error message? Any display?

Remember that we can't see your screen, access your HDD, or read your mind.
Use the "Improve question" widget to edit your question and provide better information.

Member 12232207 28-Dec-15 9:12am

updated

sudevsu 28-Dec-15 9:17am

I think datagridview is from windows application. In that case, you will have fill the grid by each row. If its a web application, most of the time we use Gridview in this case assigning the Datasource to Gridview works.
also Please use parameterized query. DO NOT PASS the value in the Query WITHOUT Parameters. Capitals may annoy you but you will thank me later if you know about it

Member 12232207 28-Dec-15 9:20am

i have data in datagridview, but i need to search all the data not just one row and display if value is same.

ZurdoDev 28-Dec-15 9:21am

The error is clear. I don't know mySql but I doubt you are supposed to put single quotes around the table name.

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2015-12-28T03:21:00

Try:

SQL

"SELECT * FROM podatki CONCAT(id) LIKE '%" + valueToSearch + "%'"

Or use the "backtick" character:

SQL

"SELECT * FROM `podatki` CONCAT(`id`) LIKE '%" + valueToSearch + "%'"

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.

C#

string searchQuery = "SELECT * FROM podatki WHERE id LIKE '%' + @VL + '%'";
MySqlCommand command = new MySqlCommand(searchQuery, con);
command.Parameters.AddWithValue("@VL", valueToSearch);

Might work better...

sudevsu · Answer 2 · 2015-12-28T03:23:00

For the comments I made above ... Here are the solutions

For each row in the datatable

VB

  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

C#

Dim cm As New iDB2Command("SELECT * FROM TABLE Where Column=@PrmColumnval", cn)
 cm.Parameters.Add("@PrmColumnval", iDB2DbType.iDB2VarChar).Value = Value

You can convert these to C#.

Search mysql database, display in datagridview

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Search mysql database, display in datagridview

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us