SQL-Server to Access dealing with NULL

Question

I am having a rather strange problem. I am writing function (based on this SO post) that dumps a MsSQL DB to Access one.

In MsSQL DB I have some Float fields. Those float fields I have mapped to Access Double.

MsSQL Float fields sometimes have NULL in them, and I can't transfer those fields to access. Access complains about Wrong value type. If I set that NULL to any value, it works just fine.

In my Code I load a table in DataTable. Like this:

SqlDataAdapter adapter1 = new SqlDataAdapter("select * from tbl_values", conn);
        DataSet dataSet = new DataSet();
        adapter1.Fill(dataSet, "tbl_values");

I prepare Access Connection like this:

 ADOX.Catalog catalog = new ADOX.Catalog();
        catalog.Create(accessConnectionString);

        //Create an Access connection and a command that we'll use
        OleDbConnection accessConnection = new OleDbConnection(accessConnectionString);
        OleDbCommand command = new OleDbCommand();
        command.Connection = accessConnection;
        //command.CommandType = CommandType.Text;
        accessConnection.Open();

And I iterate like this:

foreach (DataTable table in dataSet.Tables)
        {
            String columnsCommandText = "(";
            foreach (DataColumn column in table.Columns)
            {
                String columnName = column.ColumnName;
                String dataTypeName = column.DataType.Name;
                String sqlDataTypeName = SetAccessFieldType(dataTypeName);
                //String sqlDataTypeName = "text";//dataTypeName;
                 columnsCommandText += "[" + columnName + "] " + sqlDataTypeName + ",";
            }
            columnsCommandText = columnsCommandText.Remove(columnsCommandText.Length - 1);
            columnsCommandText += ")";

            command.CommandText = "CREATE TABLE " + table.TableName + columnsCommandText;

            command.ExecuteNonQuery();
        }

        foreach (DataTable table in dataSet.Tables)
        {               

            foreach (DataRow row in table.Rows)
            {
                int counter = 0;
                String commandText = "INSERT INTO " + table.TableName + " VALUES (";
                foreach (var item in row.ItemArray)
                {                       
                      commandText += "'" + item.ToString() + "',";                       

                }
                commandText = commandText.Remove(commandText.Length - 1);
                commandText += ")";

                command.CommandText = commandText.ToString();
                command.ExecuteNonQuery();
            }
        }

        accessConnection.Close();

I set Access Types using this helper method:

static public string SetAccessFieldType(string FieldName)
    {   
        var AccessDataType = "";

        switch (FieldName)
        {
            case "Int32":
                AccessDataType = "long";
                break;
            case "String":
                AccessDataType = "text";
                break;
            case "Double":
                AccessDataType = "decimal";
                break;
            case "DateTime":
                AccessDataType = "date";
                break;
            case "Boolean":
                AccessDataType = "boolean";
                break;
            case "Byte[]":
                AccessDataType = "date";//This is timeStamp datatype in Access
                break;
            default:
                AccessDataType = "text";  
                break;                   
        }

        return AccessDataType;
    }

What can I do to deal with NULL's???

Dmytro Shevchenko · Accepted Answer · 2015-09-23T12:57:49.917

1

Instead of this line:

commandText += "'" + item.ToString() + "',";

Try this:

if (item == null || item == DBNull.Value)
{
    commandText += "NULL,";  
}
else
{
    commandText += "'" + item.ToString() + "',";  
}

Also, make sure that your tables in Access have corresponding columns configured to allow nulls. See https://msdn.microsoft.com/en-us/library/bb177893(v=office.12).aspx. By default, if you don't add the NOT NULL clause when defining your columns, you should be OK.

edited Sep 23 '15 at 12:57

answered Sep 23 '15 at 12:51

Dmytro Shevchenko

33,431
6
51
67

1

That did it :). Thank you. – Amiga500 Sep 24 '15 at 06:58

SQL-Server to Access dealing with NULL

1 Answers1