JOHNFROG
asked on
error when changing sort key in gridview to a non-numeric value
VB.NET on VS2008 with MS SQL2005 backend
I have a gridview which is devoid of any columns as I am using a single template field for each row. The user can select a sort key from a dropdown list (outside of the gridview) and once selected the gridview will re-sort itself based on the selection. (see code - using a control parameter of the dropdownlist)
This works well for numeric values but as soon as I try to sort using a nchar(50) datatable field I receive an error as folows
"Error Converting Data Type Varchar To Bigint."
So my problem is when I try to sort on the Domain field of the example supplied in code. Sorting on the other 2 fields works perfectly.
I have a gridview which is devoid of any columns as I am using a single template field for each row. The user can select a sort key from a dropdown list (outside of the gridview) and once selected the gridview will re-sort itself based on the selection. (see code - using a control parameter of the dropdownlist)
This works well for numeric values but as soon as I try to sort using a nchar(50) datatable field I receive an error as folows
"Error Converting Data Type Varchar To Bigint."
So my problem is when I try to sort on the Domain field of the example supplied in code. Sorting on the other 2 fields works perfectly.
<asp:SqlDataSource ID="SqlDataSource_ContactHyperlink" runat="server" ConnectionString="<%$ ConnectionStrings:conn_db1 %>"
ProviderName="System.Data.SqlClient" SelectCommand="SELECT IndexID, OrderID,
hyperlink_url, DomainName, hyperlink_memo, origin_UserName, origin_UserID, origin_Visibility,
origin_dateTime, starRating FROM tbl_bookmarks
WHERE origin_UserID = @curUserID
ORDER BY CASE @SortBy
WHEN 'IndexID' THEN IndexID
WHEN 'starRating' THEN starRating
WHEN 'domain' THEN DomainName
END">
<SelectParameters>
<asp:SessionParameter Name="curUserID" SessionField="UserGUID" Type="String" />
<asp:ControlParameter ControlID="DropDownList_sortOrder" Name="SortBy" PropertyName="SelectedValue"
Type="String" />
</SelectParameters>
</asp:SqlDataSource>
Or check whether you pass varchar values instead of bigint for @curUserID
ASKER
That confuses me as what you are doing is converting all the other fields to character based fields and leaving the problematic one alone. Will give it a try and see what happens though. The curUserID is a GUID so it definately isnt bigint either.
Remember this works for all sorts except the domainname field which is nchar(50)
Have been playing with using RTrim on the DomainName field but cant get the syntax right. Is this perhaps the solution or am i barking up the wrong tree??
Remember this works for all sorts except the domainname field which is nchar(50)
Have been playing with using RTrim on the DomainName field but cant get the syntax right. Is this perhaps the solution or am i barking up the wrong tree??
Let me explain clearly.
In a CASE statement, the THEN Clause that is your output should be in same datatype for all WHEN clauses.
You have IndexID and starRating as integers/ bigint and DomainName as Varchar.
Obviously to resolve out this issue, you have to convert it into a common datatype either Varchar / bigint.
Conversion from bigint to varchar is possible whereas varchar to bigint is not possible.
And hence I converted all fields to varchar which should work out fine.
Hope this clarifies.
In a CASE statement, the THEN Clause that is your output should be in same datatype for all WHEN clauses.
You have IndexID and starRating as integers/ bigint and DomainName as Varchar.
Obviously to resolve out this issue, you have to convert it into a common datatype either Varchar / bigint.
Conversion from bigint to varchar is possible whereas varchar to bigint is not possible.
And hence I converted all fields to varchar which should work out fine.
Hope this clarifies.
ASKER
Thanks I didnt know that. Got my stripes on MS Access so that will probably explain it.
While we are on the subject can you tell me if it creates extra overhead if I was to create multiple SqlDataSources on my page and simply toggled between them upon changing of the dropdown list?
While we are on the subject can you tell me if it creates extra overhead if I was to create multiple SqlDataSources on my page and simply toggled between them upon changing of the dropdown list?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
works brilliantly - next time I will be less critical of answers that seem a bit strange.
Will be posting another shortly on the same block of code re getting the ascending descending to work. Currently am toggling between 2 identical SqlDataSources to achieve asc and desc outcomes.
Will be posting another shortly on the same block of code re getting the ascending descending to work. Currently am toggling between 2 identical SqlDataSources to achieve asc and desc outcomes.
ASKER
flawless !
Thanks..
And glad to help you out now and in future too..
And glad to help you out now and in future too..
Since your DomainName was character, it creates the problem for other WHEN Clauses in your CASE statement.
Open in new window