如何参数化类似mysql的查询?

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.

有人能帮我修一下这个吗?

3 个解决方案

#1

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。

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.
不要尽可能多地使用AddWithValue。它有时可能产生意想不到的和令人惊讶的结果。使用Add method重载指定参数类型及其大小。
Use using statement to dispose your data adapter automatically.
使用using语句自动配置数据适配器。

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.

顺便说一下，你的方法中没有cmd。在方法中定义命令和连接，并使用语句处理它们。

#2

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

#3

            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.

我不知道为什么，但是这个代码是有效的，当我使用AddWithValue而不是AddWithValue时，编译器会给我错误的datetime的错误，但是感谢大家的帮助。

#1