Change a datarow item from DBNull to a string valu

Question

I may be doing this all wrong, but here goes:

    OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM [CRPRList$]", XLSConnect);
    //the XLSConnect is from a spreadsheet. I've confirmed the data is there.
    DataSet ds = new DataSet();
    adapter.Fill(ds);
    DataTable thisTable = ds.Tables[0];
    //The table exists and it does populate
    thisTable.Columns.Add("Summary");
    //The new column is added and I am able to populate it (below)

    for (int rowNum = 0; rowNum < thisTable.Rows.Count; rowNum++) { 
        //Here I'm cycling through the row items

        DataRow row = thisTable.Rows[rowNum];
        string pr = row["PR"].ToString();
        string cr = row["CR"].ToString();
        //The first two column values are grabbed and examined

        if ((pr != "" && pr != null)&&(cr == null || cr == "")) {
           //if the first column is empty then the second is examined and I am attempting
           //to populate the first column using the searchForCRNumber method
           //This method returns a string

            cr = this.searchForCRNumber(pr); //assignment works
            row[0] = cr; //Here is the problem, this assignment does not work (more explained below)
        }
        row["Summary"] = this.searchForSummary(cr);
        row.AcceptChanges();
    }
    this.showResults(thisTable);//works, except the changes above are not applied

The line row[0] = cr; is naturally an empty value and so it comes up as type DBNull and won't accept cr string.

The error I'm getting is System.FormatException: Input string was not in a correct format.

I've searched for ways to convert row[0] to an empty string or some other way to cast the cr and so far I've not found a way to do this.

EDIT I'm adding more to help understand what is going on around this problem. I thought I was thorough enough above, but perhaps not...

The data for XLSConnect comes from:

private bool XSLConnection(string filePath, string fileType) { // returns false if XSL or XSLX is not the fileType
    string strConn;
    if (fileType.ToLower() == "xls" || fileType.ToLower() == "xlsx") {
        strConn = (fileType == "xlsx") ? string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0\"", filePath) : string.Format("Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0\"", filePath);

        XLSConnect = new OleDbConnection(strConn);
        return true;
    } else return false;
}

Here's the searchForCRNumber method:

private string searchForCRNumber(string PRNumber) {
    return this.getProperty("MyReturnColumn", PRNumber, "MyMatchColumn");
}

And here's the getProperty method:

private string getProperty(string searchProperty, string searchVal, string rtrnProperty) {
    string undefined = "Undefined";
    string rtrn = null;
    string query = "SELECT " + rtrnProperty + " FROM MySQLTable WHERE " + searchProperty + " = '" + searchVal + "'";
    this.QCConn.Open();

    SqlDataReader reader = this.runQCConnect(query).ExecuteReader();
    reader.Read();
    rtrn = (!reader.HasRows)?
        undefined :
        reader[0].ToString();

    if (rtrn == "" || rtrn == null) rtrn = undefined;
    this.QCConn.Close();


    return rtrn;
}

MORE Edit I'm still working on this one and I'm begining to wonder if I have not found a bug. I have found what is wrong and am still working on a way to fix it. The xls(x) that is uploaded can have empty cells. I've found that those empty cells will cause the DataTable to have empty cells but their type is DBNull... which, of course, can't (naturally) be assigned a string value.

When I apply the following in a try/catch:

throw new Exception("PR = " + pr + " and typeof = " + pr.GetType().ToString() + " && CR = " + cr + " and typeof = " + cr.GetType().ToString() + "\nCell typeof = " + row["CR"].GetType().ToString());

I get this response:

PR = 7800 and typeof = System.String && CR = Undefined and typeof = System.String
Cell typeof = System.DBNull 

So as you can see, the cell is System.DBNull and won't accept a string value. This is the source of my error. But I still do not know how to make this work. Is there something I'm missing? How can I assign the string value to these cells regardless of the DBNull value?

Answer 1

DBNull is not a null string, and converting it to a string will not give you null.

If that's what you want, you should probably check for that before converting it to a string. Do something like:

string pr = (row["PR"] == System.DBNull.Value) ? null : row["PR"].ToString();
string cr = (row["CR"] == System.DBNull.Value) ? null : row["CR"].ToString();