Use of dbnull.value causes datatype declaration to be nvarchar(4000)

[Benjamin Walling]

When using SqlParameters, it appears that any time you set the value to
dbnull.value, it causes the variable to be declared as nvarchar(4000) in
the prepared statement (you can check this in Profiler). This seems to
work for most data types, but does not work for binary data types. I
have a nullable column of binary data type. I can correctly update the
column with data using a parameter when there is data for the column. I
cannot do it when the column needs to be null. I get a syntax error
converting nvarchar to binary. No amount of explicitly definining the
dbtype or the sqldbtype or the isnullable will change this behavior.

How do I insert/update a binary column with a null value using a SQL
paramenter?

 

[Pablo Castro [MS]]

I used this code to try this out and it didn't cause any errors:

using(SqlConnection conn = new SqlConnection("server=pablocas2;
integrated security=true")) {
conn.Open();

SqlCommand cmd = new SqlCommand("DECLARE @b VARBINARY(10); SET
@b=@p", conn);
SqlParameter param = cmd.Parameters.Add("@p",
SqlDbType.VarBinary, 10);
param.Value = DBNull.Value;
cmd.ExecuteNonQuery();
}

Could you post the few lines of code from your app where you setup the
statement and parameters for the command?

--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Benjamin Walling]

Could you post the few lines of code from your app where you setup the
statement and parameters for the command?

The difference may be that I do not explicitly declare my @variables in the
sql command text. The command object had appeared to do that on my behalf
based on the values supplied in the parameter list. However, in the case of
a null value, it fails to correctly determine the data type, even if I have
explicitly assigned the data type to the command object.

Given a table named Users with single varbinary(28) column named User_SID:

using(SqlConnection conn = new SqlConnection("server=pablocas2;
integrated security=true")) {
conn.Open();

SqlCommand cmd = new SqlCommand("INSERT Users (User_SID) VALUES
(@User_SID)", conn);
SqlParameter param = cmd.Parameters.Add("@User_SID",
SqlDbType.VarBinary, 28);
param.Value = DBNull.Value;
cmd.ExecuteNonQuery();
}

 

[Benjamin Walling]

based on the values supplied in the parameter list. However, in the case
of a null value, it fails to correctly determine the data type, even if I
have explicitly assigned the data type to the command object.

Sorry, I meant if I had assigned it into the parameter object, not the
command object.

 

[Pablo Castro [MS]]

I tried the sample below and it still works. Anything else missing in the
code or in the database schema?

using(SqlConnection conn = new SqlConnection("server=pablocas2;
database=test; integrated security=true")) {
conn.Open();

SqlCommand cmd = new SqlCommand(null, conn);
cmd.CommandText =
"if (select count(*) from sysobjects where
name='paramtest' and xtype='U') > 0 " +
"begin " +
" drop table paramtest " +
"end " +
"create table paramtest (c varbinary(10))";
cmd.ExecuteNonQuery();

cmd.CommandText = "INSERT INTO paramtest VALUES (@p)";
SqlParameter param = cmd.Parameters.Add("@p",
SqlDbType.VarBinary);
param.Value = DBNull.Value;
cmd.ExecuteNonQuery();
}

--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

This posting is provided "AS IS" with no warranties, and confers no rights.