Trouble with sorting SQL requested data!

[passs]

Hello,
Maybe someone can tell me where is a mistake here. I'm taking dataes from MS SQL DB and put it into dropdownlist menu as items, but i need to sort them by ID. for example this code take data from table "Year" which has columns: YearID, Year:

for (int iCount = 1; iCount <= num_of_str; iCount++)
{
//create command object
SqlCommand cmdGetCalls = new SqlCommand(&quot;SELECT Year FROM Year WHERE YearID =&quot; + iCount + &quot;ORDER BY YearID DESC&quot;, sqlConnection);
//create reading data object
SqlDataReader dreadCalls;
//do a command
dreadCalls = cmdGetCalls.ExecuteReader();
//show strings
while (dreadCalls.Read())
{
ddlYear.Items.Add(new ListItem(dreadCalls.GetInt32(0).ToString(),dreadCalls.GetInt32(0).ToString()));
}
jCount++;
//close reading data object
dreadCalls.Close();
}

But this tale: &quot;ORDER BY YearID DESC&quot; doesn't work and i can not understand why.

Will be very thankful for any help!
Best regards,
Alexander

 

[cappmgr]

Not sure why you would wrap it in the for construct or what jCount++ is doing. What if you remove it from the for construct and rebuild your sql statement to use YearID IN (num_of_str)
assuming num_of_str is a csv.
Marty

 

[Rhys666]

Look at this line;

SqlCommand cmdGetCalls = new SqlCommand("SELECT Year FROM Year WHERE YearID =" + iCount + "ORDER BY YearID DESC", sqlConnection);

there's no space between iCount and ORDER BY... so, if iCount = 1, the statment will go through as...

SELECT Year FROM Year WHERE YearID =1ORDER BY YearID DESC

Add a space character after the quote marks and before ORDER

Rhys

Be careful that the light at the end of the tunnel isn't a train coming the other way.

 

[passs]

Hey Rhys!
I insert spaces in all such requests but no result. Items still not sorted, and i just can not understand why. It mast be sorted, but no
May be it can be some kind of trounle with MS SQL Server, what do you think?

Thank you a lot for your help!

Best regards,
Alexander

 

[cappmgr]

How can you sort on what is in your where predicate the way you have it built
if YearID = 1999 the result set is all YearID equal to 1999 and you sort on the same value 1999
I'm confused.
Marty

 

[rosenk]

The way you're doing it, you can just order your YearIDs descending just by changing your [tt]for[/tt] to:
[tt]for (int iCount = num_of_str; iCount >= 1; iCount--)[/tt]

It's a little strange, though. (You certainly don't need the [tt]ORDER BY[/tt], as it's not doing anything, as mentioned above.)

 

[passs]

YEs-yes-yes
i realised that this night, and was very suprised how stupid i am))

Thank you a lot guys! For your help and attantion!
I'm very thankful to all of you!

Best regards,
Alexander