All,
I am encountering "Conversion overflow" exceptions on one of the SqlDataAdapter.Fill() usages for a decimal field. The error occurs for value beginning 10 billion, but not till 1 billion. Here is the code:
DataSet ds = new DataSet();
SqlDataAdapter sd = new SqlDataAdapter();
adapter.SelectCommand = <my SQL Command instance>
adapter.Fill(ds);
I have read using SqlDataReader as an alternate but we need to set the datatype and precision explicitly. There are at least 70 columns that I am fetching and I don't want to set all of them only for one decimal field in error.
Can anyone suggest alternate approaches?
Thank you.
Although dataset is allowed for "filling" a data adapter, I've typically done with a DataTable instead as when querying, I'm only expecting one result set. Having said that, I would pre-query the table, just to get its structure... something like
select whatever from yourTable(s) where 1=2
This will get the expected result columns when you do a
DataTable myTable = new DataTable();
YourAdapter.Fill( myTable );
Now that you have a local table that will not fail for content size because no records will have been returned, you can now explicitly go to that one column in question and set its data type / size information as you need...
myTable.Columns["NameOfProblemColumn"].WhateverDataType/Precision = Whatever you need...
NOW, your local schema is legit and the problem column will have been identified with its precision. Now, put in your proper query with proper where clause and not the 1=2 to actually return data... Since no actual rows in the first pass, you don't even need to do a myTable.Clear() to clear the rows... Just re-run the query and dataAdapter.Fill().
I haven't actually tried as I don't have your data issues to simulate same problem, but the theoretical process should get you by without having to explicitly go through all columns... just the few that may pose the problem.