Solved

error when changing sort key in gridview to a non-numeric value

Posted on 2009-07-06
9
390 Views
Last Modified: 2012-05-07
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.
<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>

Open in new window

0
Comment
Question by:JOHNFROG
  • 5
  • 4
9 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24784195
Hope this helps:

Since your DomainName was character, it creates the problem for other WHEN Clauses in your CASE statement.
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 cast(IndexID AS varchar(1000))

WHEN 'starRating' THEN cast(starRating AS varchar(1000))

WHEN 'domain' THEN DomainName

END

Open in new window

0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24784199
Or check whether you pass varchar values instead of bigint for @curUserID
0
 

Author Comment

by:JOHNFROG
ID: 24784341
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??
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24784398
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.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:JOHNFROG
ID: 24784436
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?
0
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 500 total points
ID: 24784497
>> 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?

As far as you manage the connections properly by opening and closing as required, there is no overhead on it.
But if you have multiple things opened and not properly closed then it might bring the system down by not allowing to open new connections and connection resource related issues.

Hope that query too worked out.
0
 

Author Comment

by:JOHNFROG
ID: 24784505
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.

0
 

Author Closing Comment

by:JOHNFROG
ID: 31600110
flawless !
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24784577
Thanks..
And glad to help you out now and in future too..
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now