Unable to cast object of type 'System.DBNull' to type 'System.String' in C#

Question

Good day Guys,

Trying to generate automatic Transaction ID. When there is an existing first record or more than one, the code below works fine. But if the table does not contain any data, it throws the error "Unable to cast object of type 'System.DBNull' to type 'System.String'."

Below is the code. Assist Please.

 public string TransactionID()
    {
        string connString = CommonVariables.ConnectionString;
        string sql = "SELECT MAX(Transaction_ID) FROM tbl_Transactions";
        using (SqlConnection cnn = new SqlConnection(connString))
        {
            cnn.Open();
            using (SqlCommand cmd = new SqlCommand(sql, cnn))
            {
                string max_ID = (string)cmd.ExecuteScalar();
                if (max_ID == null || Convert.IsDBNull(max_ID.ToString()))
                {
                    Transaction_ID = "17900000000001";
                    return Transaction_ID;
                    // MessageBox.Show("Maximum ID does not Exist", "Customer Exist", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                }
                else
                {

                    double newTransaction_ID = double.Parse(max_ID) + 1;
                    Transaction_ID = newTransaction_ID.ToString();
                    return Transaction_ID;
                    //MessageBox.Show("Maximum ID Exist, It is  " + max_ID, "Customer Exist", MessageBoxButtons.OK, MessageBoxIcon.Warning);

                }
            }

        }

    }

Does this answer your question? [Unable to cast object of type 'System.DBNull' to type 'System.String\`](https://stackoverflow.com/questions/870697/unable-to-cast-object-of-type-system-dbnull-to-type-system-string) — Max Zolotenko, Jan 29 '20 at 12:45
@МаксимЗолотенко, nope, it did not solve the problem. I am getting An object referenced is non static — mantics, Jan 29 '20 at 13:41
@mantics In if block you have not declared Transaction_ID and assigning it the value only. — Suraj Kumar, Jan 29 '20 at 14:15

Suraj Kumar · Accepted Answer · 2020-01-29T14:21:58.520

0

You can simply change the below line. You can return the default values like empty string or zero from your query in the select statement so the below line will not give the error.

SELECT MAX(IsNull(Transaction_ID, 0)) FROM tbl_Transactions

You need to check why the value of max(Transaction_ID) is not returning a value. It may be due the table has not any record.

string max_ID = (string)cmd.ExecuteScalar();

You can also use Convert.ToString(cmd.ExecuteScalar()) which will handle the null value exception.

if (max_ID == null || Convert.IsDBNull(max_ID.ToString()))

To

if (String.IsNullOrEmpty(max_ID))
{
  //Your if block statement.
}
else
{
  //Your else block statement.
}

It is because if max_ID will be null in that case max_ID.ToString() will give error.

edited Jan 29 '20 at 14:21

answered Jan 29 '20 at 13:15

Suraj Kumar

5,547
8
20
42

The exception is here: `(string)cmd.ExecuteScalar()`. – Johnathan Barclay Jan 29 '20 at 13:16
@JohnathanBarclay Updated the answer for that part also. Thanks. – Suraj Kumar Jan 29 '20 at 13:21
Requesting to retract the down vote as per the updated answer. Thanks. – Suraj Kumar Jan 29 '20 at 13:34
2

Mine has been retracted. – Johnathan Barclay Jan 29 '20 at 13:37
1

This solved the problem – mantics Jan 29 '20 at 15:06
Does it mean i cannot cast the executescalar like that? – mantics Jan 29 '20 at 15:06
@mantics Thanks to letting me know. – Suraj Kumar Jan 29 '20 at 15:06
@mantics You can cast but if the null value is output then you can not. You can see the documentation where newProdID has been cast into the int. https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.executescalar?view=netframework-4.8 – Suraj Kumar Jan 29 '20 at 15:07
1

@SurajKumar, thanks – mantics Jan 29 '20 at 15:08
@mantics You are welcome. – Suraj Kumar Jan 29 '20 at 15:09

score -3 · Answer 2 · answered Jan 29 '20 at 12:46

-3

Don't use string for variable max_ID, use object instead.

answered Jan 29 '20 at 12:46

j03p

321
3
11

Unable to cast object of type 'System.DBNull' to type 'System.String' in C#

2 Answers2