OMG why was this so difficult...

So, today my challenge was sorting on a gridview inside an ajax update panel...  Note to self: no more sorting/paging in update panels (requires javascript, which I have about as much interest in as cutting a finger off).  I figured instead of trying to figure java out today I opted to remove the sorting from the gridview itself and OUTSIDE the update panel put two drop down lists, one for the column name to sort and the other for ASC or DESC.  With enjoying VB so much I figured I'd get all fancy with a couple subs to call instead of writing the same code over and over in the code behind, did I mention I'm using SqlDataSources...

So, the trick was first to try to parameterize the order by clause of the data source.  This did not got over well...

 

Ok, so whatever instead of wasting time with this I figured I'd just change the data sources select command on the fly as required, something like:

 

Dim strSC As String = SqlDataSource1.SelectCommand

Dim ordbycol As String = DropDownList1.SelectedValue

Dim ordbysort As String = DropDownList2.SelectedValue

Dim ordby As String = " ORDER BY " & ordbycol & " " & ordbysort

SqlDataSource1.SelectCommand = strSC & ordby

 

And voila order by done... But wait... nope, a sql exception around the order by.  So, next I catch the exception and drop a response.write(strSC & ordby), copy and paste into Management Studio, declare and set the parameters and surprise, surprise the query runs fine, no errors, wtf...

 

So what was the problem, how about you tell me, cause I still don't know.  What I do know though is that where required if I added:

 

SqlDataSource1.SelectCommand = SqlDataSource1.SelectCommand & " ORDER BY " & DropDownList1.SelectedValue & " " & DropDownList2.SelectedValue & ""

 

before setting the parameters all was well with the world.  I'm going to go with the fact that the double quote I dropped and the end (that I didn't in my initial sub) was what did it.  But, I've wasted enough on my life today to figure out why it didn't work, but if any actual educated people out there stumble upon this and care to share, your comments will be greatly appreciated.

 

-T