S
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;
}
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;
}