The numerical value is too large to fit into a 96 bit decimal.

Question

Hello I have an issue in which I am binding a table from sql by executing query select * from test in this table the data are in this format : Id Name Details 1 dinesh 987654312.00000000000000000000 2 sandeep 987654312.00000000000000000000 3 neha 987654312.00000000000000000000 4 sunil 987654312.00000000000000000000 but wen i am binding it then its displaying error as The numerical value is too large to fit into a 96 bit decimal. The Datatype of columns of Table is as follows:- Name DataType Allow null Id nchar(10) Checked Name nchar(10) Checked Amount numeric(38 20) Checked Code which I have written is as follows:- Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter( tmpquery ConnectionString) Dim dt As New DataSet da.Fill(dt) Please help me.....

Vulpes · Answer

Well you can certainly fill a DataSet from the rows returned by a stored procedure. See for example the following code which should also work for the OleDbDataAdapter and is easy to translate from C# to VB.Net: http://www.java2s.com/Code/CSharp/Database-ADO.net/PopulateaDataSetobjectusingastoreprocedure.htm But the stored procedure will have to convert the Details column into something that can be read directly from .NET otherwise you're going to have the same problem as before.

neha prasad · Answer

Dear Vulpes ; Server which I am using as well as client is using is SQL Server 2008. you have suggest GetSQLDecimal is WOrking but its not a good working procedure since the query will not be only select statement it can be complex stored procedure or complex query from client server. Is there any procedure to just execute the complex stored procedure or query at server and fill it in a Dataset...... so that it will a runtime solution without affecting the performance....

Vulpes · Answer

It sounds to me as though the DataAdapter is attempting to populate a decimal field in the DataSet with the contents of the Amount field in the database but is failing because 38 digit precision is needed and decimal only provides 28 digits. The possibility of this occurring is mentioned in this MSDN link ( http://msdn.microsoft.com/en-us/library/bh8kx08z.aspx ) though this applies to the SqlDataAdapter rather than the OleDbDataAdapter. In the former case you could use the SqlDataReader.GetSqlDecimal method to get the decimal in native SQL form and then try and convert it to a .NET decimal or a double. However there doesn't appear to be a corresponding option for the OleDbDataReader. Which database are you actually using?

neha prasad · Answer

Hello Satyapriya Nayak ; Sorry  Cant change the datatype of the DataBase table since its a client database. So can you suggest how can i retrieve the data of such datatype numeric(3820) through .net.... This is the only issue I am facing.... even it can a Stored Procedure retrieving such a column. Help..........

Satyapriya Nayak · Answer

Hi Neha Here is your solution Please change the datatype of Amount numeric(38 20) to Amount nvarchar(MAX) Imports System.Data Imports System.Data.SqlClient Partial Class _Default Inherits System.Web.UI.Page Dim strConnString As String = System.Configuration.ConfigurationManager.ConnectionStrings.Item(ConnectionString).ToString() Dim con As New SqlConnection(strConnString) Dim str As String Dim com As SqlCommand Dim sqlda As SqlDataAdapter Dim ds As DataSet Protected Sub Page_Load(ByVal sender As Object ByVal e As System.EventArgs) Handles Me.Load con.Open() str = (SELECT * from test) com = New SqlCommand(str con) sqlda = New SqlDataAdapter(com) ds = New DataSet sqlda.Fill(ds test) GridView1.DataMember = test GridView1.DataSource = ds GridView1.DataBind() con.Close() End Sub End Class Thanks If this post helps you mark it as answer