How to convert DBNull into another type using C#?

Question

I'm trying to write a code, which checks if a cell in selected row in DataGridView is empty/null before the cell will be updated with a value. The code, which I wrote, doesn't work, as I get an error:

System.InvalidCastException: An object of type 'System.DBNull' cannot be converted to the 'System.String' type

I have tried it with this code:

if ((string)dataGridView1.SelectedCells[0].OwningRow.Cells[1].Value == null)
{
    try
    {
        String ConnectionString = @"Data Source=.\SQLEXPRESS01;Initial Catalog=Vagtplan;Integrated Security=True";
        SqlConnection myconnection = new SqlConnection(ConnectionString);
        myconnection.Open();
        DateTime primaryKey = Convert.ToDateTime(dataGridView1.SelectedRows[0].Cells[0].Value);
        SqlCommand AddNumbeCommand = myconnection.CreateCommand();
        AddNumbeCommand.CommandText = "UPDATE dbo.Vagter SET [ansatID] = @ansatID WHERE [Dato] = @dato";
        AddNumbeCommand.Parameters.AddWithValue("@ansatID", SqlDbType.Int).Value = textBox1.Text;
        AddNumbeCommand.Parameters.Add("@dato", SqlDbType.DateTime).Value = primaryKey;
        AddNumbeCommand.ExecuteNonQuery();
        myconnection.Close();
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
    finally
    {
        MessageBox.Show("The cell is updated.");
    }
}
else
{
    MessageBox.Show("The cell has already a value.");
}

The expected result is that when a user select in DataGridView a row where cell under column ansatID already has a value, write a value in textBox1, and press on ''Tilføj ansatID til vagten'', the he get error:"There is already a value in the cell.". If he will select a row where cell under column ansatID is empty, write a value in textBox1, and press on ''Tilføj ansatID til vagten'', then the SQL query will be executed and he gets message "The cell is updated." Moreover, the column ansatID is of data type "int". This is also shown on following picture:

Using databinding and a DataAdapter you can do without all that code - one line and it will open a connection and update the db. I am pretty sure that `AddWithValue` is wrong and/or will fail or is not what you meant — Ňɏssa Pøngjǣrdenlarp, May 06 '19 at 19:11
Where is it trying to convert DBNull.Value to a string? Which statement is it? — Scott Hannen, May 06 '19 at 19:14
[How to use DataAdapter to update Database with values from DataGridView](http://csharp.net-informations.com/dataadapter/datagridview-sqlserver.htm) — Harald Coppoolse, May 06 '19 at 19:56

score 1 · Accepted Answer · answered May 06 '19 at 19:55

1

(string)dataGridView1.SelectedCells[0].OwningRow.Cells[1].Value == null

DBNull cannot be converted to any datatype directly so you would need to check if it is DBNull before doing the conversion

something like

if (dataGridView1.SelectedCells[0].OwningRow.Cells[1].Value == DBNull.Value)
//dosomething
else
//do something else

Or depending on how you read it from the database you can use the extension method to save yourself some typing

dataRow.Field<string>("Column Name")

answered May 06 '19 at 19:55

Steve

11,696
7
43
81

if (dataGridView1.SelectedCells[0].OwningRow.Cells[1].Value == DBNull.Value) was exactly what I needed and it works now, thank you :) – Paweł Żelazny May 06 '19 at 20:23

score 0 · Answer 2 · answered May 06 '19 at 19:58

To answer your question, it looks like DBNull can be converted in two ways:

First, as you can see, DBNull can be converted to a string using DBNull.ToString(), which just returns an empty string.

Second, since DBNull implements the IConvertible interface, you can use DBNull.ToType(Type, IFormatProvider) to convert to another type by providing an implementation of the IFormatProvider interface.

In your case, the only place I see you converting the cell value to a string is in the if condition.

You're using it to determine whether or not the cell has a value or not. If that's all you want to do, you would probably be better served by the answer here: How to check empty and null cells in datagridview using C#

I got solution already, it was just if (dataGridView1.SelectedCells[0].OwningRow.Cells[1].Value == DBNull.Value) :) — Paweł Żelazny, May 06 '19 at 20:46

How to convert DBNull into another type using C#?

2 Answers2