After about 2 months downtime, Reimers.dk by Jacob Reimers is up again. You can download the excellent Google Maps control for ASP.NET 2.0, which supports version 2 of the Google Maps API. The licensed version has many more features, but I’ve worked with the free version only and will soon post some tips, especially how to enable the mousewheel zooming feature. Please note that version 1 of the Google Maps API is now dead.
When building an editable ASP.NET 2.0 GridView or DetailsView this error is probably the most common issue. There must hardly be a developer who has not come across it at one time or another. It occurs when working with an ObjectDataSource based on a TableAdapter in ASP.NET 2.0, the easy path to enable Updating, Inserting and Deleting without any code or stored procedures the TableAdapter respresenting the data layer. This is the main advantage the GridView has over the DataGrid of .NET v.1x yore.
There is plenty of discussion of this issue available online, but two measure are mandatory:
First, always set the ReadOnly property of the data key fields of your GridView or DetailsView to False. It’ True by default. It’s very easy to forget to redo this when rebinding the GridView.
Second, when configuring the TableAdapter with th TableAdapter Configuration Wizard, be sure to not use a wildcard in the query at “What data should be loaded into the table?”, so use
SELECT FirstName, LastName, ID FROM Customer
instead of
SELECT * FROM Customer
A click on the Query Builder… button will reformat the query automatically.
Some say that disabling the second or third of the Advanced Options helps with this issue, however, I cannot confirm that is has any real effect.
Always remember to do and redo these two things and your GridView will manage your data just fine.
In Microsoft SQL Server 2005 Transact-SQL (and prior versions), you can make a dynamic ORDER BY based on a variable like so:
DECLARE @OrderBy varchar (50)
SELECT * FROM Customer
ORDER BY
CASE @OrderBy
WHEN ‘CustomerID‘ THEN Customer.CustomerID –CustomerID is an integer column
WHEN ‘MemberSince‘ THEN Customer.MemberSince –MemberSince is smalldatetime colum
WHEN ‘FullName‘ THEN Customer.FullName – FullName is a varchar colum
WHEN ‘Category‘ THEN Customer.Category – Category is a char column
WHEN ‘Rating‘ THEN Customer.Rating –Rating is a float column
END ASC
SET @OrderBy = ‘CustomerID‘
SET @OrderBy = ‘Rating‘
Conversion failed when converting character string to smalldatetime data type.
in the first case
and
Msg 8115, Level 16, State 2, Line 3
Arithmetic overflow error converting expression to data type smalldatetime.
in the second if the optimizer encounters data that can’t be converted to integer.
The explanation is found in the SQL Server books online:
[The CASE statement] returns the highest precedence type from the set of types in result_expressions and the optional else_result_expression.
This means that you cannot mix types in your CASE statement which cannot be implicitly converted into the types with higher precedence according to the following list (also found in Books Online):
Because datetime and smalldatetime with their high precedence are internally stored as 4-byte and 2-byte integers respectively, you can almost always mix integers with datetime or smalldatetime. Floating numbers don’t mix unless they can be cleanly converted to integers.
As a workaround you could use table variables or Common Table Expressions (CTE, new in SQL Server 2005), insert the ordered data, and query these instead.
The better solution with less overhead might be to group the CASE statements according to data types. The above example would look like this:
DECLARE @OrderBy varchar (50)
SELECT * FROM Customer
ORDER BY
CASE @OrderBy –“integer data” first
WHEN ‘CustomerID‘ THEN Customer.CustomerID –CustomerID is numeric column
WHEN ‘MemberSince‘ THEN Customer.MemberSince –MemberSince is smalldatetime colum
END, CASE @OrderBy –now the char and varchar
WHEN ‘FullName‘ THEN Customer.FullName – FullName is a varchar colum
WHEN ‘Category‘ THEN Customer.Category – Category is a char column
END ASC, CASE @OrderBy –finally the float data and non-int numeric columns
WHEN ‘Rating‘ THEN Customer.Rating – Rating is a float column
END ASC
Another approach you can “upconvert” everything to varchar like so:
DECLARE @OrderBy varchar (50)
SELECT * FROM Customer
ORDER BY
CASE @OrderBy
WHEN ‘CustomerID‘ THEN CONVERT (varchar, Customer.CustomerID)
WHEN ‘MemberSince‘ THEN CONVERT (varchar, Customer.MemberSince)
WHEN ‘FullName‘ THEN CONVERT (varchar, Customer.FullName)
WHEN ‘Category‘ THEN CONVERT (varchar, Customer.Category)
WHEN ‘Rating‘ THEN CONVERT (varchar, Customer.Rating)
END ASC
This discussion has been running for almost six years which must be some kind of record. This post is meant to give a short gist of it.
The Amazon Ecommerce Service is a great tool not just to monetize you web site, but also to get relevant content into your site. This content is frequently updated and search engines love this!
As I found when researching listing travel guides on my Thailand Hotels web site, the latest version 4.0 ECS have matured into an easy to use and stable offering. When I started using Amazon Web Services about three years ago, there were still some quirks, especially with Unicode encoding (more on that in Part 2).
A good starting point is downloading the AmazonECS.Net library and demo code created by Dominic B
Recent Comments