C# How to pass sql variable

Question

I'm trying to insert some values to mysql database via C#

INSERT INTO cliente (column1, column2) VALUES (value1, value2);
set @ultima_pk =  LAST_INSERT_ID();
INSERT INTO cliente_ref (refCol1, refCol2) VALUES (refvalue1, (SELECT @ultima_pk));
INSERT INTO cliente_ref (refCol1, refCol2) VALUES (refvalue1, (SELECT @ultima_pk));

When I test it in mysql only, work perfectly but when I try to insert it via C# I cannot.

Error message:

Parameter '@ultima_pk' must be defined.

But @ultima_pk not is param, is a mysql variable.

Bellow the insert function

public bool insert(string query)
{
    this.conectar();

    cmd = new MySqlCommand(query, this.conexion);

    cmd.ExecuteReader();
    //bool retornar = cmd.ExecuteNonQuery() >= 1 ? true : false;
    bool retornar = true;
    this.desconectar();

    return retornar;
}

The connection is working perfecfly.

Any suggestion How can I pass @ultima_pk inside of query in C#?

The insert() method here will force you to write code that is horribly vulnerable to sql injection attacks, because it lacks a mechanism for accepting parameter data that is separate from the query string. — Joel Coehoorn
– Joel Coehoorn, Commented Jun 14, 2015 at 22:24
Thanks to all. I found an answer here.. stackoverflow.com/questions/21795565/… Adding AllowUserVariables=True to connection string — gvd
– gvd, Commented Jun 17, 2015 at 19:36

Joel Coehoorn · Accepted Answer · 2015-06-14 22:34:21Z

1

You need to declare it, and then you don't need to select it:

DECLARE @ultima_pk int;
INSERT INTO cliente (column1, column2) VALUES (value1, value2);
set @ultima_pk =  LAST_INSERT_ID();
INSERT INTO cliente_ref (refCol1, refCol2) VALUES (refvalue1, @ultima_pk);
INSERT INTO cliente_ref (refCol1, refCol2) VALUES (refvalue1, @ultima_pk);

Call it like this:

public bool insert_cliente(string value1, string value2, string refvalue, string refvalue2)
{
    string query = @"
    DECLARE @ultima_pk int;
    INSERT INTO cliente (column1, column2) VALUES (@value1, @value2);
    set @ultima_pk =  LAST_INSERT_ID();
    INSERT INTO cliente_ref (refCol1, refCol2) VALUES (@refvalue1, @ultima_pk);
    INSERT INTO cliente_ref (refCol1, refCol2) VALUES (@refvalue2, @ultima_pk);";

    cmd = new MySqlCommand(query, this.conexion);

    //I had to guess at parameter types/lengths. Use the actual column definitions from your database for this
    cmd.Parameters.Add("@value1", MySqlDbType.VarChar, 50).Value = value1;
    cmd.Parameters.Add("@value2", MySqlDbType.VarChar, 50).Value = value2;
    cmd.Parameters.Add("@refvalue", MySqlDbType.VarChar, 50).Value = refvalue;
    cmd.Parameters.Add("@refvalue2", MySqlDbType.VarChar, 50).Value = refvalue2;

    try 
    { 
        this.conectar();
        return (cmd.ExecuteNonQuery() >= 1);
    }
    finally
    {
        this.desconectar();
    } 
}

edited Jun 14, 2015 at 22:34

answered Jun 14, 2015 at 22:20

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

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

3 Comments

gvd Over a year ago

For mysql DECLARE @ultima_pk int; this is not valid. You only need use set @ ultima_pk = LAST_INSERT_ID(); according to reference manual. The sql code snipped that I show in my post was tested with mysql (out of C#) and work perfect. The problem here is C#.

Joel Coehoorn Over a year ago

You say that, and yet the error message is telling you that the @ultima_pk variable is not defined. Let's try defining it.

gvd Over a year ago

I prove your suggestion but I getting the same error {"Parameter '@ultima_pk' must be defined."}. It does not make sense to me, work in mysql but not from C#.

Collectives™ on Stack Overflow

C# How to pass sql variable

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related