CODEWITHSUNDEEP
c#mysqlwinformsparameterssql-like
eilraK
eilraK

Reputation: 37

How to parameterize mysql like query?

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.

Upvotes: 0

Views: 647

Answers (3)

eilraK
eilraK

Reputation: 37

            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.

Upvotes: 0

Soner Gönül
Soner Gönül

Reputation: 98868

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;

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.

Upvotes: 3

sujith karivelil
sujith karivelil

Reputation: 29036

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

Upvotes: 1

Related Questions