user3548593
user3548593

Reputation: 499

how to handle Datarow DBNull

The following code return empty cells for the ones who are stored as text" Number Stored as Text" How can I get the value of these cells ?

string connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties='Excel 8.0;HDR=yes;IMEX=2';");
foreach (var sheetName in GetExcelSheetNames(connectionString))
{
    using (OleDbConnection con1 = new OleDbConnection(connectionString))
    {
        var dt = new DataTable();
        string query = string.Format("SELECT  * FROM [{0}]", sheetName);
        con1.Open();
        OleDbDataAdapter adapter = new OleDbDataAdapter(query, con1);
        adapter.Fill(dt);
        for (int i = 1; i < dt.Rows.Count; i++)
        {
            for (int j = 1; j < dt.Columns.Count; j ++)
            {
                MessageBox.Show(dt.Rows[0][j].GetType().ToString());
            }
        }

enter image description here

Upvotes: 0

Views: 524

Answers (2)

dovid
dovid

Reputation: 6491

var cell = dt.Rows[0][j] == System.DBNull.Value ? "null...", dt.Rows[0][j];

Upvotes: 1

Kryptonian
Kryptonian

Reputation: 870

Use IMEX=1 for mixed types in connection string.

Upvotes: 0

Related Questions