Pb retrieving sorted data from Table in SQL.

[LionelNorge]

Hi,

I get problem to retrieve data in the right order from table in database SQL.

See code attached: code.png

I want to sort data according to ID number And then to retrieve those data and fill out a ring control in the right order "descending" .

But i got always ID 2 instead of 1. See attached  picture : control.png

it seems that the select statement (see under) is not woking properly. But it is working well in a query in Access DataBase .  

//Select statement
   hstmt = DBActivateSQL (hdbc, ("SELECT * FROM %s ORDER BY ID DESCENDING",szTableName));
   if (hstmt<= DB_SUCCESS) {ShowError(); goto Error;}

Some idea ??

Lionel.

[SteveD123]

I know that this is a really stupid suggestion, but I cannot think of anything else: Have you tried DBFetchNext instead of DBFetchRandom ?

[LionelNorge]

I have tried but get the same that with DBFetchRandom.....

[robertpf]

Hi Lionel,

Could you please try adding a single quote around the string identifier, like this:   '%s'

Does it work?

I also recommend the LabWindows/CVI SQL Toolkit Reference Manual, especially chapter 5, for additional information on the library functions.

http://www.ni.com/pdf/manuals/370502a.pdf

Best regards,

[LionelNorge]

Could you please try adding a single quote around the string identifier, like this:   '%s'

Does it work?

I have tried "  the string identifier, like this:   '%s'" but it is not working ... Query works in access but dont seem to works in CVI ...

I have also worked  a lot with the 'LabWindows/CVI SQL Toolkit Reference Manual'  but without sucess.

Regards.

Lionel. 

[Constantin--P]

Hello Lionel,

According to the document Robert linked, the prototype of DBActivateSQL() is:

int statementHandle = DBActivateSQL (int connectionHandle, char SQLStatement[]);

For the 2nd parameter(SQLStatement), you're sending:

("SELECT * FROM %s ORDER BY ID DESCENDING",szTableName)

Probably your intention is to substitute %s with szTableName, but that's not what it happens.

By using comma operator, ("SELECT * FROM %s ORDER BY ID DESCENDING",szTableName) is evaluated to szTableName, so you call is equivalent to:

hstmt = DBActivateSQL (hdbc, szTableName);

In order to substitute %s with szTableName you need to first build the string you're passing as the 2nd parameter:

char query[512];
sprintf(query, "SELECT * FROM %s ORDER BY ID DESCENDING",szTableName);
hstmt = DBActivateSQL (hdbc, query);
 

[LionelNorge]

I have tried your suggestion but it still not working ...

[Constantin--P]

Can you debug the program and see that query contains the correct string.

What type is szTableName?

[LionelNorge]

Hi,

I have resolved the problem by using DBBindColChar function !!! See attached code.

Thank you for your help !!

Best regards.

Lionel.