C# MYSQL - 使用组合框和文本框搜索过滤数据 GridView

Question

您好，我正在尝试使用 combobox 和 textbox 搜索过滤器 datagridview。

我已经成功完成了，但只有在我搜索 ID 列时它才能正常工作。其他列只是崩溃显示以下消息:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'Name LIKE 'd%'' at line 1

该错误消息中的 d 字母正是我试图用来过滤搜索的字母。

有人可以帮我解决这个问题吗？

我的代码如下

string myConnection = "datasource=localhost;port=3306;username=root;password=;";
MySqlConnection conDatabase = new MySqlConnection(myConnection);
try
        {
            if (comboBoxSrchPatient.Text == "ID")
            {
                MySqlCommand cmd = new MySqlCommand("select * from clinic_inventory_system.patient WHERE ID LIKE '" + txtSearchPatient.Text + "%'", conDatabase);
                MySqlDataAdapter sda = new MySqlDataAdapter();
                sda.SelectCommand = cmd;

                DataTable dbdataset = new DataTable();
                sda.Fill(dbdataset);
                dataPatientGridView.DataSource = dbdataset;
            }
            else if (comboBoxSrchPatient.Text == "FIRST NAME")
            {
                MySqlCommand cmd = new MySqlCommand("select * from clinic_inventory_system.patient WHERE First Name LIKE '" + txtSearchPatient.Text + "%'", conDatabase);
                MySqlDataAdapter sda = new MySqlDataAdapter();
                sda.SelectCommand = cmd;

                DataTable dbdataset = new DataTable();
                sda.Fill(dbdataset);
                dataPatientGridView.DataSource = dbdataset;
            }

            else if (comboBoxSrchPatient.Text == "LAST NAME")
            {
                MySqlCommand cmd = new MySqlCommand("select * from clinic_inventory_system.patient WHERE Last Name LIKE '" + txtSearchPatient.Text + "%'", conDatabase);
                MySqlDataAdapter sda = new MySqlDataAdapter();
                sda.SelectCommand = cmd;

                DataTable dbdataset = new DataTable();
                sda.Fill(dbdataset);
                dataPatientGridView.DataSource = dbdataset;
            }

            else if (comboBoxSrchPatient.Text == "AGE")
            {
                MySqlCommand cmd = new MySqlCommand("select * from clinic_inventory_system.patient WHERE Age LIKE '" + txtSearchPatient.Text + "%'", conDatabase);
                MySqlDataAdapter sda = new MySqlDataAdapter();
                sda.SelectCommand = cmd;

                DataTable dbdataset = new DataTable();
                sda.Fill(dbdataset);
                dataPatientGridView.DataSource = dbdataset;
            }

            else if (comboBoxSrchPatient.Text == "CONTACT NUMBER")
            {
                MySqlCommand cmd = new MySqlCommand("select * from clinic_inventory_system.patient WHERE Contact Number LIKE '" + txtSearchPatient.Text + "%'", conDatabase);
                MySqlDataAdapter sda = new MySqlDataAdapter();
                sda.SelectCommand = cmd;

                DataTable dbdataset = new DataTable();
                sda.Fill(dbdataset);
                dataPatientGridView.DataSource = dbdataset;
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }

Answer 1

您的字段名称包含空格。
要在查询中使用它们，您需要将它们括在反引号 (ALT+096) 之间

MySqlCommand cmd = new MySqlCommand(@"select * from 
    clinic_inventory_system.patient WHERE `Last Name` LIKE ....";

说，尽快考虑将您的查询更改为使用参数化查询

using(MySqlCommand cmd = new MySqlCommand(@"select * from 
            clinic_inventory_system.patient 
            WHERE `First Name` LIKE @name", conDatabase);
{
    cmd.Parameters.Add("@name", MySqlDbType.VarChar).Value = txtSearchPatient.Text + "%";
    MySqlDataAdapter sda = new MySqlDataAdapter();
    sda.SelectCommand = cmd;
    DataTable dbdataset = new DataTable();
    sda.Fill(dbdataset);
    dataPatientGridView.DataSource = dbdataset;
}

通过这种方式，您的代码更安全，因为不再可能针对您的数据库构建 Sql 注入(inject)攻击，并且如果 First Name 包含单引号，则您没有语法再次出错