All Forums Connectivity
tekriewa 8 posts Joined 04/13
23 Jul 2014
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?

 

NetFx 346 posts Joined 09/06
24 Jul 2014

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).

tekriewa 8 posts Joined 04/13
24 Jul 2014

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

You must sign in to leave a comment.