How to load image from SQL CE in VB.NET

[adelords]

I've been searching and trying numerous codes to get this done but to no avail. I simply want to load an image binary from SQL CE to be displayed on a picturebox and it should be in VB.NET. Here's the code I'm stuck with

Dim ItemID As Integer = 10814950
Dim SQL As String
Dim BA As Byte()
Dim SC As New SqlCeCommand
SQL = "SELECT ip_img FROM InventoryPhoto WHERE inventory_item_it_id = " & ItemID

SC.Connection = conn
conn.ConnectionString = data_source
SC.Connection.Open()
SC.CommandType = Data.CommandType.Text
SC.CommandText = SQL
BA = CType(SC.ExecuteScalar(), Byte())
SC.Connection.Close()
SC.Dispose()

Dim ArraySize As New Integer
ArraySize = BA.GetUpperBound(0)
Dim fs As New FileStream("tmp.gif", FileMode.CreateNew)

fs.Write(BA, 0, ArraySize + 1)
fs.Close()

PicBox.SizeMode = PictureBoxSizeMode.StretchImage
PicBox.Image = New Bitmap("tmp.gif")

When I execute this, there will be an error pointing to this line -> fs.Write(BA, 0, ArraySize + 1), with the message : Value does not fall within the expected range. Plz help

[DaveT]

Hello,

The code below shows how to achieve the desired:

Imports System.Data
Imports System.Data.SqlServerCe
Imports System.Data.Common
Imports System.Data.SqlTypes
Imports System.IO
Imports System.Reflection

Public Class Form1
    Private dbPath As String, connStr As String
    Private conn As SqlCeConnection
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.Button1.Text = "Show Image"
        'Create database
        dbPath = "\My Documents\test.sdf"
        connStr = "Data Source=" & dbPath
        If Not File.Exists(dbPath) Then
            Dim eng As New SqlCeEngine(connStr)
            eng.CreateDatabase()
        End If
        conn = New SqlCeConnection(connStr)
        conn.Open()
        Dim cmd As SqlCeCommand = conn.CreateCommand()
        cmd.CommandText = "select count(*) from INFORMATION_SCHEMA.Tables where TABLE_NAME = 'test'"
        If CInt(cmd.ExecuteScalar()) = 0 Then
            cmd.CommandText = "create table test (id int identity, picture image)"
            cmd.ExecuteNonQuery()
        End If

        addimagetodatabase()

    End Sub

    Sub addimagetodatabase()
        'Store image in database
        Dim fs As FileStream
        Try
            fs = File.Open("\My Documents\gooble.bmp", FileMode.Open)
        Catch ex As Exception
            MessageBox.Show(ex.ToString(), "Failed to open the image")
            Exit Sub
        End Try

        Dim imageData(fs.Length) As Byte
        fs.Read(imageData, 0, imageData.Length)
        fs.Close()

        Try
            Dim cmd As SqlCeCommand = conn.CreateCommand()
            cmd.CommandText = "delete from test "
            cmd.ExecuteNonQuery()
            cmd.CommandText = "insert into test (picture) values (?)"
            Dim param As SqlCeParameter = cmd.Parameters.Add("picture", SqlDbType.Image)
            param.Value = imageData
            cmd.ExecuteNonQuery()
        Catch sqex As SqlCeException
            MessageBox.Show(sqex.ToString(), "DB operation failed")
        End Try

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim cmd As SqlCeCommand = conn.CreateCommand()
        cmd.CommandText = "select id, picture from test"
        Dim value As Byte(), rdr As SqlCeDataReader
        Try
            rdr = cmd.ExecuteReader()
        Catch ex As SqlCeException
            MessageBox.Show(ex.ToString(), "DB operation failed")
            Exit Sub
        End Try
        ' Check that we did not receive an empty recordset. 
        If Not rdr.Read() Then
            MessageBox.Show("No records in the database")
            Exit Sub
        End If
        value = CType(rdr("picture"), Byte())
        Dim ms As MemoryStream = New MemoryStream(value)
        PictureBox1.Image = New Bitmap(ms)
    End Sub
End Class

Regards,
Dave

Dave Traister
Software Engineer
ComponentOne LLC

[marrisa]

Hi, Dave, I tried the code you wrote, but I ran into another problem.
When I load BMP from file, there comes the same error. So could you be so kind to help me?
Thks
marrisa

Hello,

The code below shows how to achieve the desired:

Imports System.Data
Imports System.Data.SqlServerCe
Imports System.Data.Common
Imports System.Data.SqlTypes
Imports System.IO
Imports System.Reflection

Public Class Form1
    Private dbPath As String, connStr As String
    Private conn As SqlCeConnection
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.Button1.Text = "Show Image"
        'Create database
        dbPath = "\My Documents\test.sdf"
        connStr = "Data Source=" & dbPath
        If Not File.Exists(dbPath) Then
            Dim eng As New SqlCeEngine(connStr)
            eng.CreateDatabase()
        End If
        conn = New SqlCeConnection(connStr)
        conn.Open()
        Dim cmd As SqlCeCommand = conn.CreateCommand()
        cmd.CommandText = "select count(*) from INFORMATION_SCHEMA.Tables where TABLE_NAME = 'test'"
        If CInt(cmd.ExecuteScalar()) = 0 Then
            cmd.CommandText = "create table test (id int identity, picture image)"
            cmd.ExecuteNonQuery()
        End If

        addimagetodatabase()

    End Sub

    Sub addimagetodatabase()
        'Store image in database
        Dim fs As FileStream
        Try
            fs = File.Open("\My Documents\gooble.bmp", FileMode.Open)
        Catch ex As Exception
            MessageBox.Show(ex.ToString(), "Failed to open the image")
            Exit Sub
        End Try

        Dim imageData(fs.Length) As Byte
        fs.Read(imageData, 0, imageData.Length)
        fs.Close()

        Try
            Dim cmd As SqlCeCommand = conn.CreateCommand()
            cmd.CommandText = "delete from test "
            cmd.ExecuteNonQuery()
            cmd.CommandText = "insert into test (picture) values (?)"
            Dim param As SqlCeParameter = cmd.Parameters.Add("picture", SqlDbType.Image)
            param.Value = imageData
            cmd.ExecuteNonQuery()
        Catch sqex As SqlCeException
            MessageBox.Show(sqex.ToString(), "DB operation failed")
        End Try

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim cmd As SqlCeCommand = conn.CreateCommand()
        cmd.CommandText = "select id, picture from test"
        Dim value As Byte(), rdr As SqlCeDataReader
        Try
            rdr = cmd.ExecuteReader()
        Catch ex As SqlCeException
            MessageBox.Show(ex.ToString(), "DB operation failed")
            Exit Sub
        End Try
        ' Check that we did not receive an empty recordset. 
        If Not rdr.Read() Then
            MessageBox.Show("No records in the database")
            Exit Sub
        End If
        value = CType(rdr("picture"), Byte())
        Dim ms As MemoryStream = New MemoryStream(value)
        PictureBox1.Image = New Bitmap(ms)
    End Sub
End Class

Regards,
Dave

Dave Traister
Software Engineer
ComponentOne LLC

[fenrenren]

Mark! Useful!
http://www.facebook.com