Cathi, Roger and Mike,

Well, problem partially solved.

Mike, the Convert.DBNull didn't do anything different. The value was still DBNull and the SqlCommand.ExecuteNonQuery still didn't like it.

Roger, I tried looking at SqlProfiler, but the error is happening on the client-side ... it's not getting to SQL Server.

Cathi, I looked again at our SP's and realized that they assumed an insert if the PK is zero, not if it's null (somebody must've changed their mind about that, cuz I woulda sworn it was if they were null). So, anyway, setting my PK to zero if it's DBNull solved the problem.

However, it still doesn't solve the problem of sending nulls to the back-end ... what if I want to do that? At this point I don't need to, but I'm hoping to find a solution to this in case I do need to in the future.

Thanks guys!
~~Bonnie



>Hi Bonnie,
>
>One idea is to make the PK a negative number instead of NULL. That way you know it is appending a new record.
>
>>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