Using INSERT INTO SELECT & VALUES TOGETHER-CodePudding

I'm having trouble using INSERT INTO with values from another table and one value from a variable.

Using only MySQL, this query worked perfectly:

INSERT INTO insumos_da_venda (vendacod, insumocod, nome, quantidade, unidmedida, preco) `
SELECT '11' AS vendacod
       , cod
       , nome
       , quantidade
       , unidmedida
       , preco 
FROM insumos_da_venda_temp

But when I try to use this inside c# code, it's transforming anything that I put where '11' is into '1'.

Doesn't matter if its a variable, a random value, or even if its blank, it'll always be '1' when I execute the command

Here's the c# code:

string insertInsumosVenda = "INSERT INTO insumos_da_venda (vendacod, insumocod, nome, quantidade, unidmedida, preco) 
SELECT '"   vendacod   "' AS vendacod
       , cod
       , nome
       , quantidade
       , unidmedida
       , preco 
FROM insumos_da_venda_temp;";

MySqlCommand insertInsumosVendaCmd = new MySqlCommand(insertInsumosVenda, BDconnection);

reader = insertInsumosVendaCmd.ExecuteReader();

while (reader.Read()) { }

reader.Close();

Here, the variable 'vendacod' can be any value from 0 to 99999

vendacod column is int(5) but I've also tried changing it to varchar and still didn't work.

I've seen some similar questions but none worked for me, I have no idea why.

You should be using parameterized queries. Your code is vulnerable to SQL Injection

I know its a pretty poorly written code, I'm making this program only for study purposes and for now I'm only studying c#, I'll upgrade my MySQL skills after, thanks for the tips

vendacod column is int(5) but I've also tried changing it to varchar and still didn't work - can't quite believe that C# can't handle the display formatter, and I can't quite believe that making it a varchar failed (unless it was a varchar(1) and you somehow didn't get a truncation error?) - can you show some screenshots of it "not working" as a varchar?

While taking screenshots I just realized something: it's not always '1', it's getting the value of the first column of "insumos_da_venda_temp" which is not even in the code, so I have no idea what's happening

here are some screenshots: https://imgur.com/a/1iaHrj2

CodePudding user response：

I really dont know why you are having that problem but you could try using MySqlParameter, You can read more about it here: https://dev.mysql.com/doc/dev/connector-net/8.0/html/P_MySql_Data_MySqlClient_MySqlCommand_Parameters.htm.

Anyway the implementation would be like this:

MySqlParameter p1= new MySqlParameter("@vendacod",vendacod);

string insertInsumosVenda = "INSERT INTO insumos_da_venda (vendacod, insumocod, nome, quantidade, unidmedida, preco) 
SELECT @vendacod AS vendacod
       , cod
       , nome
       , quantidade
       , unidmedida
       , preco 
FROM insumos_da_venda_temp;";

MySqlCommand insertInsumosVendaCmd = new MySqlCommand(insertInsumosVenda, BDconnection);

insertInsumosVendaCmd.Parameters.Add(p1);

reader = insertInsumosVendaCmd.ExecuteReader();

while (reader.Read()) { }

reader.Close();

CodePudding user response：

you are trying to insert vendacod as a string, but it is an integer columnt ( int(5) ). Try to add vendacod as an integer parameter

string insertInsumosVenda = "INSERT INTO insumos_da_venda (vendacod, insumocod, nome, quantidade, unidmedida, preco) 
SELECT @vendacod AS vendacod
       , cod
       , nome
       , quantidade
       , unidmedida
       , preco 
        FROM insumos_da_venda_temp;";

    var cmd = new MySqlCommand(insertInsumosVenda, BDconnection);
   cmd.CommandType = CommandType.Text;
     cmd.Parameters.Add("@vendacod", MySqlDbType.Int32).Value = vendacod;
    cmd.conection.Open();
   var result= cmd.ExecuteNonQuery();
    cmd.connection.Close();