VB.net - insert/retrieve picture from mysql Database directly to/from a Picturebox
I'm am having a heck of a time finding a code snippet that works for this. I have got to the point where it appears the picture is stored as a blob (perhaps incorrectly) by using this code.
Dim filename As String = txtName.Text + ".jpg"
Dim FileSize As UInt32
Dim ImageStream As System.IO.MemoryStream
ImageStream = New System.IO.MemoryStream
PbPicture.Image.Save(ImageStream, System.Drawing.Imaging.ImageFormat.Jpeg)
ReDim rawdata(CInt(ImageStream.Length - 1))
ImageStream.Position = 0
ImageStream.Read(rawdata, 0, CInt(ImageStream.Length))
FileSize = ImageStream.Length
Dim query As String = ("insert into actors (actor_pic, filename, filesize) VALUES (?File, ?FileName, ?FileSize)")
cmd = New MySqlCommand(query, conn)
cmd.Parameters.AddWithValue("?FileName", filename)
cmd.Parameters.AddWithValue("?FileSize", FileSize)
cmd.Parameters.AddWithValue("?File", rawData)
cmd.ExecuteNonQuery()
MessageBox.Show("File Inserted into database successfully!", _
"Success!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk)
but on retrieving to the picturebox using the following code:
Private Sub GetPicture()
'This retrieves the pictures from a mysql DB and buffers the rawdata into a memorystream
Dim FileSize As UInt32
Dim rawData() As Byte
Dim conn As New MySqlConnection(connStr)
conn.Open()
conn.ChangeDatabase("psdb")
Dim cmd As New MySqlCommand("SELECT actor_pic, fil开发者_如何学编程esize, filename FROM actors WHERE actor_name = ?autoid", conn)
Cmd.Parameters.AddWithValue("?autoid", Actor1Box.Text)
Reader = cmd.ExecuteReader
Reader.Read()
'data is in memory
FileSize = Reader.GetUInt32(Reader.GetOrdinal("filesize"))
rawData = New Byte(FileSize) {}
'get the bytes and filesize
Reader.GetBytes(Reader.GetOrdinal("actor_pic"), 0, rawData, 0, FileSize)
Dim ad As New System.IO.MemoryStream(100000)
' Dim bm As New Bitmap
ad.Write(rawData, 0, FileSize)
Dim im As Image = Image.FromStream(ad) * "error occurs here" (see below)
Actor1Pic.Image = im
Reader.Close()
conn.Close()
conn.Dispose()
ad.Dispose()
I get the error "parameter not valid" in the area noted. FYI If anyone even has some better (working) code examples than this that I can plug in versus debugging this mess that would be great too.
Well since getting no help i bashed away at the problem and got it to work finally. Here is my working code.
SAVE TO MySQL out of Picturebox (pbPicture)
Dim filename As String = txtName.Text + ".jpg"
Dim FileSize As UInt32
conn.Close()
Dim mstream As New System.IO.MemoryStream()
PbPicture.Image.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg)
Dim arrImage() As Byte = mstream.GetBuffer()
FileSize = mstream.Length
Dim sqlcmd As New MySqlCommand
Dim sql As String
mstream.Close()
sql = "insert into [your table] (picture, filename, filesize)
VALUES(@File, @FileName, @FileSize)"
Try
conn.Open()
With sqlcmd
.CommandText = sql
.Connection = conn
.Parameters.AddWithValue("@FileName", filename)
.Parameters.AddWithValue("@FileSize", FileSize)
.Parameters.AddWithValue("@File", arrImage)
.ExecuteNonQuery()
End With
Catch ex As Exception
MsgBox(ex.Message)
Finally
conn.Close()
End Try
LOAD from MySQL db Back to Picturebox
Dim adapter As New MySqlDataAdapter
adapter.SelectCommand = Cmd
data = New DataTable
adapter = New MySqlDataAdapter("select picture from [yourtable]", conn)
NOTE!! can only put once picture in picturebox so obvoiusly this query can only return one record for you
commandbuild = New MySqlCommandBuilder(adapter)
adapter.Fill(data)
Dim lb() As Byte = data.Rows(0).Item("picture")
Dim lstr As New System.IO.MemoryStream(lb)
PbPicture.Image = Image.FromStream(lstr)
PbPicture.SizeMode = PictureBoxSizeMode.StretchImage
lstr.Close()
Well, You are trying to cast the type MemoryStream when the 'Image.FromStream' is expecting System.IO.Stream
I just wrote this code as part of my contribution to this forum though not a member but love to help. This code search for multiple records and their corresponding images as you keep searching for record instead of search for one record and close the app to search again. This code allows you to search a record, clear the fields enter the search criteria and search again & again.
If TextBox3.Text = "" Then ' This is the search field to be used it could be any field from your database that will match the value from the database. Either firstname, phone or email etc
MsgBox("Nothing to search for from the database", MsgBoxStyle.OkOnly + MsgBoxStyle.Exclamation, "Oop!")
End If
Try
conn.Open()
Dim data As New MySqlDataAdapter("SELECT * FROM users_data WHERE phoneno = '" & TextBox3.Text & "' ", conn)
Dim dTable As New DataTable
data.Fill(dTable)
If dTable.Rows.Count > 0 Then
TextBox1.Text = dTable.Rows(0).Item("firstname")
TextBox2.Text = dTable.Rows(0).Item("lastname")
'Fetching the corresponding image to this member
Dim arrImage As Byte()
Dim myMS As New IO.MemoryStream
If Not IsDBNull(dTable.Rows(0).Item("myimage")) Then
arrImage = dTable.Rows(0).Item("myimage")
For Each ar As Byte In arrImage
myMS.WriteByte(ar)
Next
PictureBox1.Image = System.Drawing.Image.FromStream(myMS)
End If
Else
MsgBox("No record found for this Phone No: " & TextBox3.Text & " Enter a valid Phone No or consult the Admin Manager", MsgBoxStyle.OkOnly + MsgBoxStyle.Information, "Record not found")
clear()
End If
Catch ex As Exception
MsgBox(ex.Message)
Exit Sub
Finally
conn.Close()
End Try
This code could work for MYSQL Server database and Microsoft SQL databases as well. The only difference is is changing this statement Dim data As New MySqlDataAdapter which is for MYSQL Server to Dim data As New SqlDataAdapter for Microsoft SQL server. Good evening all StackOverflowers
VB.NET - insert/retrieve picture from MySQL Database directly to/from a Picturebox
Example Procedure VISUAL BASIC. NET 2017: I'm sharing: 1° Configuration of the table MySQL and variables. 2° Visual design 3° code.
Overview of the procedure: The user put an imagen in a picturebox. The imagen located in picturebox is SAVED in MySQL table. Another option is give the ID of the image (textbox1.text) and then LOAD the image from the MySQL table. There is another button to clear the picturebox but is not strictly necessary. It is not the perfect procedure it has being made only to understand the flow of images load/save to MySQL table AND I SHARE my knoledge with you.
- Configure the table in MySQL server. Example: table used: "imagenes" variables:
"idimagen" as INT. (Primary key)
"imagen" as LONGBLOB (allow null) ------->>>> Container of the image.
Mysql table configuration example:
- DESIGN IN VB.NET
Designed form used:
- Code used:
Add reference: MySQL.data 6.10.5.0 You need to import: MySql.Data.MySqlClient AND Imports System.IO
Public Class Form1
Dim Server As String = "XX.XX.XXX.XXX"
Dim UserID As String = "XXXXXXXXXXXXXX"
Dim Password As String = "PASSWORD"
Dim Database As String = "DATABASE NAME"
Dim Port As Integer = 3306
Dim AllowUserVariables As Boolean = True
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim ULTIMOID As Integer = 0
Try
Dim xxxxcon As New MySqlConnectionStringBuilder()
xxxxcon.Database = Database
xxxxcon.Server = Server
xxxxcon.UserID = UserID
xxxxcon.Password = Password
xxxxcon.Port = 3306
xxxxcon.AllowUserVariables = True
Dim con As New MySqlConnection(xxxxcon.ToString)
Dim dbsql As String = "SELECT MAX(idimagen) AS 'ULTIMOID' FROM imagenes;"
Dim cmdMy As New MySqlCommand(dbsql, con)
con.Open()
cmdMy.ExecuteNonQuery()
Try
ULTIMOID = Convert.ToInt32(cmdMy.ExecuteScalar())
Catch ex As Exception
ULTIMOID = 0
End Try
con.Close()
Catch ex As Exception
MsgBox("Problemas leyendo la BASE para obtener el último ID. Error: " & ex.Message)
End Try
Try
Dim xxxxcon As New MySqlConnectionStringBuilder()
xxxxcon.Database = Database
xxxxcon.Server = Server
xxxxcon.UserID = UserID
xxxxcon.Password = Password
xxxxcon.Port = 3306
xxxxcon.AllowUserVariables = True
Dim FileSize As UInt32
Dim con As New MySqlConnection(xxxxcon.ToString)
Dim mstream As New System.IO.MemoryStream()
PictureBox1.Image.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg)
Dim arrImage() As Byte = mstream.GetBuffer()
FileSize = mstream.Length
Dim sqlcmd As New MySqlCommand
Dim sql As String
mstream.Close()
sql = "insert into imagenes (idimagen, imagen) VALUES(@id, @imagen)"
Try
con.Open()
With sqlcmd
.CommandText = sql
.Connection = con
.Parameters.AddWithValue("@id", ULTIMOID + 1)
.Parameters.AddWithValue("@imagen", arrImage)
.ExecuteNonQuery()
End With
Catch ex As Exception
MsgBox(ex.Message)
Finally
con.Close()
End Try
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
OpenFileDialog2.Filter = "image file (*.jpg, *.bmp, *.png) | *.jpg; *.bmp; *.png| all files (*.*) | *.* "
If OpenFileDialog2.ShowDialog <> Windows.Forms.DialogResult.Cancel Then
PictureBox1.Image = Image.FromFile(OpenFileDialog2.FileName)
End If
End Sub
Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
PictureBox1.Image = Nothing
End Sub
Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
Dim xxxxcon As New MySqlConnectionStringBuilder()
xxxxcon.Database = Database
xxxxcon.Server = Server
xxxxcon.UserID = UserID
xxxxcon.Password = Password
xxxxcon.Port = 3306
xxxxcon.AllowUserVariables = True
Dim con As New MySqlConnection(xxxxcon.ToString)
Try
Dim ds As New DataSet
Dim dbsql As String = "SELECT * FROM imagenes WHERE idimagen = " & TextBox1.Text & ";"
Dim cmdMy As New MySqlCommand(dbsql, con)
con.Open()
Dim da As New MySqlDataAdapter(dbsql, con)
da.Fill(ds, "Imagenes")
con.Close()
If ds.Tables("imagenes").Rows.Count > 0 Then
Dim bytes As [Byte]() = ds.Tables("imagenes").Rows(0).Item(1)
Dim ms As New MemoryStream(bytes)
PictureBox1.Image = Image.FromStream(ms)
Else
MsgBox("No record found for this Phone No: " & TextBox1.Text)
End If
Catch ex As Exception
MsgBox(ex.Message)
Exit Sub
End Try
End Sub
End Class
What happens if you switch this:
Dim ad As New System.IO.MemoryStream(100000)
to:
Dim ad As New System.IO.MemoryStream()
EDIT
VB array sizes are different than other programming languages, I think you need to do a minus 1:
rawData = New Byte(FileSize - 1) {}
EDIT 2
Alright, lets look over what you have for raw binary data. All JPGs should start with FFD8
and end with FFD9
. Insert the following after you set the rawData
array. If it throws an error then your JPEG information is corrupt.
If (rawData(0) = &HFF) AndAlso (rawData(1) = &HD8) Then
Trace.WriteLine("File start OK")
Else
Throw New ApplicationException("Invalid jpg header")
End If
If (rawData(rawData.Length - 2) = &HFF) AndAlso (rawData(rawData.Length - 1) = &HD9) Then
Trace.WriteLine("File end OK")
Else
Throw New ApplicationException("Invalid jpg footer")
End If
EDIT 3
We're going to need to see what the first few bytes of the data look like. Run this and post what gets outputed:
For I = 0 To 20
Trace.Write(Convert.ToString(rawData(I), 16).ToUpperInvariant())
Next
精彩评论