MySQL query error in c#

Question

When i run the query from visual studio c#

insert into nts1.empty_routes_mip 
select @rownum := @rownum + 1, station_from_id, station_to_id, nko_empty_run, cost, nko_min, nko_max, nko_ratio_1, nko_ratio_2 
from nts1.empty_routes, (SELECT @rownum := 0) r
where id in (select empty_route_id from nts1.empty_runs_made_lp);

i get an error

MySql.Data.MySqlClient.MySqlException (0x80004005): Fatal error encountered during command execution. ---> MySql.Data.MySqlClient.MySqlException (0x80004005): Parameter '@rownum' must be defined.
в MySql.Data.MySqlClient.Statement.SerializeParameter(MySqlParameterCollection parameters, MySqlPacket packet, String parmName, Int32 parameterIndex)
в MySql.Data.MySqlClient.Statement.InternalBindParameters(String sql, MySqlParameterCollection parameters, MySqlPacket packet)
в MySql.Data.MySqlClient.Statement.BindParameters()
в MySql.Data.MySqlClient.PreparableStatement.Execute()
в MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
в MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
в MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()

Could anyone please help me how to fix?

Answer 1

From the error that you posted I would say that you didn't define the variable @rownum and therefor it cannot execute the query. I don't know how you are using your entire query but since its in c# you probably can use something like this:

  <SQLcommand_name>.parameters.add("@parametername", SqlDbType.<variabletype>).Value = <variable value>;

the things between <> are to be set according to your own database etc.

Answer 2

Your forgot to initialize @rownum

insert into nts1.empty_routes_mip 
SET @rownum:=0; /* YOUR MISTAKE*/
select @rownum := @rownum + 1, station_from_id, station_to_id, nko_empty_run,cost, nko_min, nko_max, nko_ratio_1, nko_ratio_2 
from nts1.empty_routes, (SELECT @rownum := 0) r
where id in (select empty_route_id from nts1.empty_runs_made_lp);