Database Discussion Boards

Re: Sql query find the primary key ,foreign key relationship between tables

Paul Conrad14-Jul-07 10:50

Paul Conrad

14-Jul-07 10:50

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

System.OverflowException for large Decimal value

Uwe Keim13-Jul-07 23:35

Uwe Keim

13-Jul-07 23:35

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 Wink | ;-)

).

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

--
• Zeta Producer Desktop CMS
Intuitive, completely easy-to-use CMS for Windows.

• Zeta Helpdesk
Open Source ticket software for Windows and web.

• Zeta Uploader
Easily send large files by e-mail. Windows and web client.

• Desargues
Innovative, lean and neat calculator for Windows.

Re: System.OverflowException for large Decimal value [modified]

Mike Dimmick14-Jul-07 5:51

Mike Dimmick

14-Jul-07 5:51

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

Stability. What an interesting concept. -- Chris Maunder

Re: System.OverflowException for large Decimal value

Uwe Keim14-Jul-07 5:55

Uwe Keim

14-Jul-07 5:55

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!

Re: System.OverflowException for large Decimal value

Uwe Keim14-Jul-07 6:47

Uwe Keim

14-Jul-07 6:47

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 ); // Outputs 90807364313.1184
    Console.WriteLine( v6 ); // Outputs 90807364313.11840100000000000
  }
}

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.

Re: System.OverflowException for large Decimal value

Mike Dimmick14-Jul-07 8:30

Mike Dimmick

14-Jul-07 8:30

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.

Stability. What an interesting concept. -- Chris Maunder

Re: System.OverflowException for large Decimal value

Uwe Keim14-Jul-07 8:36

Uwe Keim

14-Jul-07 8:36

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

Re: System.OverflowException for large Decimal value

Mike Dimmick14-Jul-07 8:54

Mike Dimmick

14-Jul-07 8:54

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.

Stability. What an interesting concept. -- Chris Maunder

Re: System.OverflowException for large Decimal value

Uwe Keim14-Jul-07 8:57

Uwe Keim

14-Jul-07 8:57

Yes, that DataAdapter.ReturnProviderSpecificTypes is what I found, too.

I'll try that and post the result here for reference purposes.

Thank you again!

Re: System.OverflowException for large Decimal value

Uwe Keim14-Jul-07 20:15

Uwe Keim

14-Jul-07 20:15

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!

using datagrid in vb.net

Rharzkie13-Jul-07 23:16

Rharzkie

13-Jul-07 23:16

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????

Re: using datagrid in vb.net

Manas Bhardwaj14-Jul-07 4:49

Manas Bhardwaj

14-Jul-07 4:49

Use dataview and thn set rowfilter to narrow down the result

Re: using datagrid in vb.net [modified]

Rharzkie14-Jul-07 20:23

Rharzkie

14-Jul-07 20:23

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

Re: using datagrid in vb.net

TomGarth15-Jul-07 9:05

TomGarth

15-Jul-07 9:05

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

recover a database in sql 2005 server

prasadbuddhika13-Jul-07 22:54

prasadbuddhika

13-Jul-07 22:54

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. Big Grin | :-D

Re: recover a database in sql 2005 server

Mike Dimmick14-Jul-07 5:53

Mike Dimmick

14-Jul-07 5:53

See my comment here[^].

Stability. What an interesting concept. -- Chris Maunder

Help for select statement

Prakash_Mishra13-Jul-07 22:29

Prakash_Mishra

13-Jul-07 22:29

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)

Re: Help for select statement

kubben14-Jul-07 1:22

kubben

14-Jul-07 1:22

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

Re: Help for select statement

Prakash_Mishra14-Jul-07 1:37

Prakash_Mishra

14-Jul-07 1:37

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)

Re: Help for select statement

Arun.Immanuel14-Jul-07 2:37

Arun.Immanuel

14-Jul-07 2:37

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

Insert

pmartike13-Jul-07 21:09

pmartike

13-Jul-07 21:09

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/

Re: Insert

pmartike13-Jul-07 22:17

pmartike

13-Jul-07 22:17

Problem solved Big Grin | :-D

--------------------------------
visit: http://pmartike.deviantart.com/

Classic ASP and MS SQL Express Edition

Fayu13-Jul-07 6:06

Fayu

13-Jul-07 6:06

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.

Re: Classic ASP and MS SQL Express Edition

Paul Conrad13-Jul-07 6:11

Paul Conrad

13-Jul-07 6:11

What kind of messages are you getting, if any?

"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus

Re: Classic ASP and MS SQL Express Edition

Fayu13-Jul-07 6:24

Fayu

13-Jul-07 6:24

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.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.