Getting System.InvalidCastException when using OleDbDataReader.GetInt64() after fetching Long from MS Access database

Question

I've been working on a project (with Visual Studio 2013) in which I need to retrieve information from a MS Access 2007 database stored locally. I'm using OleDb to deal with the connection at the moment. The database has a single table, with several fields. I am trying to retrieve the value from SID - which is the primary key, set to Auto-Number as a Long Integer from within Access.

Here's my problem: when the OleDbDataReader has finished executing and I try to retrieve the result (using the GetInt64 method) I get this error:

An unhandled exception of type 'System.InvalidCastException' occurred in System.Data.dll

Additional information: Specified cast is not valid.

This happens regardless of whether I'm assigning the result to a value or not. Changing the call to GetInt32 makes it work, but I've no idea why!

Could anybody shed light on why this is the case?

I've searched extensively on here and elsewhere, mostly they suggest the field type is not set to Long Integer in Access, but mine is already, so I don't think that's the issue. None of the other solutions seem to apply or work.

I've extracted the problem code from my main application, and stripped away all non-essential code, and still get the error, here is the simple version:

        // Set up connection and test query
        OleDbConnection connection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Users\\Combobulator\\Desktop\\Testing Databases\\Reagents Database\\Reagents.mdb");
        string query = "SELECT SID FROM MolTable WHERE [Chemical Name] = 'Acetyl Chloride'";
        
        OleDbCommand command = new OleDbCommand(query, connection);

        try {
            connection.Open();
            OleDbDataReader reader = command.ExecuteReader();

            if (!reader.HasRows) {
                System.Console.WriteLine("Returned no rows.");
                Environment.Exit(-1);
            }

            // Find all matching entries
            while (reader.Read()) {
               reader.GetInt64(0);   // This is where the error is thrown
            }

            // Close the reader and connection
            reader.Close();
        } catch (OleDbException e) {
            System.Console.WriteLine("Error: " + e.Errors[0].Message);
        } finally {
            connection.Close();
        }

I'm totally stumped, this is the first time I've had to ask online for help.

Thanks for taking the time to read!

EDIT: I forgot to mention, this isn't just me confusing the length of Long Integer in Access is it? I assume that it's 64-bit, please correct me if I'm wrong.

what if you try calling this with the actual field name (Int64)reader["SID"]; also when you are stepping through the code assuming you are using the debugger, what is the value of the reader object after you call the ExecuteReader() method.. are you sure it's even returning data..? are you sure the datatype is of Int64 or is it a string for the datatype..? what if you try it with GetInt32() method or try what @JonSkeet has suggested — MethodMan
– MethodMan, Commented Mar 25, 2015 at 19:47
@Jon It returns the same as with GetInt32- the value I'm looking for, which in this case is just 13. Casting it to long doesn't work, casting to Int does. I can work with 32-bits if I have to, but I really want to figure out what's going on here! Thanks for the fast reply :) — PaleSaint
– PaleSaint, Commented Mar 25, 2015 at 19:51
Ah, I misread the question. I thought that GetInt32 didn't work. — Jon Skeet
– Jon Skeet, Commented Mar 25, 2015 at 19:52
@MethodMan Calling with the field name gives the same error as above. The reader object value is System.Data.OleDb.OleDbDataReader after the execute call - gyazo.com/431e25a6ee3e08426b76d4d2980a5168 here is a picture of the debugger immediately after the execute call. GetInt32 works fine still. — PaleSaint
– PaleSaint, Commented Mar 25, 2015 at 20:02

Jon Skeet · Accepted Answer · 2015-03-25 20:51:00Z

3

I forgot to mention, this isn't just me confusing the length of Long Integer in Access is it? I assume that it's 64-bit, please correct me if I'm wrong.

Yup, I think that's exactly the problem. For example, from "Field types in MS Access":

Integers in Access come in 1, 2 and 4 byte varieties. The single byte number is named Byte (Range 0-255), the two-byte number is named Integer (-32768 to 32767) and then there is the Long Integer (-2 billion to 2 billion).

(This site agrees.)

MS documentation is thin on the ground, but HansUp found this "Introduction to data types and field properties" that includes:

Field Size

...

Long Integer — Use for integers that range from -2,147,483,648 to 2,147,483,647. Storage requirement is 4 bytes.

edited Mar 25, 2015 at 20:51

answered Mar 25, 2015 at 19:53

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

PaleSaint Over a year ago

Aha! Can't believe I missed that, looks like I need to learn to love the Int32! Much appreciated, thanks!

HansUp Over a year ago

If you still want a Microsoft link, here is one

Collectives™ on Stack Overflow

Getting System.InvalidCastException when using OleDbDataReader.GetInt64() after fetching Long from MS Access database

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related