C#: arithmetic overflow by reading data via ODBC

Question

In my actual project im reading Data from a Filemaker v14 Database. Im getting the Data like that:

command.CommandText = "SELECT * FROM CAR";

//Create new SqlDataReader object and read data from the command.
using (OdbcDataReader reader = command.ExecuteReader())
{
    /*
    * Get and cast the ID
    */
    int counter = 0;
    while (reader.Read() && counter < numberOfOrders)
    {
        SalesOrder s = new SalesOrder();
        s.abNR = reader["Auftrags Nr."].ToString();
        s.artNr = reader["Artikel nr."].ToString();
        s.quantity = reader["Menge"].ToString();
        s.city_country_Coustomer = reader["Stadt"].ToString();
    }
}

This code is running perfectly on the computer I'm developing on.

If I put my project to my IIS this error occurs:

Arithmetic operation resulted in an overflow.

in this line:s.abNR = reader["Auftrags Nr."].ToString();

I already checkt the dsn on both my computer and the server. Both seem to be the same.

The conection is created like that:

conn = new OdbcConnection("DSN=FILEMAKER1;Uid=Test;Pwd=tset");
conn.Open();

command = conn.CreateCommand();

Im looking forward to your answers!

EDIT:

This is my SalesOrder-Class:

public class SalesOrder
{
    public string abNR { get; set; }
    public string artNr { get; set; }
    public string quantity { get; set; }
    public string city_country_Coustomer { get; set; }
}

EDIT 2:

Sample Data for a SalesOrder:

Aufrags Nr. | Artikel nr. | Menge | Stadt   |
------------+-------------+-------+---------+
  168953    |   508800    |   2   | Berlin  |
------------+-------------+-------+---------+
  167996    |   508850    |   4   | München |
------------+-------------+-------+---------+
  FF8957    |   509010    |   1   | Berlin  |

EDIT 3:

[OverflowException: Die arithmetische Operation hat einen Überlauf verursacht.] System.Data.Odbc.OdbcDataReader.GetSqlType(Int32 i) +359 System.Data.Odbc.OdbcDataReader.GetValue(Int32 i) +57 System.Data.Odbc.DbCache.AccessIndex(Int32 i) +82
System.Data.Odbc.OdbcDataReader.GetValue(Int32 i) +38
Produktionscockpit.SQL.FilemakerController.getActualSalesOrders(Int32 numberOfOrders) in c:\Dev\ProductionCockpit\Produktionscockpit\SQL\FilemakerController.cs:56 Produktionscockpit.Home.addSalesOrderTabelContent() in c:\Dev\ProductionCockpit\Produktionscockpit\default.aspx.cs:79
Produktionscockpit.Home.Page_Load(Object sender, EventArgs e) in c:\Dev\ProductionCockpit\Produktionscockpit\default.aspx.cs:21
System.Web.UI.Control.LoadRecursive() +116
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2910

Nations

What does your SalesOrder class look like? Additionally what data type is your Aufttrags Nr. column? If it's larger than Int32.Max, you might need to explicitly convert it to something like a long prior to storing it. – Unsuccess

@RionWilliams Thanks for your fast response, I editet my post – Nations

No problem. Can you elaborate on what datatype your Aufttrags Nr. column is? – Unsuccess

@RionWilliams Its type is text – Nations

Does OdbcDataReader have similar methods as for instance SqlDataReader? If so, what happens if you try to use reader.GetString(0) (assuming Auftrags Nr. is the first column)? This is not an answer but, possibly, a workaround... – Tightwad

Answer 1

I was reading a bit about this, and there's a problem with with connecting between 32-bit and 64-bit. Is your application being compiled for x86? Assuming you are using a 32-bit ODBC data source.

From what I see in other people with the same problem (I never experienced this) you can fix it by either moving your application to 32 bits (if the data source is 32 bits) or someone also uninstalled the 64-bit version of filemaker and installed the 32-bit.

It's likely this is the problem, but I can't really give you a proper solution since I don't know your environment.

Boost

Thanks for your answer! It looks like this is going into the right direction. I just recognized, that my IIS is using the 64 Bit driver and my Visual Studio is using the 32 Bit driver. I changed everything to 64bit. The error is stil the same, but now also in Visual Studio. That makes the debugging a bit more easy. – Nations

Use the 32-bit driver and a 32-bit application pool in IIS link – Handyman

You were right. Our Sysadmin deactivated 32bit mode in IIS. The IIS always used the 64bit driver. Thanks your hint brought me into the right direction! – Nations

If connecting to an Informix database, IBM list this as the issue. – Frit

Answer 2

If you are receiving an ArithmeticOverflow, it sounds like .NET is having trouble determining the type of your Aufttrags Nr. column and is guessing incorrectly.

If this column is a Number then there are a wide range of .NET data types that it could map to :

You might consider trying to read it as an Int64 via the Convert.ToInt64() method explicitly before outputting it as a string :

s.abNR = Convert.ToInt64(reader["Auftrags Nr."]).ToString();

Or if you expect the value to be a floating-point number, you could try using a Decimal via the Convert.ToDecimal() method :

s.abNR = Convert.ToDecimal(reader["Auftrags Nr."]).ToString();

With Regards to Connecting

Since the title of your question explicitly asks about connecting to a FileMaker database, you can check to ensure that your connection string is correct here and an example Filemaker Pro connection string below :

Driver=FileMaker Pro;AllAsText=0;ApplicationUsingThreads=1;FetchChunkSize=100;
FileOpenCache=0;IntlSort=0;MaxTextlength=255;ServerAddress=127.0.0.1;
TranslationOption=0;UseRemoteConnection=1;

Although it seems like your primary issues aren't related to connectivity.

Unsuccess

Thanks for your fast Response! I tried both of your suggestions. While its working on my local pc with the visual studio webserver, its still didn't working on the webserver I'm using. The error is still the same. The error appears for each of the four lines I'm trying to read the data. Also thanks for your advice with the titel, something went wrong there, I should change that. – Nations

By using your connection string adive i get an OdbcException wich is saying: "ERROR [08S01] [FileMaker][FileMaker] Failed to connect to listener (3)" – Nations

Do you have any sample data for what your table looks like? If the value is being stored as text, then there shouldn't be any conversions / arithmetic overflow issues. It seems like something else might be wrong, consider posting your entire code as well as some sample data, which might help. – Unsuccess

I addet sample data to my post in EDIT 2. Also checked the datatype of those fields, and they are all Filemaker text. – Nations

And its still the same problem, its working out of Visual Stuido on my computer but not on the IIS running on my webserver.... – Nations

If it works locally but not when deployed on IIS, that would make me inclined to believe there was an issue with connecting to the database itself, but that isn't what your error indicates. Have you checked the Event Viewer to see if you can get any more information like a StackTrace on the error in question? – Unsuccess

I addet the StackTrace to my post, see Edit 3. – Nations

Answer 3

The stack trace System.Data.Odbc.OdbcDataReader.GetSqlType(Int32 i) you provided drives me to conclusion that the problem is that the TEXT type does recognized by the ODBC GetSqlType. The difference in behaviour may be explained by different version of your ODBC driver or it default settings. Check the version and the settings in both environments.

As a work around you may try to CAST your fields to some known types. For example,

SELECT CAST(C.[Aufrags Nr.] as NVARCHAR(MAX)) as [Aufrags Nr.], .... FROM CAR C

Choose appropriate column type depending on what SQL server (or the ODBC source) you query.

Wallacewallach

Thanks for your answer! But as soon as I'm using this, I get an OdbcException wich says "ERROR [42000] [FileMaker][FileMaker] FQL0001/(1:13): There is an error in the syntax of the query." – Nations

Answer 4

I was having almost similar problem once and after trying lot of things like converting to long etc etc, this finally solved my problem although I am not sure how it solved?

abNR = reader["Auftrags Nr."] + "";

I replaced ToString() with + "";

Rashida

Thank you for your answer, but it doesn't work for me, the error still keeps the same.... – Nations

Answer 5

Having some problems with 64-bit ODBC DSN I've just created 32-bit DSN on 64-bit Windows by running C:\WINDOWS\SYSWOW64\odbc32.exe instead of default ODBC tool. Worked fine.

Herzen

Thanks for your answer! How can I make the 64Bit IIS use the 32 bit FMODBC-Driver and DSN? Like that my application cant find the dsn – Nations

IIS configuration is beyond my experience, sorry. Mine was desktop application, Mapinfo. I only noted that on my desktop all User DSNs are listed in registry all together under HKEY_CURRENT_USER\Software\ODBC\ODBC.INI while system DSNs use different keys for 32-bit and 64-bit. Probably you need user DSN under IIS account. – Herzen

Answer 6

There might be a problem with your column naming convention: for that change your query to this

command.CommandText = "SELECT *, [Auftrags Nr.] as AuftragsNr, [Artikel nr.] as ArtikelNr FROM CAR";

//Create new SqlDataReader object and read data from the command.
using (OdbcDataReader reader = command.ExecuteReader())
{

int counter = 0;
while (reader.Read() && counter < numberOfOrders)
{
    SalesOrder s = new SalesOrder();
    s.abNR = reader["AuftragsNr"].ToString();
    s.artNr = reader["ArtikelNr"].ToString();
    s.quantity = reader["Menge"].ToString();
    s.city_country_Coustomer = reader["Stadt"].ToString();
}
}

Emilio

Answer 7

The ODBC driver determined the data type of the column called AuftragsNr as int and fails on the 3rd line. Try to use the GetString method.

Redo

Answer 8

Our workaround was to set the "OdbcDataAdapte"-property "MissingSchemaAction" to "MissingSchemaAction.Add" (instead of "MissingSchemaAction.AddWithKey").

Quaternity