How to load image from SQL CE in VB.NET


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: How to load image from SQL CE in VB.NET

  1. #1
    Join Date
    Jan 2008
    Posts
    2

    Unhappy How to load image from SQL CE in VB.NET

    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

  2. #2
    Join Date
    Feb 2008
    Posts
    2

    Re: How to load image from SQL CE in VB.NET

    Hello,

    The code below shows how to achieve the desired:
    Code:
    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

  3. #3
    Join Date
    Jul 2013
    Posts
    2
    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
    Quote Originally Posted by DaveT View Post
    Hello,

    The code below shows how to achieve the desired:
    Code:
    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

  4. #4
    Join Date
    May 2013
    Location
    Sydney
    Posts
    6
    Mark! Useful!
    http://www.facebook.com

Similar Threads

  1. Replies: 1
    Last Post: 02-20-2008, 05:30 AM
  2. SQL Server image datatype and inserts
    By Daniel Reber in forum Database
    Replies: 2
    Last Post: 01-16-2002, 09:21 AM
  3. Retrieving data from an SQL image field
    By jonas in forum VB Classic
    Replies: 0
    Last Post: 12-19-2001, 04:55 AM
  4. Access & SQL Server
    By David Jones in forum Database
    Replies: 0
    Last Post: 08-31-2001, 01:22 PM
  5. ADO vs SQL image DatType
    By Devin Knutson in forum VB Classic
    Replies: 0
    Last Post: 12-05-2000, 03:38 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center