Solved

Image BLOB, Read, MySQL

Posted on 2008-06-19
4
444 Views
Last Modified: 2013-11-26
Hi

I'm trying to fix my code so I can read a image from  BLOB filed in my MySQL table and then put it in my picturebox.

Here is the code

Public Sub GetBLOBImage(ByVal TableName As String, ByVal FieldName As String, ByVal PictureBox As PictureBox)

    '// Create the SQL query

    Dim QueryString As String = "SELECT Image FROM tbl_employed WHERE EmpNr = 1234"""

    Dim rawData() As Byte

    Dim FileSize As UInt32

    Dim fs As FileStream

 

    '// The "Using" block will automatically dispose of the connection when we're finished

    Using MyConnectionMySQLOpen As New MySqlClient.MySqlConnection(m_strConnectionString)

 

        Try

            '// Open the DB connection

            MyConnectionMySQLOpen.Open()

 

            '// Create a new command object

            Dim cmd As New MySqlClient.MySqlCommand()

 

            '// Set command properties

            With cmd

                .Connection = MyConnectionMySQLOpen

                .CommandType = CommandType.Text

                .CommandText = QueryString

            End With

 

            '// Execute the SQL query with the command object, and get the affected rows in the DB back

            Dim myData As MySqlDataReader = cmd.ExecuteReader

 

            '// Close the connection

            MyConnectionMySQLOpen.Close()

 

            If Not myData.HasRows Then Throw New Exception("There are no BLOBs data")

 

            myData.Read()

 

            '////////////////// HERE is where I need the help to complete the code//////////////////////////

            FileSize = myData.xxxxxx

            rawData = New Byte(CInt(FileSize)) {}

 

            myData.GetBytes(myData.GetOrdinal(FieldName), 0, rawData, 0, CInt(FileSize))

 

            PictureBox.Image = xxxxxxxxxxxxxx

 

        Catch MyException As MySqlException

            Throw

        Catch ex As Exception

            Throw

        Finally

            '// Close connection if an exception was thrown before the connection could close

            If MyConnectionMySQLOpen.State = ConnectionState.Open Then

                MyConnectionMySQLOpen.Close()

            End If

        End Try

    End Using

 

End Sub

Open in new window

0
Comment
Question by:AWestEng
  • 2
  • 2
4 Comments
 
LVL 24

Expert Comment

by:mankowitz
ID: 21826448
There is a forum article that gives some sample code.
Check out http://forums.mysql.com/read.php?38,6172,186617#msg-186617

' This assumes that the filesize is stored as a separate column in the table
FileSize = myData.GetUInt32(myData.GetOrdinal("filesize"))
rawData = New Byte(FileSize) {}
'get the bytes and filesize
myData.GetBytes(myData.GetOrdinal("file"), 0, rawData, 0, FileSize)
Dim ad As New MemoryStream(100000)
Dim bm As Bitmap
ad.Write(rawData, 0, FileSize)
bm = New Bitmap(ad)
0
 
LVL 1

Author Comment

by:AWestEng
ID: 21829343
oki. but must I have the filesize in the table?

is it not possible to solve it otherwise?
0
 
LVL 24

Accepted Solution

by:
mankowitz earned 500 total points
ID: 21834494
Yes, you should be able to do it without the filesize, but you would have to do a separate query which returns a scalar, like this:

 MyCon = New MySqlConnection("your connection string here")
            MyCon.Open()
            Dim str As String = "SELECT image_field FROM table_name WHERE
id=1"
            MyCmd = New MySqlCommand(str, MyCon)
            Dim b() As Byte
            b = MyCmd.ExecuteScalar()
            If (b.Length > 0) Then
                Dim stream As New MemoryStream(b, True)
                stream.Write(b, 0, b.Length)
                picturebox1.image= New Bitmap(stream)
                stream.Close()
            End If
            MyCon.Close()
0
 
LVL 1

Author Comment

by:AWestEng
ID: 21837863
oki, thx I will test it and get back to you
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now