This one is really stumping me. I hope someone has some suggestions.

I access all my SQL tables through Stored Procedures. One of the things that has to be done in this situation is to set up the SqlCommand.Parameters. Here's the method that I have that does that:

protected void SetBasicParameters(SqlCommand Command, DataTable Table, DataRow Row, string KeyName)
{
    Command.Parameters.Clear();
    for (int i = 0; i < Table.Columns.Count; i++)
    {
      if (i < 1 || Row[i] != System.DBNull.Value)
         Command.Parameters.Add("@" + Table.Columns[i].ColumnName, Row[i]);
    }
    Command.Parameters["@" + KeyName].Direction = ParameterDirection.InputOutput;
}

A coupla things to note here:

The first column in all our tables is always the PK (that's why the the first column is always being added to the parameter list, regardless of whether it contains a null value or not).

We purposely do *not* add parameters for columns that contain null values ... no need to update those columns and our SP's handle this correctly.

However, and here's the problem, we *have* to have the PK be a null value in the case where we are inserting a new record. The fact that it is null tells the SP that this is an insert, not an update, and it (the SP) will return the new PK in that parameter (hence the ParameterDirection.InputOutput). But, I don't know if there's a bug or what, but I cannot get the ExecuteNonQuery() method to accept this. (Note that the value is System.DBNull.Value ... it will not accept it as plain old null ... I tried <g>). It comes back with an error: "Parameter 0: '@portalsecuritykey' of type: String, the property Size has an invalid size: 0".

How the heck can I pass a null parameter? Does anyone have any ideas?

TIA,
~~Bonnie