SqlDataAdapter.Fill() - Conversion overflow

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 s开发者_StackOverflowd = 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.

I had the same problem and the reason is because in my stored procedure I returned a decimal(38,20) field. I changed it into decimal(20,10) and all works fine. It seems to be a limitation of Ado.Net.

CREATE PROCEDURE FOOPROCEDURE AS
BEGIN
    DECLARE @A DECIMAL(38,20) = 999999999999999999.99999999999999999999;
    SELECT @A;
END
GO

string connectionString ="";
SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
SqlCommand cmd = new SqlCommand("EXEC FOOPROCEDURE", conn);
SqlDataAdapter adt = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adt.Fill(ds); //exception thrown here