Error inserting null value into nullable field using TdCommand

Connectivity
Enthusiast

Error inserting null value into nullable field using TdCommand

I have the following unit test which is failing due to a TdException with the message "[Teradata Database] [2689] Field is null for column defined as NOT NULL."
[TestMethod]
public void CanInsertNullStringParameter()
{
using (DbConnection connection = db.CreateConnection())
{
connection.Open();
using (DbTransaction transaction = connection.BeginTransaction())
{
string sqlString = "insert into TempCustomers (CustomerID, CompanyName, ContactName) Values (?, ?, ?);"
DbCommand insert = db.GetSqlStringCommand(sqlString);
db.AddInParameter(insert, "CustomerID", DbType.Int32, 1);
db.AddInParameter(insert, "CompanyName", DbType.String, "test");
db.AddInParameter(insert, "ContactName", DbType.String, null);

db.ExecuteNonQuery(insert, transaction);
transaction.Rollback();
}
}
}

The SQL used to create the table is as follows ... I have confirmed via Teradata SQL Assistant that column ContactName is indeed nullable, and that I can insert a null value via an insert statement applied directly in SQL Assistant.

CREATE TABLE TempCustomers (CustomerId VARCHAR(5) NOT NULL, CompanyName VARCHAR(128) NOT NULL, ContactName VARCHAR(128) NULL);

Internally, my C# code gets down to a TdCommand.ExecuteNonQuery(), which is where the exception gets thrown.  The command text is the SQL below. I have confirmed that the third parameter has value DBNull.Value.

insert into TempCustomers (CustomerID, CompanyName, ContactName) Values (?, ?, ?)

Can someone advise why I'm receiving this error?

Tags (3)
2 REPLIES
Teradata Employee

Re: Error inserting null value into nullable field using TdCommand

Check TdCommand.Parameters[2].IsNullable property. I think isNullable is set to false but the application is sending a DbNull to the Teradata Database; resulting in a mismatch between the parameter description (NOT NULABLE) and the parameter value (NULL).

Enthusiast

Re: Error inserting null value into nullable field using TdCommand

Yes, that appears to be it - the test case works when I change IsNullable to true.  Thanks for the guidance.