Unable to cast object of type 'System.DBNull' to type 'System.String'. Error [duplicate]

Question

I keep getting this error because the VendorID is null for that record. I've seen that this question has been asked a couple times and it seems like I should just do a check to see if null before it converts it and enters it in the dbtable, but I'm not sure how to do that.

public static PartOrder findPartOrder(string orderNo)
    {
        PartOrder aPartOrder = new PartOrder();
        OleDbDataAdapter myAdapter = new OleDbDataAdapter();
        if (aConnection.State == ConnectionState.Closed)
            aConnection.Open();
        OleDbCommand cmd = aConnection.CreateCommand();
        OleDbDataReader dbReader = null;
        cmd.CommandText = "SELECT * FROM PartOrder WHERE OrderNo = '" + orderNo + "'";
        dbReader = cmd.ExecuteReader();

        while (dbReader.Read())
        {
            aPartOrder.OrderNo = (string)dbReader.GetValue(0);
            aPartOrder.Length = (string)dbReader.GetValue(1);
            aPartOrder.Finish = (string)dbReader.GetValue(2);
            aPartOrder.Cost = (string)dbReader.GetValue(3);
            aPartOrder.PartDrawingNo = (string)dbReader.GetValue(4);
            aPartOrder.VendorId = (string)dbReader.GetValue(5);
            aPartOrder.ShopId = (string)dbReader.GetValue(6);
            aPartOrder.Completed = (string)dbReader.GetValue(7);
        }

        dbReader.Close();
        return aPartOrder;
    }

Again, the line "aPartOrder.VendorId = (string)dbReader.GetValue(5);" gets the error because VendorId is null.

Nikhil Agrawal · Accepted Answer · 2012-05-11 06:20:58Z

3

for this particular line try this:

aPartOrder.VendorId = dbReader.GetValue(5)==DBNull?"":dbReader.GetValue(5).value;

better would be to write a helper function:

private static string MyToString(object o)
{
    if(o == DBNull.Value || o == null) 
       return "";

    return o.ToString();
}

and use it:

aPartOrder.VendorId = MyToString(dbReader.GetValue(5));

edited May 11, 2012 at 6:20

Nikhil Agrawal

48.8k25 gold badges134 silver badges212 bronze badges

answered May 11, 2012 at 5:21

amantur

40.6k22 gold badges136 silver badges192 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nikhil Agrawal · Accepted Answer · 2012-05-11 05:21:36Z

1

One Line Answer

aPartOrder.VendorId = dbReader.IsDBNull(5) ? "" :(string)dbReader.GetValue(5);

answered May 11, 2012 at 5:21

Nikhil Agrawal

48.8k25 gold badges134 silver badges212 bronze badges

Comments

rt2800 · Accepted Answer · 2012-05-11 05:24:03Z

0

dbReader.GetValue(5).HasValue ? dbReader.GetValue(5).ToString() : String.Empty

answered May 11, 2012 at 5:24

rt2800

3,0552 gold badges22 silver badges26 bronze badges

Comments

KV Prajapati · Accepted Answer · 2012-05-11 05:31:39Z

0

Use the OleDbDataReader.IsDBNull(ordinal) to verify the field value.

if(!dbReader.IsDBNull(5))
  aPartOrder.VendorId = dbReader.GetString(5);

Suggestion: Always use parameters/precompiled sql statement to prevent SQL-Injection attack.

cmd.CommandText = "SELECT * FROM PartOrder WHERE OrderNo = @OrderNo";
cmd.Parameters.Add("@OrderNo",System.Data.OleDb.OleDbType.VarChar,20).Value=orderNo;
dbReader = cmd.ExecuteReader();

edited May 11, 2012 at 5:31

answered May 11, 2012 at 5:20

KV Prajapati

94.8k20 gold badges151 silver badges188 bronze badges

Collectives™ on Stack Overflow

Unable to cast object of type 'System.DBNull' to type 'System.String'. Error [duplicate]

4 Answers 4

Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Linked

Related