CODEWITHSUNDEEP
c#asp.netsql-order-byfirebird
Marcello Grechi Lins
Marcello Grechi Lins

Reputation: 3410

Order By Parameter Firebird + C#

i am currently writting a method that reads date from a firebird database. Here is the Header : 

 public static void ReadInfo(int first, int skip,string orderBy)

That is my SQL Query : 

const string SQL = "SELECT FIRST @first SKIP @skip A.ID,B.SHORTNAME, A.DATETIME, A.COMPUTERNAME,A.OSLOGIN, A.DBLOGIN, A.INFOTYPE, A.INFO FROM EVENTSGENERAL A JOIN EVENTSGENERATORS B ON B.GENERATOR_ = A.GENERATOR_ ORDER BY @orderBy";

And here is where i prepare the parameters for the query:

cmd.Parameters.Add("@first", FbDbType.Integer).Value = first;
cmd.Parameters.Add("@skip", FbDbType.Integer).Value = skip;
cmd.Parameters.Add ("@orderBy", FbDbType.VarChar, 50).Value = orderBy;

The problem is: The first two parameters work (first and skip are prepared correctly),i´ve tested it. But,the orderBy parameter raises an exeption once i try to run it :

{"Dynamic SQL Error\r\nSQL error code = -804\r\nData type unknown"}

I´ve tried to change toe FbDbType to "Text",but still not working. Sorry about any bad english. Thanks in advance for any help given.

Upvotes: 1

Views: 1109

Answers (1)

Renatas M.
Renatas M.

Reputation: 11820

For order by clause needs field specified you cant pass field name. What may work is if you make @orderBy parameter datatype integer and pass index of field. for example 

select id, name from city order by 1 //this will order by id field

or if you programmatically edit sql statement like this:

string SQL = "SELECT FIRST @first SKIP @skip A.ID,B.SHORTNAME, A.DATETIME,
 A.COMPUTERNAME,A.OSLOGIN, A.DBLOGIN, A.INFOTYPE, A.INFO FROM EVENTSGENERAL A JOIN
 EVENTSGENERATORS B ON B.GENERATOR_ = A.GENERATOR_ ORDER BY {0}";
SQL = string.Format(SQL, orderBy);

Upvotes: 1

Related Questions