c#中存在SQL语法错误

本文关键字:语法 错误 SQL 存在 | 更新日期: 2023-09-27 18:28:51

我正在编写一个脚本,以便在错误跟踪系统中添加错误报告。单击提交按钮后,SQL语法错误对话框将弹出。

这是我的编码

public partial class AddBugForm : Form
{
    public AddBugForm()
    {
        InitializeComponent();
       Fillcombo();
       Fillcombo1();
       Fillcombo2();
    }
    void Fillcombo()
    {
        string constring = "datasource = localhost; username = root; password = ";
        string Query = "select * from bug.type";
        MySqlConnection conDataBase = new MySqlConnection(constring);
        MySqlCommand cmdDataBase = new MySqlCommand(Query, conDataBase);
       MySqlDataReader myReader;
        try
        {
            conDataBase.Open();
            myReader = cmdDataBase.ExecuteReader();
            while (myReader.Read())
            {
                string type = myReader.GetString("Type_of_bug");
                comboBox1.Items.Add(type);
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
     }
      void Fillcombo1()
       {
           string constring1 = "datasource = localhost; username = root; password = ";
           string Query1 = "select * from bug.severity";
           MySqlConnection conDataBase1 = new MySqlConnection(constring1);
           MySqlCommand cmdDataBase1 = new MySqlCommand(Query1, conDataBase1);
           MySqlDataReader myReader;
           try
           {
               conDataBase1.Open();
               myReader = cmdDataBase1.ExecuteReader();
               while (myReader.Read())
               {
                   string severity = myReader.GetString("severity");
                   severity_combo.Items.Add(severity);
               }
           }
           catch (Exception ex)
           {
               MessageBox.Show(ex.Message);
           }
       }
    void Fillcombo2()
    {
        string constring2 = "datasource = localhost; username = root; password = ";
        string Query2 = "select * from bug.priority";
        MySqlConnection conDataBase2 = new MySqlConnection(constring2);
        MySqlCommand cmdDataBase2 = new MySqlCommand(Query2, conDataBase2);
        MySqlDataReader myReader;
        try
        {
            conDataBase2.Open();
            myReader = cmdDataBase2.ExecuteReader();
            while (myReader.Read())
            {
                string priority = myReader.GetString("priority");
                priority_combo.Items.Add(priority);
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
    }
    private void submit_button_Click(object sender, EventArgs e)
    {
        string constring = "datasource=localhost;username=root;password=";
        string Query = "INSERT INTO 'bug.bug' (Bug_ID, title, Type_of_bug, software, software_version, description, step_to_reproduction, severity, priority, symptom) values('" + this.bugid_txt.Text+"', '" + this.title_txt.Text + "','" + this.comboBox1.Text + "','" + this.software_txt.Text + "','" + this.software_version_txt.Text + "','" + this.description_txt.Text + "','" + this.step_to_reproduction_txt.Text + "','" + this.severity_combo.Text + "','" + this.priority_combo.Text + "','" + this.symptom_txt.Text + "');";
        MySqlConnection conDataBase = new MySqlConnection(constring);
        MySqlCommand cmdDataBase = new MySqlCommand(Query, conDataBase);
        MySqlDataReader myReader;
        try
        {
            conDataBase.Open();
            myReader = cmdDataBase.ExecuteReader();
            MessageBox.Show("Saved");
            while(myReader.Read())
            {
            }
        }catch(Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
    }
}

}

请帮我:(((

c#中存在SQL语法错误

我在INSERT查询中看到两个语法错误上下文问题

首先是INSERT INTO 'bug.bug';去掉那些单引号,否则它是一个文字值,而不是表名。应该是INSERT INTO bug.bug

其次,从最后一个查询语句中删除分号

.... + this.symptom_txt.Text + "');";
                                  ^.... this semicolon

用替换此INSERT INTO 'bug.bug'

INSERT INTO `bug.bug`

您的表名以字符串开头,mysql引擎看不到该表。

您遇到的语法错误是什么?

关于插入语句的几点。

  • 您不应该通过组合值字符串来构建SQL命令字符串,这可能会造成SQL注入问题,并容易导致语法错误。相反,您应该使用参数。参数还使语法简单得多。

  • 您应该使用ExecuteNonQuery命令而不是Reader,因为Insert语句没有读取任何数据

更新的语句(只有两个值用于使其变小):

string Query = "INSERT INTO bug.bug (Bug_ID, title) values (@id, @title)"
    MySqlConnection conDataBase = new MySqlConnection (constring);
    MySqlCommand cmdDataBase = new MySqlCommand (Query, conDataBase);
    cmdDataBase.Parameters.AddWithValue ("@id", bugid_txt.Text)
    cmdDataBase.Parameters.AddWithValue ("@title", title_txt.Text)
    conDataBase.Open();
    cmdDataBase.ExecuteNonQuerty ();
    MessageBox.Show("Saved");

使用Parameters可能会解决语法错误。