DbNull.Value in an image column

C

cwineman

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();
 
S

Sushil Chordia

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.
 
C

cwineman

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)?
 
G

Guest

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();
Click to expand...

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

Guest

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();
Click to expand...

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Fetching & Inserting Data into a column of TEXT Data type in SQL server 2000 Using ASP.NET 1
Problem while Fetching Text type of DATA in ASP.NET 1

Top