Nathan Palmer Nathan Palmer - 6 months ago 9
SQL Question

Is it possible to use a MySql User Defined Variable in a .NET MySqlCommand?

I'm trying to execute a query that currently works in phpMyAdmin but it does not working when executing it in .NET using the MySqlAdapter. This is the Sql statement.

SELECT @rownum := @rownum +1 rownum, t . *
FROM (
SELECT @rownum :=0
) r, (
SELECT DISTINCT
TYPE FROM `node`
WHERE TYPE NOT IN ('ad', 'chatroom')
)t


It is using the @rownum to number each distinct row that is returned from my inner scalar query. But if I use it in .NET it's assuming that the @rownum is a parameter and throwing an exception because I didn't define it.

using (var sqlConnection = new MySqlConnection(SOURCE_CONNECTION))
{
sqlConnection.Open();

MySqlDataAdapter sqlAdapter = new MySqlDataAdapter(SqlStatement, sqlConnection);

DataTable table = new DataTable();
sqlAdapter.Fill(table);
sqlConnection.Close();

return table;
}


Any ideas for how I could get around this problem? Or possible ways for me to get a line number?

Answer

I found this blog, which tells, that with newer versions of .net Connector you have to add

;Allow User Variables=True

to the connection string. Compare my SO question How can I use a MySql User Defined Variable in a .NET MySqlCommand?

Comments