Hi All
I'm having a spot of bother trying to retrieve and display SQL Server 2005 IMAGE data in standard picture box controls. I am converting an Access 2003 application that uses bound forms and all works fine. I have been kicking ideas around for a couple of days now, I have downloaded a working app from these forums that works fine, however, my code will not run.
The first problem I came upon was trying to retrieve the IMAGE data as an OUTPUT parameter from a SQL stored procedure by 'rolling-my-own' parameterised ADO command objects but cannot get a VARBINARY or BINARY parameter to work, even using the .Prepare and letting ado sort it doesn't. So I am left with retrieving a recordset and picking the IMAGE data out into a Byte() array.
Everyting works until the code reachs ' .Image = Image.FromStream(ms)' and I get a 'Parameter is not valid' exception. I lifted the MemoryStream code straight from the example.
Anyone got any ideas as to how to deal with this, or best practise ideas.
I was thinking it might be worthing investing some time in writing a routine to pull all of the data out of SQL Server and store in a folder as a rebuilt jpg and read the file instead.
Sorry to waffle on but wanted to give as much infor as possible.
Mark Harby
Nottingham, UK
Public Function GetAlbumArt(ByVal AlbumNo As Integer) As Byte()
Dim adoCommand As New ADODB.Command
Dim adoRecordset As New ADODB.Recordset
Try
With adoCommand
.ActiveConnection = m_adoConnection
.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
.CommandText = "MC07__Album_GetAlbumArt"
.Parameters.Append(.CreateParameter("Return", ADODB.DataTypeEnum.adInteger, ADODB.ParameterDirectionEnum.adParamReturnValue))
.Parameters.Append(.CreateParameter("AlbumNo", ADODB.DataTypeEnum.adInteger, ADODB.ParameterDirectionEnum.adParamInput, , AlbumNo))
'.Parameters.Append(.CreateParameter("AlbumArt", ADODB.DataTypeEnum.adVarBinary, ADODB.ParameterDirectionEnum.adParamInputOutput))
'.Prepared = True
'.Parameters(1).Value = AlbumNo
'.Execute(, , ADO_adExecuteNoRecords)
adoRecordset.Open(adoCommand, , ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly)
If adoRecordset.BOF And adoRecordset.EOF Then
dim RetByte() As Byte = {0}
Else
Dim RetByte() As Byte = CType(adoRecordset.Fields("AlbumArt").Value, Byte())
End If
End With
Catch ex As Exception
MessageBox.Show(ex.Message, "CAlbum.GetAlbumArt(" & AlbumNo.ToString & ")", MessageBoxButtons.OK, MessageBoxIcon.Error)
Finally
adoRecordset = Nothing
adoCommand = Nothing
End Try
Return RetByte
End Function
' ...
' Main form stuff.
.
.
Dim PictureData() As Byte = AlbumClass.GetAlbumArt(.AlbumNo)
Dim ms As New MemoryStream(PictureData)
With Me.picAlbumArt
.Image = Image.FromStream(ms)
.SizeMode = PictureBoxSizeMode.CenterImage
.BorderStyle = BorderStyle.Fixed3D
End With
.
.