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

时间:2022-06-02 00:25:32

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


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。

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


1  

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


0  

            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


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。

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


1  

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


0  

            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的错误,但是感谢大家的帮助。