c# mysql insert statement. Parameterized query example?

Question

I'm trying to get this insert statement to work.

string sql = "INSERT INTO usuario (apellido,nombre,email,password,id_localidad) "
                         + "VALUES (?apellido,?nombre,?email,?password,?idLocalidad); ";

            MySqlCommand cmd = new MySqlCommand(sql, conn);
            cmd.Prepare();
            cmd.Parameters.AddWithValue("apellido ", usuario.apellido);
            cmd.Parameters.AddWithValue("nombre ", usuario.nombre);
            cmd.Parameters.AddWithValue("email ", usuario.email);
            cmd.Parameters.AddWithValue("password ", password);
            cmd.Parameters.AddWithValue("idLocalidad ", usuario.localidad.idLocalidad);
cmd.ExecuteNonQuery();

but yet again, i get an exception saying first parameter needs to be defined. If I use the @ placeholder, i get a null error when clearly I'm adding parameters. I also added the following to my connection string.

AllowUserVariables=True

I know variants of this question have appeared throughout the site, but I have followed advice provided and still haven't been able to make it work.

Any help will be greatly appreciated. Thanks

Could you please add the code with `@ placeholder` that you have tried? — sujith karivelil, Jun 24 '16 at 03:38
string sql = "INSERT INTO usuario (apellido,nombre,email,password,id_localidad) " + "VALUES (@apellido,@nombre,@email,@password,@idLocalidad); "; — Juan M, Jun 24 '16 at 03:38
cmd.Parameters.AddWithValue("@apellido ", usuario.apellido); cmd.Parameters.AddWithValue("@nombre ", usuario.nombre); cmd.Parameters.AddWithValue("@email ", usuario.email); cmd.Parameters.AddWithValue("@password ", password); cmd.Parameters.AddWithValue("@idLocalidad ", usuario.localidad.idLocalidad); — Juan M, Jun 24 '16 at 03:41
Looks good. let me know the line which gives such exception? this time — sujith karivelil, Jun 24 '16 at 03:58
could you try this one: `cmd.Parameters.Add("?apellido", MySqlDbType.VarChar).Value = usuario.apellido;` — currarpickt, Jun 24 '16 at 04:18
@currarpickt you sir/madam, are a genius. If you want to add this comment as the answer, i'll mark it as such. thanks! — Juan M, Jun 24 '16 at 04:28

score 0 · Accepted Answer · answered Jun 24 '16 at 04:31

0

You could try to specify MySQLDbType to match your DB Type and use Add function:

cmd.Parameters.Add("?apellido", MySqlDbType.VarChar).Value = usuario.apellido;

answered Jun 24 '16 at 04:31

currarpickt

2,290
4
24
39

c# mysql insert statement. Parameterized query example?

1 Answers1