Supply a stored procedure with byte array 1

[swreng]

Dear all,

I would like to update an image field on sql server with a stored procedure "INSERTIMG" which receives one parameter varbinary(16).

So i am reading a picture file i am storing it in a byte[] array "bts" and i want all the byte array become the parameter value for stored procedure "INSERTIMG".
But during the execution i am receiving the error:

"Procedures INSERTIMG expects parameter @img which was not supplied".
Why the data of bts array doesn't supply the parameter of stored procedure?

Many thanks

FileStream str=new FileStream(@"c:\temp1.jpg",FileMode.Open ,FileAccess.Read);
byte[] bts=new byte[str.Length-1];
str.Read(bts,0,bts.Length-1);
str.Close();

OleDbCommand cmd=new OleDbCommand();
cmd.CommandText="INSERTIMG";
cmd.Parameters.Add("@img",textdata);


try
{
cmd.ExecuteNonQuery();
}

catch(System.Exception ex)
{
MessageBox.Show(ex.Message);
}

 

[swreng]

Hi,
I have a mistake in the post in the point
cmd.Parameters.Add("@img",textdata);

in my code is like this
cmd.Parameters.Add("@img",bts);

and is not working.

Many thanks

 

[ninjadeathmonkey]

Well, I don't think this has anything to do with it, but shouldn't you initialize bts like this:

byte[] bts = new byte[str.Length];

I don't think you need the str.Length - 1 in there. As for the parameter, I doubt it will make much of a difference, but what if you changed it to something like this:

cmd.CommandText="INSERTIMG";
SqlParameter paramImg = new SqlParameter("@img", SqlDbType.Image);
paramImg.Value = bts;
cmd.Parameters.Add(paramImg);

Hope that helps.

Ron Wheeler
Tekdev Open Source Development

 

[swreng]

I changed but i didn't see any different.
I suspect that the problem is that i have OleDbCommand and not SqlDbCommand, but i am not really sure.

Thank you for your interesting

 

[AlexCuse]

You can't pass an array as a parameter to a stored procedure. why bother splitting it into an array at all? I imagine you can just pass the string of bytes.

Hope this helps,

ALex

Ignorance of certain subjects is a great part of wisdom

 

[ninjadeathmonkey]

Well, I'm pretty sure you can pass a byte array to a stored procedure. Here is an example from SQL Server Magazine on how they did theirs: Article

Listing A: C# Code That Calls the p_insertimage Procedure
public class ImageData
{
    public static void Main()
    {
      string sconn = "Provider=SQLOLEDB;Data Source=teca4;"
        + "Initial Catalog=SQLMag;Integrated Security=SSPI;";

      OleDbConnection conn = new OleDbConnection();
      conn.ConnectionString = sconn;
      conn.Open();

      FileStream fs =
        new FileStream("C:\\temp\\homer.jpg",
        FileMode.Open, FileAccess.Read);
      Byte[] imagefile = new Byte[fs.Length];
      fs.Read(imagefile, 0, (int)fs.Length); 
      fs.Close(0;

      OleDbCommand cmd = new OleDbCommand();

      //Stored procedure with an image input parameter
      //  that inserts to a table containing an image column.
      cmd.CommandText = "p_insertimage";
      cmd.CommandType = CommandType.StoredProcedure;
      cmd.Connection = conn;
      OleDbParameter image =
        cmd.Parameters.Add("@image", OleDbType.LongVarBinary);
      image.Direction = ParameterDirection.Input;
      image.Value = imagefile;
      cmd.ExecuteNonQuery();
      conn.Close();
    }
}

Ron Wheeler
Tekdev Open Source Development

 

[AlexCuse]

Touche ;-)

I see they are using OleDbType.LongVarBinary, I wonder if there is an implicit conversion back to string? I might have to play around with this some time this weekend (I don't want to put an image in a 'real' database ;-) ).


Ignorance of certain subjects is a great part of wisdom

 

[ninjadeathmonkey]

Haha. Yeah, I'm not sure either. I'll have to load the OleDbCommand object into Reflector or something to see how it does it. I could be wrong, but I don't think the database does the conversion.

swreng,
By the way, did that help at all?

Ron Wheeler
Tekdev Open Source Development

 

[swreng]

Well

I think that i found something:
I declare the type of input parameter of stored procedure as image
and the the type of cmd OleDbCommand as LongVarBinary as you told me and it works properly.

Many thanks