Error while trying to pass NULL to a timestamp field

Question

I am trying to insert records into Access table from SQL Server table.

For each row in SQL Server table, I'm checking for a condition that if timestamp field is NULL, then insert NULL into corresponding Access field as well.

Pasting a small code snippet here:

foreach (DataRow dr in dra)  //Each datarow in SQL Server table
{
    OleDbCommand accessCmdUpdate = new OleDbCommand("update xxx set xxxID=@0,xxxNotes=@2,xxxdatetime=@3 where xxxID=@1", AccessConnection);

    accessCmdUpdate.Parameters.Add(new OleDbParameter("@0", dr[0].ToString()));
    accessCmdUpdate.Parameters.Add(new OleDbParameter("@1", dr[1].ToString()));
    accessCmdUpdate.Parameters.Add(new OleDbParameter("@2", convertedText));
    if (dr[3] != System.DBNull.Value)
        accessCmdUpdate.Parameters.Add(new OleDbParameter("@3",OleDbType.DBTimeStamp,100,ParameterDirection.Input,true,(byte)(100),(byte)(0),"xxxdatetime",DataRowVersion.Current,Convert.ToDateTime(dr[3])));
    else
        accessCmdUpdate.Parameters.Add(new OleDbParameter("@3", OleDbType.DBTimeStamp, 100, ParameterDirection.Input, true, (byte)(100), (byte)(0), "xxxdatetime", DataRowVersion.Current, DBNull.Value));

    accessCmdUpdate.ExecuteNonQuery();

}

I have tried inserting DbNull.Value for @3 (DBTimeStamp) parameter. But it doesn't work. I get the following error:

System.Data.OleDb.OleDbException: No value given for one or more required parameters. Parameters @0 and @2 are inserted properly.

How do I pass NULL for DBTimeStamp field using OleDbParameter?

Please submit the structure of the table. Maybe it's a mandatory field? — Fischermaen
Tha table structure is exactly same for both Access and SQL Server tables. It is not a mandatory field, but I have to pass all the field values from SQL Server table. — KhD

sikender sikender · Accepted Answer · 2011-10-12T04:18:31

I think you should select allow nulls constrain from database table for that field where you got exception.

Otherwise you forgot to give value to any field in code and it hasn't allow nulls constrain.

Error while trying to pass NULL to a timestamp field

4 Answers