CODEWITHSUNDEEP
c#mysqlsyntax-error
Calvin Leung
Calvin Leung

Reputation: 31

Having SQL Syntax error in c#

I'm writing a script to add a bug report in the bug tracking system. While after clicking the submit button, the SQL syntax error dialog have been pop-up.

Here is my coding

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

}

Please help me :((((

Upvotes: 1

Views: 104

Answers (3)

pcowan
pcowan

Reputation: 39

What is the syntax error you are getting?

Couple of points regarding the Insert statement.

  • You should not build the SQL command string by combining the value strings, this can create SQL injection problems and easily cause syntax errors. Instead you should use Parameters. Parameters also make the syntax a lot simpler.

  • You should use the ExecuteNonQuery command instead of a Reader, as the Insert statement is not reading any data

Updated statement (only two values used to make it smaller):

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");

Using Parameters will probably solve your syntax error.

Upvotes: 0

Gouda Elalfy
Gouda Elalfy

Reputation: 7023

replace this INSERT INTO 'bug.bug' by 

INSERT INTO `bug.bug`

your table name is tarted as string and mysql engine doesn't see the table.

Upvotes: 0

Rahul
Rahul

Reputation: 77926

I see two issues with context of syntax error in your INSERT query

first, INSERT INTO 'bug.bug'; remove those single quotes else it's a literal value and not table name. It should be INSERT INTO bug.bug

Second, remove the semicolon from last of your query statement

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

Upvotes: 2

Related Questions