Invalid cast for OleDbDataReaderGetInt16

[Sonnich Jensen]

Hi all

I use this to get the next ID for a table, in and old system. The
problem is that when the table is empty it does not work, just
crashes. I read around and found that it is a common problem, but I
did not find a solution for my need.
It crashes when the table is EMPTY. In that case I can understand that
max(id) could have some odd value (should be 0 by default in eg
Delphi). Therefore I added count(id) which in any case should have a
value - it is an Int32 - but it crashes at GetInt32 - so I went on for
another solution.

Next: dr.IsDBNull(0) - crashes too and the very line.

GetOrdinal fails too, at GetInt.

Now I am out of ideas. The code below is as it looks right now.
Oh and the DB is an access file.

public int GetNextID(string Table)
{
OleDbCommand com = new OleDbCommand();
com.Connection = con;
com.CommandText = "select max(id) as x, count(id) as y
from " + Table;
OleDbDataReader dr = com.ExecuteReader();

//if (dr.IsDBNull(0) ) // fails too
// return 1;

int a = dr.GetOrdinal("x"); // does no change

if (dr.Read())
{
int i = GetInteger(dr, a);
dr.Close();
return ++i;
}
else
{
dr.Close();
return 1;
}
}


int GetInteger(OleDbDataReader dr)
{
return GetInteger(dr, 0);
}
int GetInteger(OleDbDataReader dr, int FieldNo)
{
int i = 0;
if (dr.GetFieldType(FieldNo) == typeof(Byte))
i = dr.GetByte(FieldNo);
else if (dr.GetFieldType(FieldNo) == typeof(Int16))
i = dr.GetInt16(FieldNo);
else if (dr.GetFieldType(FieldNo) == typeof(Int32))
i = dr.GetInt32(FieldNo);
else
throw new Exception("Unhandled integer type: " +
dr.GetFieldType(FieldNo));
return i;
}

 

[James A. Fortune]

Hi all

I use this to get the next ID for a table, in and old system. The
problem is that when the table is empty it does not work, just
crashes. I read around and found that it is a common problem, but I
did not find a solution for my need.
It crashes when the table is EMPTY. In that case I can understand that
max(id) could have some odd value (should be 0 by default in eg
Delphi). Therefore I added count(id) which in any case should have a
value - it is an Int32 - but it crashes at GetInt32 - so I went on for
another solution.

Next: dr.IsDBNull(0)  - crashes too and the very line.

GetOrdinal fails too, at GetInt.

Now I am out of ideas. The code below is as it looks right now.
Oh and the DB is an access file.

public int GetNextID(string Table)
        {
            OleDbCommand com = new OleDbCommand();
            com.Connection = con;
            com.CommandText = "select max(id) as x, count(id) as y
from " + Table;

Try:

select IIF(max(id) is not null, max(id), 0) AS x, ...

Then when the table has no records, x will return 0.

James A. Fortune
(e-mail address removed)

The name Fortune literally means "One of the Britons (or Barat)" -- L.
A. Waddell

 

[Arne Vajhøj]

I use this to get the next ID for a table, in and old system. The
problem is that when the table is empty it does not work, just
crashes. I read around and found that it is a common problem, but I
did not find a solution for my need.
It crashes when the table is EMPTY. In that case I can understand that
max(id) could have some odd value (should be 0 by default in eg
Delphi). Therefore I added count(id) which in any case should have a
value - it is an Int32 - but it crashes at GetInt32 - so I went on for
another solution.

First: using SELECT MAX(id) to gt the next id is generally not a
good idea due to concurrency problems.

Second: it would be easier to troubleshoot if you told us what
"crashes" and "fails" actually means by giving us the complete
exception text.

Third: COUNT() may return int but it may also return long or decimal
all depending on what the database is. If you told us what database
you were using then we could tell you what type it should be.

Fourth: I would expect MAX() to return NULL if no rows, but again it
would help to know what database.

Next: dr.IsDBNull(0) - crashes too and the very line.

GetOrdinal fails too, at GetInt.

Now I am out of ideas. The code below is as it looks right now.
Oh and the DB is an access file.

public int GetNextID(string Table)
{
OleDbCommand com = new OleDbCommand();
com.Connection = con;
com.CommandText = "select max(id) as x, count(id) as y
from " + Table;
OleDbDataReader dr = com.ExecuteReader();

//if (dr.IsDBNull(0) ) // fails too
// return 1;

int a = dr.GetOrdinal("x"); // does no change

if (dr.Read())
{
int i = GetInteger(dr, a);
dr.Close();
return ++i;
}
else
{
dr.Close();
return 1;
}
}

int GetInteger(OleDbDataReader dr, int FieldNo)
{
int i = 0;
if (dr.GetFieldType(FieldNo) == typeof(Byte))
i = dr.GetByte(FieldNo);
else if (dr.GetFieldType(FieldNo) == typeof(Int16))
i = dr.GetInt16(FieldNo);
else if (dr.GetFieldType(FieldNo) == typeof(Int32))
i = dr.GetInt32(FieldNo);
else
throw new Exception("Unhandled integer type: " +
dr.GetFieldType(FieldNo));
return i;
}

Arne

 

[James A. Fortune]

First: using SELECT MAX(id) to gt the next id is generally not a
good idea due to concurrency problems.

Your warning is valid. However, sometimes an autogenerated primary
key cannot be used to assign id's or cannot do it in the way that you
expect. For example, some databases require the autogenerated primary
keys to be generated randomly for performance. Autogenerated primary
keys really only guarantee uniqueness, not a contiguous set of
numbers. Still, I use autogenerated primary keys to handle most
concurrency situations. Other situations, such as encountering append
errors, are capable of causing autogenerated primary key values to
skip. If you need a contiguous set of numbers for, say, purchase
order serial numbers (POSN's), using a Max(id) might help ensure that
no numbers are skipped. Auditors absolutely hate skipped P.O.
numbers . One idea for making the Max(id) idea more concurrent is
to check to see if someone else has added an id while you've owned it
immediately before adding the supposedly new id. That way, you'll
only get a key violation database error if someone adds your id in the
few milliseconds after the check. Since I program in an environment
with lots of users hitting database records concurrently, to minimize
even that possibility, I came up with a two step system to ensure
unique and contiguous POSN's nearly all the time. When I grab a new
POSN, I put it in a table and mark a Taken field. So the only way for
a duplicate POSN to be attempted is for two users to grab a POSN
within a few milliseconds of each other, do their editing, then try to
post the P.O. data within a few milliseconds of the other P.O. chosen
at about the same time. Ultimately, the database can still enforce
that no duplicate POSN's get added to the table, but almost never has
to attempt adding duplicates. Note that the checks are made at both
the reservation and post stages. Any POSN's that are abandoned are
appended to another table (pool) the next day and made available for
use before resorting to the Max(id) idea to preserve contiguity. If
the OP simply didn't consider the possibility of concurrency, then I
add my voice to yours in warning.

James A. Fortune
(e-mail address removed)

 

[Arne Vajhøj]

Your warning is valid. However, sometimes an autogenerated primary
key cannot be used to assign id's or cannot do it in the way that you
expect. For example, some databases require the autogenerated primary
keys to be generated randomly for performance. Autogenerated primary
keys really only guarantee uniqueness, not a contiguous set of
numbers. Still, I use autogenerated primary keys to handle most
concurrency situations. Other situations, such as encountering append
errors, are capable of causing autogenerated primary key values to
skip. If you need a contiguous set of numbers for, say, purchase
order serial numbers (POSN's), using a Max(id) might help ensure that
no numbers are skipped. Auditors absolutely hate skipped P.O.
numbers . One idea for making the Max(id) idea more concurrent is
to check to see if someone else has added an id while you've owned it
immediately before adding the supposedly new id. That way, you'll
only get a key violation database error if someone adds your id in the
few milliseconds after the check. Since I program in an environment
with lots of users hitting database records concurrently, to minimize
even that possibility, I came up with a two step system to ensure
unique and contiguous POSN's nearly all the time. When I grab a new
POSN, I put it in a table and mark a Taken field. So the only way for
a duplicate POSN to be attempted is for two users to grab a POSN
within a few milliseconds of each other, do their editing, then try to
post the P.O. data within a few milliseconds of the other P.O. chosen
at about the same time. Ultimately, the database can still enforce
that no duplicate POSN's get added to the table, but almost never has
to attempt adding duplicates. Note that the checks are made at both
the reservation and post stages. Any POSN's that are abandoned are
appended to another table (pool) the next day and made available for
use before resorting to the Max(id) idea to preserve contiguity. If
the OP simply didn't consider the possibility of concurrency, then I
add my voice to yours in warning.

Fair enough.

It is still not obvious to me why you do that instead of a
list of holes and simple auto generated keys.

But I don't know you app nor your database, so that does not mean much.

Arne

 

[Sonnich Jensen]

First: using SELECT MAX(id) to gt the next id is generally not a
good idea due to concurrency problems.

Second: it would be easier to troubleshoot if you told us what
"crashes" and "fails" actually means by giving us the complete
exception text.

Third: COUNT() may return int but it may also return long or decimal
all depending on what the database is. If you told us what database
you were using then we could tell you what type it should be.

Fourth: I would expect MAX() to return NULL if no rows, but again it
would help to know what database.

I write that it is an (MS) Access database. The reason for not using
autoincrement was made long ago (as I wrote it is an OLD system),
because the system needs to work on whatever database the user
chooses. Therefore certain features are not used.
As this is an old and running system I have limited options, as the
design IS GIVEN. It was not my choice to do so, but I have to continue
it.

People here tend to ask why we do so - often, this is given and cannot
easily be changed, therefore we use the existing DB and need to find a
proper solution.

The IIF solution is one that I have thought of my self, as AFAIK this
system is only in use on Access databases, I have not seen it on other
even that it was planned so.

I agree that MAX will return something odd when there is no data, but
that does not explain why count() does not work. I added it in hope
that it at least would return 0 in case of an empty table. It does
not, it gives a casting error (I only get this error) when reading it
even detecting it as INT32). However it works, if I read only count.
That made me change the order, so reading count(id), max(id) actually
works,

Therefore this works:

public int GetNextID(string Table)
{
OleDbCommand com = new OleDbCommand();
com.Connection = con;
com.CommandText = "select count(id) as x, max(id) as y
from " + Table;
OleDbDataReader dr = com.ExecuteReader();
if (dr.Read())
{
int i = GetInteger(dr);
if (i != 0)
{
i = GetInteger(dr, 1);
dr.Close();
return ++i;
}
}
dr.Close();
return 1;
}

 

[Arne Vajhøj]

I write that it is an (MS) Access database. The reason for not using
autoincrement was made long ago (as I wrote it is an OLD system),
because the system needs to work on whatever database the user
chooses. Therefore certain features are not used.
As this is an old and running system I have limited options, as the
design IS GIVEN. It was not my choice to do so, but I have to continue
it.

That is a common scenario.

People here tend to ask why we do so - often, this is given and cannot
easily be changed, therefore we use the existing DB and need to find a
proper solution.

Even if it is not so relevant for the original poster, then trying to
prevent readers from ending up with similar solutions is still
a good thing.

I agree that MAX will return something odd when there is no data, but
that does not explain why count() does not work. I added it in hope
that it at least would return 0 in case of an empty table. It does
not, it gives a casting error (I only get this error) when reading it
even detecting it as INT32). However it works, if I read only count.
That made me change the order, so reading count(id), max(id) actually
works,

Therefore this works:

public int GetNextID(string Table)
{
OleDbCommand com = new OleDbCommand();
com.Connection = con;
com.CommandText = "select count(id) as x, max(id) as y
from " + Table;
OleDbDataReader dr = com.ExecuteReader();
if (dr.Read())
{
int i = GetInteger(dr);
if (i != 0)
{
i = GetInteger(dr, 1);
dr.Close();
return ++i;
}
}
dr.Close();
return 1;
}

If it works then you are all set.

Without the exact error and the matching code, then we will never
find out why it did not work before.

Arne

 

[James A. Fortune]

Fair enough.

It is still not obvious to me why you do that instead of a
list of holes and simple auto generated keys.

But I don't know you app nor your database, so that does not mean much.

That's not a bad idea. I tried that already. Querying for holes is
quite simple. The database is MS-Access. Access is more prone to
database corruption than SQL Server and requires a gentler approach.
But that was many years ago on a much slower network. Also, Access
has taught me not to rely on the error handling safety net as a first
resort. That practice has resulted in what I feel is better code, so
I continue that practice as much as possible, even in C#, contrary to
the popular style of many. SQL Server, contrary to popular opinion,
is also quite capable of corrupting data. Given the effort involved
in finding and fixing corrupted records, I feel that extra care with
even SQL Server doesn't hurt. Another factor in the implementation
decision was that the P.O. had to be viewed (including the P.O.
number) before the decision to post. Until the record is created,
there is no primary key assigned. I know that I could have created a
placeholder record anyway and posted the data later, whether from that
P.O. or from a later one if it got cancelled, but decided against
implementing it that way, mostly because I actually used a unique
index for the P.O. number instead of using it as the primary key ,
and partly for aesthetics! So it really came down to a schema
choice. But thanks for trying to suggest a way improve things.

James A. Fortune
(e-mail address removed)