17

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?

Nathan Palmer
  • 1,970
  • 1
  • 20
  • 28

3 Answers3

40

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?

Community
  • 1
  • 1
bernd_k
  • 11,558
  • 7
  • 45
  • 64
3

What version of the MySQL data provider are you using? You may need to update.

I found this in the 5.0 documentation:

Prior versions of the provider used the '@' symbol to mark parameters in SQL. This is incompatible with MySQL user variables, so the provider now uses the '?' symbol to locate parameters in SQL. To support older code, you can set 'old syntax=yes' on your connection string. If you do this, please be aware that an exception will not be throw if you fail to define a parameter that you intended to use in your SQL.

Bugs
  • 4,491
  • 9
  • 32
  • 41
  • Thanks for the update. I saw that in the documentation too. It's a pretty recent version since it was downloaded only a couple of weeks ago. I tried the old syntax=yes with no change in behavior. – Nathan Palmer Jun 16 '09 at 04:22
  • Link appears to be dead now. – Bugs Apr 18 '17 at 15:23
1

You could add a column to your table called rownum, then populate it with values:

table.Columns.Add("rownum");
for (int i=0; i < table.Rows.Count; i++)
{
table.Rows[i]["rownum"] = i;
}
Chook2330
  • 21
  • 1
  • I'll be honest I would shy away from manually generating the number this way but I could see this as a possible solution. I think really I need to rethink the way I'm pulling the data out. – Nathan Palmer Jun 17 '09 at 02:38