CODEWITHSUNDEEP
c#mysql.net
Putte
Putte

Reputation: 328

Syntax error near: When connecting to mysql database

I've read on a lot of threads, but none of them is actually working, thats why I'm asking a new question.

Well, so I'm trying to insert values into my MySQL database but I'm getting the error.

MySql.Data.MySqlClient.MySqlException: 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Desc, Detectors, DetectorNos, Question1, Question2, SpecialPrec, OfficerSign, Of' at line 1'

And I can't find where the problem is. I've been trying for hours without any result, even read all throught a hundred of times but still no luck. So I need someone else to take a look.

 public string detector = "";
    public string questions = "";
    public string question2 = "";
    public string capOrCheif = "";

    private void btn_send_Click(object sender, EventArgs e)
    {
        if(cbox_detectors_yes.Checked == true)
        {
            detector = "Yes";
        }
        if(cbox_yes1.Checked == true && cbox_yes3.Checked == true && cbox_yes4.Checked == true && cbox_yes5.Checked == true && cbox_yes6.Checked == true && cbox_yes7.Checked == true)
        {
            questions = "Yes";
        }
        if(cbox_yes2.Checked == true)
        {
            question2 = "Yes";
        }
        if(cbox_cheif.Checked == true)
        {
            capOrCheif = "Cheif Engineer";
        }
        else if(cbox_captain.Checked == true)
        {
            capOrCheif = "Captain";
        }
        else if(cbox_na2.Checked == true)
        {
            question2 = "N/A";
        }
        else if(cbox_detectors_na.Checked == true)
        {
            detector = "N/A";
        }

        string constring = "Server = **; Database = **; User Id = **; Password = ***; Sslmode = none;";
        string Query = "INSERT INTO tbl_permit (Username, Ship, Date, TimeFrom, TimeTo, Location, Desc, Detectors, DetectorNos, Question1, Question2, SpecialPrec, OfficerSign, OfficerName, OfficerPos, CheifSign, CheifName, CaptainSign, CaptainName, PrecAddedBy, PrecBox) values(@Username, @Ship, @Date, @TimeFrom, @TimeTo, @Location, @Desc, @Detectors, @DetectorNos, @Question1, @Question2, @SpecialPrec, @OfficerSign, @OfficerName, @OfficerPos, @CheifSign, @CheifName, @CaptainSign, @CaptainName, @PrecAddedBy, @PrecBox);";
        MySqlConnection con = new MySqlConnection(constring);
        MySqlCommand cmdDatabase = new MySqlCommand(Query, con);

        cmdDatabase.Parameters.Add("@Username", MySqlDbType.VarChar, 50).Value = login.username;
        cmdDatabase.Parameters.Add("@Ship", MySqlDbType.VarChar, 50).Value = txtbox_ship.Text;
        cmdDatabase.Parameters.Add("@Date", MySqlDbType.VarChar, 50).Value = txtbox_date.Text;
        cmdDatabase.Parameters.Add("@TimeFrom", MySqlDbType.VarChar, 50).Value = txtbox_timeFrom.Text;
        cmdDatabase.Parameters.Add("@TimeTo", MySqlDbType.VarChar, 50).Value = txtbox_timeTo.Text;
        cmdDatabase.Parameters.Add("@Location", MySqlDbType.VarChar, 50).Value = txtbox_location;
        cmdDatabase.Parameters.Add("@Desc", MySqlDbType.VarChar, 50).Value = txtbox_work_desc.Text;
        cmdDatabase.Parameters.Add("@Detectors", MySqlDbType.VarChar, 50).Value = detector;
        cmdDatabase.Parameters.Add("@DetectorNos", MySqlDbType.VarChar, 50).Value = txtbox_detector_desc.Text;
        cmdDatabase.Parameters.Add("@Question1", MySqlDbType.VarChar, 50).Value = questions;
        cmdDatabase.Parameters.Add("@Question2", MySqlDbType.VarChar, 50).Value = question2;
        cmdDatabase.Parameters.Add("@SpecialPrec", MySqlDbType.VarChar, 50).Value = txtbox_precautions.Text;
        cmdDatabase.Parameters.Add("@OfficerSign", MySqlDbType.VarChar, 50).Value = txtbox_officer_sign.Text;
        cmdDatabase.Parameters.Add("@OfficerName", MySqlDbType.VarChar, 50).Value = txtbox_officer_name.Text;
        cmdDatabase.Parameters.Add("@OfficerPos", MySqlDbType.VarChar, 50).Value = txtbox_officer_pos.Text;
        cmdDatabase.Parameters.Add("@CheifSign", MySqlDbType.VarChar, 50).Value = txtbox_cheif_sign.Text;
        cmdDatabase.Parameters.Add("@CheifName", MySqlDbType.VarChar, 50).Value = txtbox_cheif_name.Text;
        cmdDatabase.Parameters.Add("@CaptainSign", MySqlDbType.VarChar, 50).Value = txtbox_captain_sign.Text;
        cmdDatabase.Parameters.Add("@CaptainName", MySqlDbType.VarChar, 50).Value = txtbox_captain_name.Text;
        cmdDatabase.Parameters.Add("@PrecAddedBy", MySqlDbType.VarChar, 50).Value = capOrCheif;
        cmdDatabase.Parameters.Add("@PrecBox", MySqlDbType.VarChar, 50).Value = txtbox_restrictions.Text;

        MySqlDataReader myReader;
        if (cbox_read.Checked == true)
        {
            con.Open();
            myReader = cmdDatabase.ExecuteReader();
            while (myReader.Read())
            {
            }

            MessageBox.Show("Hot Work Permit Form has been sent to the Cheif Engineer");
        }
        else
        {
            MessageBox.Show("You have to read it through and accept it!");
        }

    }

Upvotes: 4

Views: 1508

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562260

DATE is not a reserved word, despite the comment above. You can get a list of reserved words here: https://dev.mysql.com/doc/refman/8.0/en/keywords.html

That page lists keywords, but only a subset of those are reserved, indicated by the (R) annotation in the list.

The error message tells you which word caused the parser to become confused:

...check the manual that corresponds to your MySQL server version for the right syntax to use near 'Desc, Detectors, ...

It got confused on the word DESC. Syntax errors in MySQL always show you the portion of the query starting at the point where it got confused.

DESC is the reserved word causing a problem in this case. DESC has the (R) annotation in the keywords list I linked to.

You should delimit identifiers that conflict with reserved words:

string Query = "INSERT INTO tbl_permit (... Location, `Desc`, Detectors, ...

Upvotes: 3

Related Questions