none
Conversion overflow with SqlDataAdapter RRS feed

  • Question

  •  

    Hallo,

     

    i want to read data from my sql Server. My first try worked with SqlDataReader (see out commented code) without problem. But now i want to do that with SqlDataAdapter and DataSet, but I get a conversion overflows.

     

    What does that mean and how can i solve that problem??

     

    Her my code:

    Code Snippet

    public void get_sql_newdata()

    {

    string sql_select = "SELECT No_ AS art_nr, Description AS beschr_1, [Description 2] AS beschr_2, [EAN-Code] AS ean, [Seite Haromac Katalog] AS seite_haromac, [Seite LSR Katalog] AS seite_lsr, [Seite Aktionskatalog] AS seite_aktionskatalog, [Seite u_fa Katalog] AS seite_ufa, [Katalog VE] AS ve_katalog, [Gross Weight] AS gewicht_brutto, [Net Weight] AS gewicht_netto, Katalogartikel AS katalog FROM dbo.Haromac$Item WHERE (Katalogartikel = 1)";

    try

    {

    SqlConnection con = new SqlConnection(Properties.Settings.Default.sql_db_con);

    con.Open();

    SqlCommand cmd = new SqlCommand(sql_select, con);

    SqlDataAdapter da = new SqlDataAdapter(sql_select, con);

    DataSet ds = new DataSet();

    da.Fill(ds);

    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)

    {

    richTextBox1.AppendText("Nr.: "+ (string)ds.Tables[0].Rows[i]["art_nr"]);

    }

    //SqlDataReader dr = cmd.ExecuteReader();

    //while (dr.Read())

    //{

    // richTextBox1.AppendText((string)dr["art_nr"]+"\n");

    //}

    //dr.Close();

    con.Close();

    }

    catch (Exception e)

    {

    richTextBox1.AppendText(e.Message);

    }

    }

     

     

    Wednesday, March 19, 2008 10:58 AM

Answers

  • This should work:

    Code Snippet

     

    SqlDataAdapter da = new SqlDataAdapter("SELECT bigDecimal FROM testDecimal", con);

    da.ReturnProviderSpecificTypes = true;

     

    DataSet ds = new DataSet();

     

    da.Fill(ds);

                       

    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)

    {

    Console.WriteLine(ds.Tables[0].Rows[i][0]);

    }

     

     

     

    Thursday, March 27, 2008 6:06 PM

All replies

  • Ok, after searching a bit more I found the reason for the error. Here you can find a detailed description:

    http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=96538

     

    and for my project there is a good workaround in the msdn library at:

    http://msdn2.microsoft.com/en-us/library/ms172137.aspx

     

     

    Party-Pansen

     

    Wednesday, March 19, 2008 11:21 AM
  •  

    Hallo,

    the problem goes on, but I cannot understand. Now I use the DataTable with Type specified columns, like the webpage i told in the post before. In most cases it works. My Sql Database has fields of type decimal(38,20), and by DataTable.Columns.Add("xxx", typeof(SqlDecimal)); it works with my SQL Select Statement. But not in all cases.

     

    There is one row of the same type decimal(38,20) and there I get a conversion overflow. I think I nearly tried all possible ways to solve it, but i cannot get it to work.

     

    If i change the Line to DataTable.Columns.Add("xxx", typeof(SqlInt64));

     

    I get the error: "Object of Type 'System.Decimal' cannot be converted into object of Type 'System.Data.SqlTypes.SqlInt64'"

     

    I understand that error, but why isn't that a conversion overflow, where is the difference between a conversion overflow ??

     

    I hope someone has a solution for that problem.

     

    thanks,

    Party-Pansen

    Wednesday, March 19, 2008 3:41 PM
  • Please post the exception stack trace, exception message, exception class and the data you get this exception at.

     

    Wednesday, March 19, 2008 8:50 PM
  • I don't really know what you mean.

     

    The exception is called by executing DataTable.Load(DataReader).

     

    The Debug Output says "A first chance exception of type 'System.ArgumentException' occurred in System.Data.dll"

     

    What do you mean by "the exception stack trace, exception class and the data you get this exception at". I think I already told the exception message. Where to find the other things ? I have a "Call Stack" windows, the line "Web_db_Update.exe!Web_db_Update.Form1.get_sql_Data() Line 63 C#" is highlighted, but that cannot be important.

     

    Please tell me where to find the information you want

     

    thanks,

    Party-Pansen

    Thursday, March 20, 2008 6:09 AM
  • The exception you caught is referenced by variable e in your code. Simply give me the output of e.ToString().

     

    The other question is why do you configure DataSet and DataTableAdapter yourself? Right-click the project in Solution Explorer, choose Add->New Item. In the window popped-up choose DataSet, give it a solid name and click "Add".

    This would create you a data set (*.xsd file). Now double-click on your DataSet in solution explorer to open designer. In designer field right-click and choose Add->TableAdapter. The configure it using the wizard.

    This way you can create a data table adapter very straight-forward way.

     

    Refer to this link for more info on TableAdapter: http://msdn2.microsoft.com/en-us/library/bz9tthwx(VS.80).aspx

    Thursday, March 20, 2008 6:15 PM
  • Here my e.ToString():

    Code Snippet

    System.OverflowException: Konversionsüberläufe bei System.Data.SqlClient.SqlBuffer.get_Decimal() bei System.Data.SqlClient.SqlBuffer.get_Value() bei System.Data.SqlClient.SqlDataReader.GetValueInternal(Int32 i) bei System.Data.SqlClient.SqlDataReader.GetValues(Object[] values) bei System.Data.ProviderBase.DataReaderContainer.CommonLanguageSubsetDataReader.GetValues(Object[] values) bei System.Data.ProviderBase.SchemaMapping.LoadDataRow() bei System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping) bei System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue) bei System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) bei System.Data.Common.LoadAdapter.FillFromReader(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) bei System.Data.DataTable.Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler errorHandler) bei System.Data.DataTable.Load(IDataReader reader) bei Web_db_Update.Form1.get_sql_newdata() in C:\\Dokumente und Einstellungen\\rauhut.HWH\\Eigene Dateien\\Visual Studio 2008\\Projects\\Web_db_Update\\Web_db_Update\\Form1.cs:Zeile 106."

     

     

    Konversionsüberläufe means conversion overflows Smile

     

    I first tried to do that by creating a dataset ... but There is the same error. And then I tried it manually. If you clicked on one of the links I told before, you could see my problem described. They told to do it the way I do it now, with the manually assignment of the datatypes. But this one field will not work and I don't know why

     

    Party-Pansen

    Tuesday, March 25, 2008 6:30 AM
  • This issue is described in the following KB article http://support.microsoft.com/kb/932288/.  The workaround at present is to not use more than 28 decimal digits of precision in your declaration of the column on SQL Server, because the SqlDecimal CLR type does not support more than 28, though SQL Server supports up to 38.

    I hope that helps,

    John

    Tuesday, March 25, 2008 6:22 PM
  • Try this code

     

    Code Snippet

    SqlCommand cmd = new SqlCommand("SELECT * FROM testDecimal", con);

    SqlDataReader reader = cmd.ExecuteReader();

    while (reader.Read())

    {

    Console.WriteLine(reader.GetSqlDecimal(0));

    }

     

     

    Tuesday, March 25, 2008 6:57 PM
  • Hallo, that works  !!

    Code Snippet

    int x;

    while (reader.Read())

    {

    x = (int)reader.GetSqlDecimal(0);

    }

     

     

     

    But why does this not work ?

    Code Snippet

    column = new DataColumn();

    column.DataType = typeof(SqlDecimal);

    column.ReadOnly = true;

    column.ColumnName = xxx;

    sql_newdata.Columns.Add(column);

    sql_newdata.Load(reader);

     

     

    If there is no solution to use datatable.load(reader) i will work with that code

     

    Thank you for solving that problem

     

    Party-Pansen

    Wednesday, March 26, 2008 6:11 AM
  • The SqlDataAdapter is using a different underlying type from the one being sent, and it can support only up to 28 decimal digits, while the SQL CLR type can support up to 38.  What is happening is that you are getting an overflow because you have more than 28 decimal digits in your SQL type.  This is dealt with by the strong typed reader.Read, but not by the SqlDataAdapter, currently.


    When using SqlDataAdapter there is a way to specify that SqlTypes be filled into the DataSet:  Set ReturnProviderSpecificTypes = True.


    I hope that helps,



    John


    Wednesday, March 26, 2008 9:19 PM
  • This should work:

    Code Snippet

     

    SqlDataAdapter da = new SqlDataAdapter("SELECT bigDecimal FROM testDecimal", con);

    da.ReturnProviderSpecificTypes = true;

     

    DataSet ds = new DataSet();

     

    da.Fill(ds);

                       

    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)

    {

    Console.WriteLine(ds.Tables[0].Rows[i][0]);

    }

     

     

     

    Thursday, March 27, 2008 6:06 PM
  •  

    Thanks a lot, now i got it to work

     

    Party-Pansen

    Thursday, April 10, 2008 4:36 PM