Saving image data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm having trouble saving SqlDbType.Image data from a file to SQl Server.
Using VB.Net 2003 and SQL Server 2000, I am attempting to use a stored
procedure to save the data using the following code:

Dim fi As New FileInfo(FileName)

' Open the Image file for Read
Dim imgStream As Stream = fi.OpenRead

' Create a Byte array the size
' of the image file for the Read
' methods buffer() byte array
Dim imgData(CInt(imgStream.Length)) As Byte

'Read the file into the byte array
imgStream.Read(imgData, 0, CInt(fi.Length))

Dim cn As New SqlConnection(ConnectionString)

Dim cmd As New SqlCommand("up_DefaultImageInsert", cn)
' holds a sql command object
cmd.CommandType = CommandType.StoredProcedure

' Add Parameters
With cmd.Parameters
.Add("@Id", SqlDbType.Int, 4).Value = ID
.Add("@Image", SqlDbType.Image, imgData.Length).Value = imgData
.Add("@Size", SqlDbType.Int, 4).Value = CInt(fi.Length)
End With

'Open the connection
cn.Open()
'Execute the query
cmd.ExecuteNonQuery()
'Close the connection
cn.Close()

The original file was 5,918 bytes long. I can change the function and write
the byte array back out to disk and its length is 5, 918 bytes. However, if I
check the length of the @Image parameter in the stored procedure it is 5, 919
bytes. A 00 byte has been added to the end of the file and is saved in the
image column along with the original data when I do a WRITETEXT. If I then
read the data back out of SQL Server and save it to disk it remains 5, 919
bytes.

Am I correct in thinking that ADO.NET is adding a byte to the end of the
data when using parameters in a stored procedure and does anyone know of a
way around this while still being able to use a stored proc?
 
It turns out that the byte array was not dimensioned correctly and I
mistakenly thought that an extra byte was being added.
 
Nope, not ADO.NET, you are adding the extra bye

Change:
Dim imgData(CInt(imgStream.Length)) As Byte
To
Dim imgData(CInt(imgStream.Length)-1) As Byte

Your array is 1 too big, and the last byte has nothing in it - but you are
saving the entire array.
 
Back
Top