CODEWITHSUNDEEP
c#mysqlselectdatatable
namco
namco

Reputation: 6338

select from table with row number to c# datatable gives an error

i use MySQL i have a mysql table.
And i want to select all records with row numbers.
and for this i use this sql command. 

SELECT @row := @row + 1 as row, t.* FROM t_persons t, (SELECT @row := 0) r

but when i try to run the command and load data to datatable like below 

DataTable dt = new DataTable();
command.CommandText = sql;
adapter.SelectCommand = command;
adapter.Fill(dt);

it gives an error like Fatal error encountered during command execution.
But when i try this command in phpmyadmin it works fine. So what is the problem. And how to solve it. Thanks...

Upvotes: 1

Views: 1950

Answers (2)

Zach Dunton
Zach Dunton

Reputation: 31

This is actually a C# error, not a MySQL error. C# is seeing the @ and assuming that you want to use a parameterized query.

adding

;Allow User Variables=True

to your connection string will fix it.

Found here How can I use a MySql User Defined Variable in a .NET MySqlCommand?

Upvotes: 3

DRapp
DRapp

Reputation: 48139

It looks like completely valid MySQL implementation of MySQL Variables within a query. You pre-declare them with your select @row := 0 as an "r" alias. It think the problem is that you are calling the final column name "row" which may be reserved. Just for grins, change to:

@row := @row + 1 as myRow

OR... change your variable from @row to @varRow (variable Row)... or a combination of both...

@varRow := @varRow +1 as myRow

I have no idea who down-voted the answer, but what you are doing is quite normal with MySQL, and I've done it MANY TIMES as can be seen through MANY of my posted answers here at s/o.
I would then try to retrieve the columns by the table FIRST, then the @vars columns... don't know if its something within the engine to get a valid record entry before trying to write the @var...

SELECT 
      t.*,
      @varRow := @varRow + 1 as myRow
   FROM 
      t_persons t, 
      (SELECT @varRow := 0) r

Upvotes: -1

Related Questions