How to parameterize mysql like query?

Question

This is my code but it gives me a fatal error encountered during execution

private void recregtxt_TextChanged(object sender, EventArgs e)
{
        if (recregcmb.Text == "Student ID")
        {
            MySqlDataAdapter sda = new MySqlDataAdapter("select StudID, LastName, FirstName, MiddleInitial, Address, Age, Birthday, Gender, Guardian, ContactNumber as 'Contact Number', Year as 'Year Level' from registeredTBL where StudID LIKE @key", conn);
            DataTable data = new DataTable();
            sda.Fill(data);
            dataGridView2.DataSource = data;

            cmd.Parameters.AddWithValue("@key", recregtxt.Text + "%");
        }
}

Can anyone help me fix this please.

Soner Gönül · Answer 1 · 2016-02-22T08:43:42.273

3

Because you try to add your parameter name and it's value after you execute it with your data adapter. You should add if before you execute it.

if (recregcmb.Text == "Student ID")
{
    MySqlDataAdapter sda = new MySqlDataAdapter("select StudID, LastName, FirstName, MiddleInitial, Address, Age, Birthday, Gender, Guardian, ContactNumber as 'Contact Number', Year as 'Year Level' from registeredTBL where StudID LIKE @key", conn);
    cmd.Parameters.AddWithValue("@key", recregtxt.Text + "%");
    DataTable data = new DataTable();
    sda.Fill(data);
    dataGridView2.DataSource = data;
}

A few things more;

Don't use AddWithValue as much as you can. It may generate unexpected and surprising results sometimes. Use Add method overload to specify your parameter type and it's size.
Use using statement to dispose your data adapter automatically.

By the way, there is no cmd in your method. Define your command and connection in your method with disposing them using statement as well.

edited Feb 22 '16 at 08:43

answered Feb 22 '16 at 07:25

Soner Gönül

97,193
102
206
364

1

`A few things more;` makes your answer perfect...! – sujith karivelil Feb 22 '16 at 07:33
Now I get a NullReferenceException error in line of parameter. Why is it happening? – eilraK Feb 22 '16 at 08:24
@AdrianRamos You still use `AddWithValue` or you changed it to `Add`? Can you please show the relevant line? Debug your code and see what is `null`. You can check: [What is a `NullReferenceException` and how do I fix it?](http://stackoverflow.com/q/4660142/447156) as well. – Soner Gönül Feb 22 '16 at 08:28
Both Add and AddWithValue give me the same error at this line cmd.Parameters.AddWithValue("@key", recregtxt.Text + "%"); – eilraK Feb 22 '16 at 08:41
@AdrianRamos Because there is no `cmd` in your method and it probably comes as `null` if you define it out of your scope. Define both connection and command in your method instead and you will be fine. – Soner Gönül Feb 22 '16 at 08:44
I moved the connection and command code from outside to inside my method but now it gives me 'use of unassigned local variable' error. – eilraK Feb 22 '16 at 09:05
@AdrianRamos Initialize your connection and command like `var conn = new MySqlConnection(yourConString)` and `var cmd = conn.CreateCommand()`. – Soner Gönül Feb 22 '16 at 09:12

sujith karivelil · Answer 2 · 2016-02-22T07:32:53.600

What is wrong with your code:

You are almost there but You are executing the query without adding the parameter value, and adding the parameter value after the execution of the command:

What you have to do:

Add parameter value before executing the query, So you snippet will be like the following:

   if (recregcmb.Text == "Student ID")
        {
            MySqlDataAdapter sda = new MySqlDataAdapter("select StudID, LastName, FirstName, MiddleInitial, Address, Age, Birthday, Gender, Guardian, ContactNumber as 'Contact Number', Year as 'Year Level' from registeredTBL where StudID LIKE @key", conn);
            cmd.Parameters.Add("@key", SqlDbType.VarChar).Value = recregtxt.Text + "%";
            DataTable data = new DataTable();
            sda.Fill(data);
            dataGridView2.DataSource = data;                
        }

score 0 · Answer 3 · answered Feb 22 '16 at 09:40

            conn.Open();
            cmd = conn.CreateCommand();
            cmd.CommandText = "select StudID, LastName, FirstName, MiddleInitial, Address, Age, Birthday, Gender, Guardian, ContactNumber as 'Contact Number', Year as 'Year Level' from registeredTBL where StudID LIKE @key;";
            MySqlDataAdapter sda = new MySqlDataAdapter(cmd);
            cmd.Parameters.AddWithValue("@key", recregtxt.Text + "%");
            DataTable data = new DataTable();
            sda.Fill(data);
            dataGridView2.DataSource = data;

I don't know why but this code worked, when I use Add instead of AddWithValue, the compiler gives me error about invalid datetime something but thank you all for helping.

How to parameterize mysql like query?

3 Answers3