exception : The value for column is DBNull.

[da_cobra]

I'm writing a database in Visual C sharp express and came upon this small problem.

I've just added a new column in my table, but now when I try to retrieve the data from the previous rows (where that column didn't existed yet and thus the value is dbNull) I get the following error :

The value for column 'MachineCable' in table 'tblMachines' is DBNull.

I understand why I get this exception thrown, but I wonder how I can ignore it or "fix" this little problem?
The property for that column is "accept nulls", so why do I get this exception, anyway? (dbNull <> Null?)

The solution now for me is to go through all the existing rows and change the value of the new column so it's not dbNull anymore.

[Rudegar]

you can check if the value is dbnull before you try to read it

or you can just create the column with a default value of "" or something

if it's a string you can use tostring() rather then reading the string
tostring return "" rather then dbnull if the value is dbnull

[da_cobra]

thx alot for the help

by adding the ".tostring()" the problem is solved!

[Tron]

You can also do something like this:


using( SqlConnectionProxy conn = connectionManager.NewSqlConnectionProxy() )
{
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "usp_MyProcedure";

cmd.Parameters.Add( "@Id", SqlDbType.Int );
cmd.Parameters["@Id"].Value = nId;

using( SqlDataReader rdr = conn.ExecuteReader(cmd) )
{
while( rdr.Read() )
{
myField = rdr.IsDBNull( 0 ) ? String.Empty : rdr.GetString( 0 );
}
rdr.Close();
}
}

[da_cobra]

it seems I still have the problem.
I thought it was solved but I already changed the values in my row to "" and therefore the ".tostring()" solution worked.

But now I added another column and again I get this error, because in the existing rows the new column has a default value of dbnull

I already also tried to compare my field to DBNull, but that doesn't work either :


if (row.InstallationElekConnection == DBNull)
showInstallationDlg.InstallationElekConnection = "";
else
showInstallationDlg.InstallationElekConnection = row.InstallationElekConnection.ToString();




Error 1 'System.DBNull' is a 'type' but is used like a 'variable'


the solution from Tron I can not use because I do not use a sqlDatareader (and do not really understand Tron's code either)

this also doesn't seem to work :( :

if (row.InstallationElekConnection != DBNull.Value)
{
MessageBox.Show("DBNull");
showInstallationDlg.InstallationElekConnection = "";
}
else
{
MessageBox.Show("not DBNull");
showInstallationDlg.InstallationElekConnection = row.InstallationElekConnection.ToString();
}

[JonnyPoet]

Hi !

Basically when reading data of a database you normally cannot be sure not to get a DBNull value in any of the rows, sometimes in different columns too. so what I normally do is something like
// ....
if (reader.HasRows == true) {
while (reader.Read()) {
billData = GetBillData(reader);
billDatas.Add(billData);
}
}
//...



private ServiceInvoiceGetBillData(OleDbDataReader reader) {
ServiceInvoice billData = new ServiceInvoice();
for (int j = 0; j < reader.VisibleFieldCount; j++) {
// checking for DBNull
if (!reader.IsDBNull(j)) {
switch (j) {
case 0:
billData.BillNo= reader.GetInt32(0);
break;
case 1:
billData.HoursTech = onvert.ToDecimal (reader.GetDouble(1));
break;
case 2:
billData.Costs = Convert.ToDecimal(reader.GetDouble(2));
break;
case 3:
billData.ServicesDone= Convert.ToDecimal(reader.GetDouble(3));
break;
case 4:
billData.TravelCosts = Convert.ToDecimal(reader.GetDouble(4));
break;
case 5:
//....
}
}
}
return billData;
}
This way I'm checking really every value before I'm adding it to a class in which I have all the data od this particular query where billDatas is a Collecion of all billData and billData are the data of one row in the datbase which in that case is one specific bill.