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 · Accepted Answer · 2016-02-22 08:43:42Z

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, 2016 at 8:43

answered Feb 22, 2016 at 7:25

Soner Gönül

99.1k103 gold badges224 silver badges375 bronze badges

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

7 Comments

sujith karivelil Over a year ago

A few things more; makes your answer perfect...!

eilraK Over a year ago

Now I get a NullReferenceException error in line of parameter. Why is it happening?

Soner Gönül Over a year ago

@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? as well.

eilraK Over a year ago

Both Add and AddWithValue give me the same error at this line cmd.Parameters.AddWithValue("@key", recregtxt.Text + "%");

Soner Gönül Over a year ago

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

|

sujith karivelil · Accepted Answer · 2016-02-22 07:32:53Z

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;                
        }

eilraK · Accepted Answer · 2016-02-22 09:40:46Z

            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.

Collectives™ on Stack Overflow

How to parameterize mysql like query?

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related