The object cannot be attributed from DBNull to other types. Error Reading Reader Zero

Question

int topID = 0; string TopIDQuery = "Select TopID from tbl_Organisation where OrganisationID=@OrgID"; paramet[0] = new MySqlParameter("@OrgID", MySqlDbType.Int32); paramet[0].Value = OrgID; reader = server.ExecuteReader(CommandType.Text, TopIDQuery, paramet); while (reader.Read()) { topID = Convert.ToInt32(reader["TopID"]); } reader.Close(); 

I read topID from table when topID is null. I want to leave topID as 0, but since it is null, it throws an error, how can I handle this error when topID is null

Answer 1

Change the reading code to:

 while (reader.Read()) { if(reader.IsDBNull(reader.GetOrdinal("TopID"))) topID = 0; else topID = Convert.ToInt32(reader["TopID"]); } 

Answer 2

Call IsDBNull() reader to check the column before trying to convert it:

  using (reader = server.ExecuteReader(CommandType.Text, TopIDQuery, paramet)) { while (reader.Read()) { var column = reader.GetOrdinal("TopID"); if (!reader.IsDBNull(column)) topID = Convert.ToInt32(reader[column]); } } } 

Or compare with DBNull.Value :

  var value = reader["TopID"]; if (value != DBNull.Value) { topID = Convert.ToInt32(value); } 

Answer 3

Use test:

 int columnNr = reader.GetOrdinal("TopID"); if (!reader.IsDBNull(columnNr)) { topID = reader.GetInt32(columnNr); } 

Answer 4

The problem is that you are trying to set an int with a null value and not in read. you can do this to set an alternate value if the one you are trying to enter is null:

  topID = Convert.ToInt32(reader["TopID"]) ?? 0; 

Another alternative is that you can use integers with a null value by initializing topID as follows:

 int? topID = 0 

but this will require checking the null value elsewhere, as well as some other things that need to be handled, so I would recommend using a double question mark in your value, as I showed at the beginning.

Answer 5

You need to check if the value is DBNull before trying to restore it. In addition, I recommend using GetInt32() over Convert.ToInt32() because it has been stated that the internal optimization of GetInt32() can change where it will be faster than using Convert.ToInt32() . I found out about this a long time ago and you do not have a help article for you. At the time I found out that this GetInt32() internally uses Convert.ToInt32()

 while(reader.Read()) { int TopIDIndex = reader.GetOrdinal("TopID"); topID = reader.IsDBNull(TopIDIndex) ? 0 : reader.GetInt32(TopIDIndex); } 

Answer 6

I like to use DataTable , not DataReader . I hate repeating the pattern, so I added some extension methods for the DataRow class to encapsulate the downstream logic and make the code more readable:

 DataTable dt = ExecStoredProcedure() ; foreach ( DataRow dr in dt ) { int id = dr.CastAsInt("id") ; dateTime? dtLastUpdated = dr.CastAsDateTimeNullable("last_update_date") ; int? col3 = dr.CastASIntNullable(3) ; } 

Here is the code to lower the column value from DataRow to int / int? :

 #region downcast to int public static int CastAsInt( this DataRow row , int index ) { return toInt( row[index] ) ; } public static int CastAsInt( this DataRow row , string columnName ) { return toInt( row[columnName] ) ; } public static int? CastAsIntNullable( this DataRow row , int index ) { return toIntNullable( row[index] ); } public static int? CastAsIntNullable( this DataRow row , string columnName ) { return toIntNullable( row[columnName] ) ; } #region conversion helpers private static int toInt( object o ) { int value = (int)o; return value; } private static int? toIntNullable( object o ) { bool hasValue = !( o is DBNull ); int? value = ( hasValue ? (int?) o : (int?) null ) ; return value; } #endregion conversion helpers #endregion downcast to int 

I am sure that similar extension methods with DataReader will be quite simple.

Answer 7

Here is the code in which we Autogenerate any id from the ms access database on the page load of any registration form

 void Number() { con.Open(); int id; cmd = new OleDbCommand("select max(ID) from Entry", con); string value = (cmd.ExecuteScalar().ToString()); string max; if (value != null) { max = "0"+value; id = Convert.ToInt32(max); lblNumber.Text = "Acronym of Reg form like for Emp for Employee" + Convert.ToString((id + 1)); } con.Close(); } 

Answer 8

Use a generic extension method for your reader (see a similar SO question )

 topID = reader.GetFieldValueOrDefaultIfDBNull<Int32>("TopID"); 

Inject this extension method into the reader class (MySql, Sql, Oracle, etc.)

 public static T GetFieldValueOrDefaultIfDBNull<T>(this SqlDataReader reader, string indexName) { if (reader.IsDBNull(reader.GetOrdinal(indexName))) { return default(T); // returns the default value for the type } else { return reader.GetFieldValue<T>(reader.GetOrdinal(indexName)); } }