loosely typed dataset assuming data-types

Question

Scenario Having a table with one of the column varchar(20). Column mostly contains integer values but we haven't restricted the user. 90% of users enter 50, but there are 5% users who enter 50 Units. Defined an in code query as follows

qry = select coalesc(CONVERT(Varchar(20),column1),'') from table1

Have got c# code to populate dataset as follows

DataSet ds = loader.LoadDataSet(qry);

Now what happens is that the .net runtime gets the first row and because it's an integer (in most of the case), it assigns the column an int data type and in scenarios like '50 Units', it returns blank as column1 is int (in.net runtime view) and fails at CONVERT(varchar(20), column1) and returns empty ('') column.

One alternative is to user strongly typed dataset and get it done but I would love to know of any other alternative to get it done before going on that path.

Answer 1

Sorry Guys,

My bad. Actually, it was the sql query which was failing in .net code. When a column is varchar, doing something like COALESC(CONVERT(VARCHAR(20),column1),0) fails. It should be COALESC(CONVERT(VARCHAR(20),column1),'0')

Thanks again, Nimesh