This issue occurs when inserting a row from the DataTable into the corresponding table contained in the Advanced SQL Engine. The DataTable contains columns that have data types that are provider specific types, and the corresponding columns in the underlying SQL table are defined as NOT NULL and with a DEFAULT value. When a row from the DataTable is inserted into the SQL table the provider will return a TdException that indicates that a Null value cannot be inserted.

Releases Affected

This issue occurs with all Teradata provider releases.

Conditions When Issue Occurs

When the following conditions exist a TdException will be thrown when DbDataAdapter.Update has been called by an application:

1. The SQL table corresponding to the DataTable contains a column defined as NOT NULL and a DEFAULT value.
2. DataAdapter.ReturnProviderSpecificTypes has been set to true. This issue does not occur when this property is set to false.
3. DataAdapter.Fill is called to fill the DataTable.
4. A new row has been added to the DataTable. This data will be inserted into the corresponding SQL table.
5. The data in the column of the DataTable is the Null value.

In the information that is sent to Teradata, the provider will indicate that the parameter is NOT NULL. However, a null value is sent to Teradata. There is a conflict between the value and the information about the parameter. This causes Teradata to send an error indicating that the value cannot be null.

Example Of Code That Causes Issue To Occur

The following is an example of code that duplicates this problem:

public static void Example1()
{
    // The definition of the table used in this example is
    //    CREATE TABLE ProblemTable(
    //        C1 INTEGER,
    //        C2 DATE NOT NULL DEFAULT DATE'2008-10-10',
    //        C3 DECIMAL(20, 4))

    // Setting up the log on string
    TdConnection cn = new TdConnection();
    TdConnectionStringBuilder tsb = new TdConnectionStringBuilder();
    tsb.DataSource = "teradata";
    tsb.UserId = "user";
    tsb.Password = "password";

    // Open a connection to the Advanced SQL Engine
    cn.ConnectionString = tsb.ConnectionString;
    cn.Open();

    // Setting up the SELECT statement that will be used to fill the DataTable
    TdCommand cmd = cn.CreateCommand();
    cmd.CommandText = "select c1, c2, c3 from ProblemTable";

    // Setting the data adapter and command builder
    TdDataAdapter da = new TdDataAdapter(cmd);
    da.ReturnProviderSpecificType = true;

    TdCommandBuilder cb = new TdCommandBuilder(da);

    DataTable dt = new DataTable("ProblemTable");

    // Filling the DataTable
    da.Fill(dt);

    // Adding a row to the DataTable
    // Column C2 will not be set. It will default to the TdDate.Null value.
    DataRow dr = dt.NewRow();
    dr["C1"] = 200;
    dr["C3"] = new TdDecimal(321.32);
    dt.Rows.Add(dr);

    try
    {
        // A TdException will get thrown when Update is called
        da.Update(dt);
    }
        catch (TdException e)
    {
        // This exception was expected to be thrown
        Console.WriteLine(e.Message)
    }
}

Workarounds

There are several work arounds for this issue:

• After filling the DataTable, set DefaultValue property of the column in the DataTable that corresponds to the NOT NULL column of the underlying SQL table. In the above example, this would be the column c2 which is the second column of the DataTable:

      da.Fill(dt)
    dt.Columns[1].DefaultValue = new TdDate(2008, 10, 10);

• If it is possible, set the ReturnProviderSpecificTypes property to false. In this situation the TdCommandBuilder will generate the correct INSERT statement.

See Also

System.Data.DataTable
TdDataReader.GetSchemaTable
TdParameter
TdCommandBuilder
TdDataAdapter