MySQL Error While Trying To Display A Table On a DataGrid

Question

This is the code I'm using:

private void btn_search_Click(object sender, EventArgs e)
{
    try
    {
        MySqlConnection con = new MySqlConnection(@"Data Source=localhost;port=3306;Initial Catalog=dp;User Id=root;password=''");
        con.Open();
        DataTable dt = new DataTable();
        MySqlDataAdapter SDA = new MySqlDataAdapter("SELECT * FROM dp WHERE id LIKE " + txt_id.Text, con);
        SDA.Fill(dt);
        dataGridView1.DataSource = dt;
    }
    catch (MySqlException ex)
    {
        Console.WriteLine("StackTrace:" + ex.StackTrace);
    }

Problem is, It throws a MySQL exception,

Exception thrown: 'MySql.Data.MySqlClient.MySqlException' in MySql.Data.dll StackTrace: at MySql.Data.MySqlClient.MySqlStream.ReadPacket() at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId) at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId) at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force) at MySql.Data.MySqlClient.MySqlDataReader.NextResult() at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) at MySql.Data.MySqlClient.MySqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable) at Dispatching_Software.SearchUsers.btn_search_Click(Object sender, EventArgs e) in C:\Users\Owner\documents\visual studio 2015\Projects\Dispatching Software\Dispatching Software\SearchUsers.cs:line 34

What I'm trying to do is search for users within a table and display them, The problem seems to be SDA.Fill(dt);

You can't use LIKE like that. Try using: "SELECT * FROM dp WHERE id =" + txt_id.Text — Koby Douek
– Koby Douek, Commented May 15, 2017 at 5:41

Salah Akbari · Accepted Answer · 2017-05-15 08:56:03Z

2

You don't need to use con.Open(); when you are using MySqlDataAdapter. Also you should always use parameterized queries to avoid SQL Injection:

MySqlDataAdapter SDA = new MySqlDataAdapter("SELECT * FROM dp WHERE id LIKE '%' + @a + '%'", con);
SDA.SelectCommand.Parameters.AddWithValue("@a", txt_id.Text);

Although specify the type directly and use the Value property is more better than AddWithValue:

 MySqlDataAdapter SDA = new MySqlDataAdapter("SELECT * FROM dp WHERE id LIKE @a", con);
SDA.SelectCommand.Parameters.Add("@a", MySqlDbType.VarChar, 200).Value = "%" + txt_id.Text;

Can we stop using AddWithValue() already?

edited May 15, 2017 at 8:56

answered May 15, 2017 at 5:42

Salah Akbari

40k10 gold badges85 silver badges115 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

JordieDevs Over a year ago

Thank you for the response, I have been looking into AntiMySQL injection, I believe this will help me alot with this project, Thanks.

Aluan Haddad Over a year ago

You might add that in addition to not using con.Open(), that the MySqlConnection instance should be wrapped in a using statement.

Collectives™ on Stack Overflow

MySQL Error While Trying To Display A Table On a DataGrid

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related