DbNull.Value in an image column

cwineman · Aug 23, 2004

Hello,

I'm using SQL Server and I have a column for storing photos of the database
type "image". I want it to be optionally NULL. I have a stored procedure
that I use to populate the table. When I run the stored procedure from the
query analyzer tool, I have no problems. I can pass in a NULL parameter for
this column, or pass in actual content.

From .NET I have a problem setting the parameter correctly. I can pass in
actual content. In this case the byte [] buffer for an image file. However,
when I try to pass in DBNull.Value, I get the error "nvarchar is
incompatible with image". I don't understand what the problem is.

Has anyone else had this error? Has anyone else stored null values in a
column of type image through .NET?

I've posted some code below to give you and idea of what I am doing.

Thanks,
Corey

_connectionString = "Data Source=DBMGR;" + "Initial Catalog=MyDatabase;" +
"User ID = MyID;" + "Password = MyPassword;";

_connection = (IDbConnection)Activator.CreateInstance(
typeof(SqlConnection), false);
_connection.ConnectionString = _connectionString;

_command = (IDbCommand)Activator.CreateInstance( typeof(SqlCommand), false);
_command.Connection = _connection;
_command.CommandType = System.Data.CommandType.StoredProcedure;

Image img = Image.FromFile("C:\\temp\\a.jpg");
MemoryStream ms = new MemoryStream();
img.Save( ms, System.Drawing.Imaging.ImageFormat.Jpeg );

byte [] myBytes = new byte[4];
myBytes[0] = 1;
myBytes[1] = 2;
myBytes[2] = 3;
myBytes[3] = 4;

string paramName = "@my_column";
object val = myBytes;// null;//(object) ms.ToArray();
ParameterDirection direction = ParameterDirection.Input;
int size = 50;

_parameter = (IDbDataParameter)Activator.CreateInstance(
typeof(SqlParameter), false );
_parameter.ParameterName = paramName;
if(val!=null)
{
_parameter.Value = val;
}
else
{
_parameter.Value = DBNull.Value;
_parameter.Size = size;
}

_parameter.Direction = direction;

_command.Parameters.Clear();

_command.CommandText = "SBASE_DALSP_Insert_Test";
_command.Parameters.Add( _parameter );

_connection.Open();
_command.ExecuteNonQuery();

Sushil Chordia · Aug 24, 2004

Corey,
Can you set the DbType or SqlDbType of the parameter property before
executing the statement? For Image, DbType enum value is DbType.Binary and
SqlDbType value is SqlDbType.Image. Since SqlClient has no information about
the type of the parameter, it tries to convert the type to nvarchar based on
the partial parameter information and SQL Server throws as String value is
not compatible with Image column.

HTH,
Sushil.

cwineman · Aug 24, 2004

Sushil,

That's it, thanks.

I saw that property, but I thought it meant db type as in SQL Server,
Oracle, OLE, ODBC, etc. I should have investigated more I guess.

So does that mean it works when I passed a valid binary object, because it
can guess the appropriate db type by looking at the data I passed in? So
maybe when I passed in DbNull.Value, it simply guessed the wrong
type(nvarchar)?

Sushil Chordia · Aug 24, 2004

Yes. Thats exactly why it worked when passing a Byte Array.
Thanks,
Sushil.

Guest · May 10, 2005

Hello,

I'm using SQL Server and I have a column for storing photos of the database
type "image". I want it to be optionally NULL. I have a stored procedure
that I use to populate the table. When I run the stored procedure from the
query analyzer tool, I have no problems. I can pass in a NULL parameter for
this column, or pass in actual content.

From .NET I have a problem setting the parameter correctly. I can pass in
actual content. In this case the byte [] buffer for an image file. However,
when I try to pass in DBNull.Value, I get the error "nvarchar is
incompatible with image". I don't understand what the problem is.

Has anyone else had this error? Has anyone else stored null values in a
column of type image through .NET?

I've posted some code below to give you and idea of what I am doing.

Thanks,
Corey

_connectionString = "Data Source=DBMGR;" + "Initial Catalog=MyDatabase;" +
"User ID = MyID;" + "Password = MyPassword;";

_connection = (IDbConnection)Activator.CreateInstance(
typeof(SqlConnection), false);
_connection.ConnectionString = _connectionString;

_command = (IDbCommand)Activator.CreateInstance( typeof(SqlCommand), false);
_command.Connection = _connection;
_command.CommandType = System.Data.CommandType.StoredProcedure;

Image img = Image.FromFile("C:\\temp\\a.jpg");
MemoryStream ms = new MemoryStream();
img.Save( ms, System.Drawing.Imaging.ImageFormat.Jpeg );

byte [] myBytes = new byte[4];
myBytes[0] = 1;
myBytes[1] = 2;
myBytes[2] = 3;
myBytes[3] = 4;

string paramName = "@my_column";
object val = myBytes;// null;//(object) ms.ToArray();
ParameterDirection direction = ParameterDirection.Input;
int size = 50;

_parameter = (IDbDataParameter)Activator.CreateInstance(
typeof(SqlParameter), false );
_parameter.ParameterName = paramName;
if(val!=null)
{
_parameter.Value = val;
}
else
{
_parameter.Value = DBNull.Value;
_parameter.Size = size;
}

_parameter.Direction = direction;

_command.Parameters.Clear();

_command.CommandText = "SBASE_DALSP_Insert_Test";
_command.Parameters.Add( _parameter );

_connection.Open();
_command.ExecuteNonQuery();

User submitted from AEWNET (http://www.aewnet.com/)

Guest · May 10, 2005

Hello there,
you can check your Reference Microsoft.ApplicationBlocks.Data. Change of reference might will surely solve your problem.
cheers..
dev team

Hello,

I'm using SQL Server and I have a column for storing photos of the database
type "image". I want it to be optionally NULL. I have a stored procedure
that I use to populate the table. When I run the stored procedure from the
query analyzer tool, I have no problems. I can pass in a NULL parameter for
this column, or pass in actual content.

From .NET I have a problem setting the parameter correctly. I can pass in
actual content. In this case the byte [] buffer for an image file. However,
when I try to pass in DBNull.Value, I get the error "nvarchar is
incompatible with image". I don't understand what the problem is.

Has anyone else had this error? Has anyone else stored null values in a
column of type image through .NET?

I've posted some code below to give you and idea of what I am doing.

Thanks,
Corey

_connectionString = "Data Source=DBMGR;" + "Initial Catalog=MyDatabase;" +
"User ID = MyID;" + "Password = MyPassword;";

_connection = (IDbConnection)Activator.CreateInstance(
typeof(SqlConnection), false);
_connection.ConnectionString = _connectionString;

_command = (IDbCommand)Activator.CreateInstance( typeof(SqlCommand), false);
_command.Connection = _connection;
_command.CommandType = System.Data.CommandType.StoredProcedure;

Image img = Image.FromFile("C:\\temp\\a.jpg");
MemoryStream ms = new MemoryStream();
img.Save( ms, System.Drawing.Imaging.ImageFormat.Jpeg );

byte [] myBytes = new byte[4];
myBytes[0] = 1;
myBytes[1] = 2;
myBytes[2] = 3;
myBytes[3] = 4;

string paramName = "@my_column";
object val = myBytes;// null;//(object) ms.ToArray();
ParameterDirection direction = ParameterDirection.Input;
int size = 50;

_parameter = (IDbDataParameter)Activator.CreateInstance(
typeof(SqlParameter), false );
_parameter.ParameterName = paramName;
if(val!=null)
{
_parameter.Value = val;
}
else
{
_parameter.Value = DBNull.Value;
_parameter.Size = size;
}

_parameter.Direction = direction;

_command.Parameters.Clear();

_command.CommandText = "SBASE_DALSP_Insert_Test";
_command.Parameters.Add( _parameter );

_connection.Open();
_command.ExecuteNonQuery();

User submitted from AEWNET (http://www.aewnet.com/)

DbNull.Value in an image column

cwineman

Sushil Chordia

cwineman

Sushil Chordia

Guest

Guest