从更改一个DBNull的数据行项目的字符串值 [英] Change a datarow item from DBNull to a string value

问题描述

我可能会做这一切错了，但这里有云：

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

行行[0] = CR; 自然是空值，所以它的类型来了的DBNull 也不会接受 CR 字符串。

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

我得到的错误是 System.FormatException：输入字符串的不正确的格式

我一直在寻找方法来转换行[0] 为空字符串或一些其他的方式来投的 CR ，到目前为止，我还没有找到一种方式来做到这一点。

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.

修改的我增加更多，以帮助了解什么是解决此问题的事情。我以为我不够彻底以上，但也许不是... 的

为 XLSConnect 本数据来源于：

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;
}

这里的 searchForCRNumber 方法：

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

和这里的的getProperty 方法：

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;
}

更多修改
我还在就这一个，我开始时怀疑，如果我还没有找到一个bug。我发现什么是错的，现在还在研究一种方法来解决它的工作。被上传的XLS（X）可以有空白单元格。我发现，那些空单元格将导致DataTable中有空白单元格，但它们的类型是DBNull的......其中，当然，不能（自然）分配一个字符串值。

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.

当我申请以下在一个try / catch：

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 

因此​​，大家可以看到，电池是System.DBNull，不会接受一个字符串值。这是我的错误的来源。但我仍然不知道如何使这项工作。有我丢失的东西？我怎么能不管分配的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?

推荐答案

的DBNull 不是一个空字符串，并将其转换为字符串不会给你空。

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();

这篇关于从更改一个DBNull的数据行项目的字符串值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！