Midori_hige Midori_hige - 22 days ago 8
SQL Question

Why my SQL syntax is wrong?

I have the following SQL query where I want to get results through two variables. However, I'm getting a syntax error, more specifically:

near '+'pablo'+ '%' AND apellido LIKE '%' +'rodriguez'+ '%'' at line 1 at
MySql.Data.MySqlClient.MySqlStream.ReadPacket() at
MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int32&
insertedId) at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId,
Int32&affectedRows, Int32& insertedId) at
MySql.Data.MySqlClient.Driver.NextResult(Int32
statementId, Boolean force) at
MySql.Data.MySqlClient.MySqlDataReader.NextResult()
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) at
MySql.Data.MySqlClient.MySqlCommand.ExecuteReader() at
ProyectoProgra4Nota3.modelos.Usuario.buscarQuery(String search, String search2)


This is my query:

MySqlCommand comando = new MySqlCommand();
comando.Connection = con.usaConexion();
comando.CommandText = "SELECT * FROM usuarios WHERE nombre LIKE '%' +@search+ '%' AND apellido LIKE '%' +@search2+ '%' ";

comando.Parameters.AddWithValue("@search", search);
comando.Parameters.AddWithValue("@search2", search2);

MySqlDataReader reader = comando.ExecuteReader();

while (reader.Read())
{
Usuario u2 = new Usuario();

u2.Correo = reader["correo"].ToString();
u2.Nombre = reader["nombre"].ToString();
u2.Apellido = reader["apellido"].ToString();
u2.Clave = reader["clave"].ToString();

lista.Add(u2);
}

Answer

Try the following, it adds the wild cards directly to the parameter instead of in the query itself. It would really help if you could post an English stack trace / error message.

using(MySqlCommand comando = new MySqlCommand())
{
    comando.Connection = con.usaConexion();
    comando.CommandText = "SELECT * FROM usuarios WHERE nombre LIKE @search AND apellido LIKE @search2";

    comando.Parameters.AddWithValue("@search", "%"+search+"%");
    comando.Parameters.AddWithValue("@search2", "%"+search2+"%");

    using(MySqlDataReader reader = comando.ExecuteReader())
    {
        // rest of code
    }
}

You should also wrap your instances where the type implements IDisposable in using blocks. This ensures the resources are cleaned up even in the event of an exception. This is especially true for the connection object, named con in your case.