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

Question

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 'where LastName=Iguin' at line 1

i can't fix the error :( can someone help me please

this is my code

try
{
    string contactNumVal = txtColContact.Text;

    if (contactNumVal.Length < 11)
    {
        MessageBox.Show("The Contact Number must have 11 digit");
    }
    else
    {
        DialogResult dw;

        dw = MessageBox.Show("Are you sure you want to Edit this data", "Confirm Deletion", MessageBoxButtons.YesNo);
        if (dw == DialogResult.Yes)
        {
            string MyConString = "SERVER=localhost;" +
                     "DATABASE=prototype_db;" +
                     "UID=root;";
            using (MySqlConnection connection = new MySqlConnection(MyConString))
            {
                connection.Open();
                DataTable dt = new DataTable();
                using (MySqlDataAdapter da = new MySqlDataAdapter("Select Collector_ID, LastName, " +
                              "FirstName,MiddleName,Address,ContactNo,BirtDay,Gender" +
                              "from collector_profile where LastName like @clname", connection))
                {
                    da.SelectCommand.Parameters.AddWithValue("@clname", "%" + txtColLname.Text + "%");

                    da.Fill(dt);

                    dt.Rows[0].BeginEdit();

                    dt.Rows[0][1] = txtColLname.Text;
                    dt.Rows[0][2] = txtColFname.Text;
                    dt.Rows[0][3] = txtColMname.Text;
                    dt.Rows[0][4] = txtColAddress.Text;
                    dt.Rows[0][5] = txtColContact.Text;
                    dt.Rows[0][6] = dtpCbday.ToString();
                    dt.Rows[0][7] = cmbCgender.SelectedItem.ToString();

                    dt.Rows[0].EndEdit();
                    MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
                    da.Update(dt);

                    connection.Close();
                }
            }

            MessageBox.Show("Data is now updated");
        }

    }
}

catch (Exception error)
{
    MessageBox.Show(error.ToString());
}

score 5 · Accepted Answer · answered Dec 06 '13 at 21:25

5

You need space between Gender and from in your SQL string.

  using (MySqlDataAdapter da = 
           new MySqlDataAdapter("Select Collector_ID, LastName, " +
           "FirstName,MiddleName,Address,ContactNo,BirtDay,Gender " + //here
           "from collector_profile where LastName like @clname", connection))

For future debugging, try copying your SQL command text from debug, and then executing the same in your SQL server to see if it works. (This will give you a better idea about the error)

answered Dec 06 '13 at 21:25

Habib

219,104
29
407
436

He might also want to consider using a verbatim string literals to make SQL queries more readable: http://stackoverflow.com/questions/1100260/multiline-string-literal-in-c-sharp . That might have prevented this error. – outis nihil Dec 06 '13 at 21:46

score 1 · Answer 2 · answered Dec 06 '13 at 21:25

1

Use instead like;

... where LastName LIKE '%' + @clname + '%'

and then

da.SelectCommand.Parameters.AddWithValue("@clname", "txtColLname.Text);

Also you need to put a space before you WHERE part.

answered Dec 06 '13 at 21:25

Soner Gönül

97,193
102
206
364

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

2 Answers2