|
Colin Angus Mackay wrote: depends on the database you are using
That is why I asked earlier. easy in SQL, but I am not sure about Access. I've looked at some of the system table and it might be a little more work.
"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus
|
|
|
|
|
Having a website that is dealing with large numbers, I am storing values inside a decimal(36,18) SQL Server 2005 column.
Until recently, everything run smooth for about 1 year.
Then it crashed with a System.OverflowException exception ("Conversion overflows.") and the following stack trace (excerpt):
at System.Data.SqlClient.SqlBuffer.get_Decimal()
at System.Data.SqlClient.SqlBuffer.get_Value()
at System.Data.SqlClient.SqlDataReader.GetValueInternal(Int32 i)
at System.Data.SqlClient.SqlDataReader.GetValues(Object[] values)
at System.Data.ProviderBase.DataReaderContainer.CommonLanguageSubsetDataReader.GetValues(Object[] values)
at System.Data.ProviderBase.SchemaMapping.LoadDataRow()
at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)
...
The value it seems to be unable to read from the database is 90807364313.118401000000000000 (i.e. a rather huge, approx. 90 billions  ).
Now after digging, I found this thread at Google Groups. [^].
This thread, from 2004, tells that this is a bug in the .NET framework and that it will be fixed in next version. Now since it is already the next version, I am rather clueless.
Question: Anyone knows a hint or a workaround/fix for this issue? Maybe a hotfix from Microsoft is available?
Thanks
Uwe
|
|
|
|
|
The .NET Framework documentation states:
"The Decimal data type provides the greatest number of significant digits for a number. It supports up to 29 significant digits and can represent values in excess of 7.9228 x 10^28. It is particularly suitable for calculations, such as financial, that require a large number of digits but cannot tolerate rounding errors."
Your number is larger than 2^96 in the mantissa portion. It simply won't fit in the data type. Your only workaround is to use ToDouble, as in the thread, which will lose some precision (as double is only 64 bits). If you need something larger, you will need to implement your own scaled integer class and access the data as raw binary using GetBytes.
[EDIT:] It looks like you should be able to use the SqlDecimal type. I see you're using GetValues to retrieve all of the row's values in one go. Consider using GetSqlValues instead, to use the SQL types. I've not tried this.
-- modified at 11:57 Saturday 14th July, 2007
|
|
|
|
|
D'oh! 
Thank you very much, Mike, I didn't even care to look for the docs of double, since I implied that decimal is the largest numeric type. Stupid me!
Thanks for your hints!
|
|
|
|
|
Trying a litte bit more, I wrote this simple test application:
internal sealed class Program
{
private static void Main()
{
double v5 = 90807364313.118401000000000000;
decimal v6 = 90807364313.118401000000000000m;
Console.WriteLine( v5 );
Console.WriteLine( v6 );
}
}
Since both data types are able to store the original value, I cannot follow your initial explainations.
I still do think this is a bug in the ADO.NET framework.
|
|
|
|
|
Both of those are losing significant digits. double uses binary floating point notation - the exponent is expressed internally as a power of two. 11 bits are used for the exponent and 52 for the mantissa (the actual significant digits), the final bit being used for the sign. The largest mantissa that can be represented is 2^52 - 1 which is 4,503,599,627,370,495, effectively 15 significant decimal digits. For more on double, see IEEE-754 on Wikipedia[^] and What Every Computer Scientist Should Know About Floating-Point Arithmetic[^]. Since the position of the decimal point is represented as a binary exponent, only negative powers of two and sums of negative powers of two can be represented. Everything else is rounded. For example, consider 1/3 in decimal (0.3333 recurring) which can never be truly represented.
The decimal value only has 17 digits after the decimal point. It's kept the magnitude correct at the expense of losing the final 0. You can only get 28 whole significant digits with this format - your database definition has 38. SqlDecimal supports the same range that SQL Server does.
|
|
|
|
|
Thanks, Mike, I really do appreciate your feedback.
Even if you (which I have no doubt!) are correct with your explainations, what practical chance do I have to apply them?
Since I'm simply doing kind of "SELECT * FROM ..." and receiving a DataTable, i do see no simply/any way to tell ADO.NET not to do the automatic conversion to decimal.
Any ideas?
Thanks
uwe
|
|
|
|
|
If you're using .NET 2.0, you may be able to set the ReturnProviderSpecificTypes property to true, to get the DataTable to use SqlDecimal internally. With .NET 1.1 you probably have to drop back down to using a SqlDataReader.
|
|
|
|
|
Yes, that DataAdapter.ReturnProviderSpecificTypes is what I found, too.
I'll try that and post the result here for reference purposes.
Thank you again!
|
|
|
|
|
Feedback: When using the ReturnProviderSpecificTypes (setting it to true), I succeeded.
I can do the conversion from the SqlXxx data types (e.g. SqlDecimal) to native CLR data types be myself, and not let it ADO.NET do it automatically.
Thanks for your help!
|
|
|
|
|
hi! i just wanna ask if it is possible for a datagrid to view some of the contents of a database, then another contents afterwards???
for example, I click the search button to search a particular school subjects. E.g. i search for subject "Theology", then all students who enroll in "theology" should be display, then i will going to search the subject "college algebra", all the students who enrolled in that will be viewed???
What code i will going to use there? will i use the OleDbDataAdapter??? If not, what should i do for it????
|
|
|
|
|
Use dataview and thn set rowfilter to narrow down the result
|
|
|
|
|
ok, i already know how, I saw the process and the codes in the internet...i used the "DISTINCT" in the query builder in Data Configuration Wizard... Then i put this codes in the search button:
OleDbDataAdapter2.SelectCommand.Parameters("subject").Value = ComboBox1.Text<br />
DataSet21.Clear()<br />
OleDbDataAdapter2.Fill(DataSet21)
but how, if i will going to put the codes in the combobox? Because what i want is if i will going to select a subject in the combobox, the students who enroll in that subject will automatically displayed in the datagrid without clicking the search button... Where i will going to put the codes above?
-- modified at 2:31 Sunday 15th July, 2007
|
|
|
|
|
First of all, do yourself a favor and dump the DataGrid and use the DataGridView. You will be much happier with the added flexibility it has. Either way you can reset the contents by changing the DataSource, preferably to a DataView object.
Tom Garth
Developer
R. L. Nelson and Associates, Inc., Virginia
|
|
|
|
|
I created a db in as a dummy in sql 2005 and set the recover as full.But I want to know when there is a problem with the db how to recover.
I have backed up it also.
|
|
|
|
|
|
Hi ALL
In my application i wrote a query like this
"select * from Cand_Information where Category= '" & DpCat.SelectedItem.Text & "' or (First_Name= '" & TxtFname.Text & "' or Last_Name= '" & TxtLname.Text & "' or Sex= '" & DpdownSex.SelectedItem.Text & "' )"
But i want the Category field to be mandatory so i kept it outside the bracket. so that it should only display data with that particular Category. so rest of the fields are optional.
How to write the correct one pl help
Regards
Prakash Mishra(Banglore,India)
|
|
|
|
|
Change the or to an and
"select * from Cand_Information where Category= '" & DpCat.SelectedItem.Text & "' and (First_Name= '" & TxtFname.Text & "' or Last_Name= '" & TxtLname.Text & "' or Sex= '" & DpdownSex.SelectedItem.Text & "' )"
Ben
|
|
|
|
|
HI
i tried it before also but according to specification if i select only Category then also records should be displayed. if i put and there then i need to select at least one field from the or options. else it not show any record.
Regards
Prakash Mishra(Banglore,India)
|
|
|
|
|
Try this:
<code>string optionalFiters="";
if(TxtFname.Text!="")
optionalFiters+=" and First_Name= '" & TxtFname.Text + "'";
if(TxtLname.Text!="")
optionalFiters+=" and Last_Name= '" & TxtLname.Text + "'";
if(DpdownSex.SelectedItem.Text!="")
optionalFiters+=" and Sex= '" & DpdownSex.SelectedItem.Text + "'";
</code>
Query:
<code>"select * from Cand_Information where Category= '" & DpCat.SelectedItem.Text & "'" & optionalFiters
</code>
Regards,
Arun Kumar.A
|
|
|
|
|
Please tell me how to insert a string like this: SAMPLE'STRING into an SQL table? How escape special characters in MSSQL?
Thank you.
--------------------------------
visit: http://pmartike.deviantart.com/
|
|
|
|
|
Problem solved
--------------------------------
visit: http://pmartike.deviantart.com/
|
|
|
|
|
I have a Classic ASP website and i want to use MS SQL Express edition as the datasource. I tried the .NET Connectionstring and they do not work for the Classic ASP (ADO) site. I have tried figuring this out for the last three days. PLEASE HELP! Thanks in advance.
|
|
|
|
|
What kind of messages are you getting, if any?
"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus
|
|
|
|
|
Here is the connection string im using:
Data Source=.\SQLEXPRESS; Integrated Security=True;AttachDbFilename=|DataDirectory|\MyDatabase.mdf;Initial Catalog=MyDatabase
This is the error:
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
