reading binary data from varbinary(max)

I used to do this with an Image data type (sql2000) and
an array of bytes. Worked great. Now , in sql2005/vb2005
we are supposed to use the varBinary(max) type . Fine.

Anyone know how to get it to work

the reader code is straight from the help docs in the section:
"Working with large value types"

I'm trying to use some of the new functionality but I can't get anything to work well yet with this datatype.

Error is on this line at design time:

Dim binaryData() As Byte = CByte(reader.GetValue(8))

.....This is supposed to read the 9th column into an array of bytes.....

"***value of type Byte cannot be converted to
1-dimensional array of byte***"

Here's the code:

blob is a varBinary(max) column

Private Sub GetImageForPB(ByVal parmSelectKey As Integer)
Dim connection As SqlConnection EntriesDB.GetConnection()
Dim selectStatement As String = _
"SELECT blob FROM entries " _
& "WHERE myKey = @myKey"

Dim selectCommand As
New SqlCommand(selectStatement, connection)
Dim myKeyParm As
New SqlParameter("@myKey", parmSelectKey)
selectCommand.Parameters.Insert(0, myKeyParm)

connection.Open()
Dim reader As SqlDataReader
reader = selectCommand.ExecuteReader(CommandBehavior.CloseConnection)

While reader.Read()
' Read the data from BLOB, varbinary(max) column
Dim binaryData() As Byte = CByte(reader.GetValue(8))
End While

'picturebox
PB.Image = binaryData.

End Sub

Any thoughts
Thanks.

Answer this question

reading binary data from varbinary(max)

Metaferia

Or

PictureBox1.Image = New Bitmap(MemoryStream(CType(dr("picture"), Byte())))
Amir Goodarzi
I guess I can buy myself a beer.
It works great on both side, insert and retrieve.

It was me. I was stupid.
I was passing a blank id into my query on insert and the image was never posted so it could not be retrieved.

I am an idiot.

chaio
GaryMcC

I never allow nulls in anything I write from the ground up.
greenmonkey
You got it guy's !
I've been fighting this for 2 days.
My vb2003 app works great with the IMAGE data type but this is using a varBinary(Max) column instead and it is driving me nuts.

The column ("blob") accepts NULL for the reason I don't always save binary data
on each row, sometimes just text, notes , etc.

'Update Sub:

Private Sub UpdateImage(ByVal getKey As Integer)
Dim cmSQL As SqlCommand
Dim strSQL As String
Dim arrFilename() As String
If (PB.Image Is Nothing) Then
Exit Sub
End If

arrFilename = Split(tbLookup.Text, "\")
Array.Reverse(arrFilename)

' save image to byte array
Dim arrImage() As Byte
Using imageStream As New IO.MemoryStream
PB.Image.Save(imageStream, PB.Image.RawFormat)
arrImage = imageStream.GetBuffer
imageStream.Close()
End Using

Dim cnSQL As SqlConnection = EntriesDB.GetConnection
strSQL = "UPDATE Entries SET " _
& "blob = @myBlob " _
& "WHERE myKey = @getKey "

cmSQL = New SqlCommand(strSQL, cnSQL)
With cmSQL
.Parameters.AddWithValue("@getKey", getKey)
.Parameters.Add(New SqlParameter("@myBlob", arrImage))
End With

Try
cnSQL.Open()
Catch Exp As SqlException
MsgBox(Exp.Message, MsgBoxStyle.Critical, "Cannot find record on insert")
End Try
Try
cmSQL.ExecuteNonQuery()
' Close and Clean up objects
cnSQL.Close()
cmSQL.Dispose()
cnSQL.Dispose()
Catch Exp As SqlException
MsgBox(Exp.Message, MsgBoxStyle.Critical, "SQL Error on Image Insert")
Catch Exp As Exception
MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error")
Finally
End Try
End Sub

'Retrieve sub:

Private Sub GetImageForPB(ByVal parmSelectKey As Integer)
Dim connection As SqlConnection = EntriesDB.GetConnection()
Dim selectStatement As String = "SELECT blob FROM entries " _
& "WHERE myKey = @myKey"

Dim selectCommand As New SqlCommand(selectStatement, connection)
Dim myKeyParm As New SqlParameter("@myKey", parmSelectKey)
selectCommand.Parameters.Insert(0, myKeyParm)

connection.Open()
Dim reader As SqlDataReader
reader = selectCommand.ExecuteReader(CommandBehavior.CloseConnection)
While reader.Read()
' Read the data from varbinary(max) column
Dim arrImage() As Byte = CType(reader("blob"), Byte())
' Dim arrImage As Byte()
Dim ms As New MemoryStream(arrImage)
' Dim bitmap As New Bitmap(memorybits)
With PB
.Image = Image.FromStream(ms)
.SizeMode = PictureBoxSizeMode.CenterImage
.BorderStyle = BorderStyle.Fixed3D
End With
End While
End Sub

Beer's on me if anyone can help
thanks

KA
tackett
Thanks,

Unfortunately for all of us MS has depreciated the IMAGE data type in SQL2005 and will eventually remove it, so it can not be used for new development.

I have to upgrade my 2003.net/sql200 app to the current release, thus the need to use the varBinary(max) data type. It is supposed to be compatible with a byte array, but I cannot get anything to work with it so far.

Your code above errors out if the table column is NULL.
Your code here is exactly what I was using originally with the IMAGE data type but will not
work with the new large varBinary.

"Unable to cast object of type "System.dbNull" to system.byte()"

That does not happen in the prior releases, (2003/2000).
nativecpp
Hi,

Paste your code.
Bhupendra_Singh_4c7760
Hi,

SQL 2005 has a image data type two and for assign a picture that is store in a database to a picture box is something like this:

Dim bits As Byte() = CType(dr("picture"), Byte())

Dim memorybits As New MemoryStream(bits)

Dim bitmap As New Bitmap(memorybits)

PictureBox1.Image = bitmap

Article:

http://support.microsoft.com/default.aspx/kb/321900/en-us spid=2965&sid=global
reading binary data from varbinary(max)

Answer this question