fatal error encountered during command execution mysql c#

Question

I have a Problem during MySQL in C#. I want to search for 4 Keywords in my MySQL Database and when i found an entry my WPF Program display it in a listview but i get always an:

fatal error encountered during command execution

here my code:

    private void Button_Click(object sender, RoutedEventArgs e)
    {

        string strName = name.Text;
        string strVorname = vorname.Text;
        string strPLZ = plz.Text;
        string strOrt = ort.Text;

        try
        {


            MySqlConnection con = new MySqlConnection(@"Server=xx.xxx.xxx.xx;Uid=user;Pwd=pw;Database=db;");
            con.Open();
            MySqlCommand cmd = new MySqlCommand("SELECT Name, Vorname, Plz, FROM TBCustomer WHERE Name LIKE @strName AND Vorname LIKE @strVorname AND Plz LIKE @strPLZ", con);

            cmd.Parameters.AddWithValue( "@Name", String.Format( "%{0}%", strName));
            cmd.Parameters.AddWithValue( "@Vorname", String.Format( "%{0}%", strVorname));
            cmd.Parameters.AddWithValue("@Plz", String.Format("%{0}%", strPLZ));

            using (var reader = cmd.ExecuteReader())
            {    
                while (reader.Read())//Lese alle Datensätze aus
                {
                    var getName = reader.GetString(reader.GetOrdinal("Name"));  // 'String aus der Spalte "Name" auslesen
                    MessageBox.Show(getName);                                                         
                }

            }



            con.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }

Where is the @Name in the sql query??

Lamloumi Afif
– Lamloumi Afif

2014-08-20 08:15:03 +00:00
Commented Aug 20, 2014 at 8:15 — Lamloumi Afif
– Lamloumi Afif, Commented Aug 20, 2014 at 8:15

TJ- · Accepted Answer · 2014-08-20 10:33:03Z

1

Use this

MySqlCommand cmd = new MySqlCommand("SELECT Name, Vorname, Plz FROM TBCustomer WHERE Name LIKE @strName AND Vorname LIKE @strVorname AND Plz LIKE @strPlz", con);
cmd.Parameters.AddWithValue( "@strName", String.Format( "%{0}%", strName));
cmd.Parameters.AddWithValue( "@strVorname", String.Format( "%{0}%", strVorname));
cmd.Parameters.AddWithValue("@strPlz", String.Format("%{0}%", strPLZ));

Note : Params not surrounded by ' is correct for them to be treated as params.

Removed a , from the query (after Plz)! That was nuts.

edited Aug 20, 2014 at 10:33

answered Aug 20, 2014 at 8:14

TJ-

14.4k13 gold badges64 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

nicBit Over a year ago

thanks it works! but now i have: " 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 'FROM TBCustomer WHERE Name LIKE %Test% AND Vorname LIKE %TEST

TJ- Over a year ago

Okay, so that is against what I thought. Try adding the single quotes around.

TJ- Over a year ago

okay, try this :

cmd.Parameters.AddWithValue( "@strName", String.Format( "'%{0}%'", strName)); cmd.Parameters.AddWithValue( "@strVorname", String.Format( "'%{0}%'", strVorname)); cmd.Parameters.AddWithValue("@strPlz", String.Format("'%{0}%'", strPLZ))

<-- Adding single quotes while providing parameters.

TJ- Over a year ago

Single quote should not be before Select! It should be around the parameter values. And this should be done while providing the values so that the parameters are identified as parameters.

nicBit Over a year ago

MySqlCommand cmd = new MySqlCommand("SELECT Name, Vorname, Plz, FROM TBCustomer WHERE Name LIKE '" + ATstrName +"' AND Vorname LIKE '"+ ATstrVorname + "' AND Plz LIKE '" + ATstrPLZ + "'", con); ... is this right now? so i test it but same again

|

Lamloumi Afif · Accepted Answer · 2014-08-20 08:16:47Z

0

Check the parameters names : You can change your snippet like this :

 MySqlCommand cmd = new MySqlCommand("SELECT Name, Vorname, Plz, FROM TBCustomer WHERE Name LIKE '@Name' AND Vorname LIKE '@Vorname' AND Plz LIKE '@Plz' ", con);

            cmd.Parameters.AddWithValue( "@Name", String.Format( "%{0}%", strName));
            cmd.Parameters.AddWithValue( "@Vorname", String.Format( "%{0}%", strVorname));
            cmd.Parameters.AddWithValue("@Plz", String.Format("%{0}%", strPLZ));

answered Aug 20, 2014 at 8:16

Lamloumi Afif

9,12728 gold badges106 silver badges195 bronze badges

Collectives™ on Stack Overflow

fatal error encountered during command execution mysql c#

2 Answers 2

12 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

12 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related