0

I'm wondering how to save image to MYSQL-DB via vb.net application. At the last I find some way to save image. But 'some type of images' doesn't save in that way. By 'some type of images' I mean diffrent widths,heights and resolutions.
For example I use this code to save image. On database there are two columns in table.

Dim mstream As New System.IO.MemoryStream()
        propic.Image.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg)
        Dim arrImage() As Byte = mstream.GetBuffer()
        mstream.Close()

        query = "INSERT INTO client(client_id, pro_pic) VALUES(@client_id, @pro_pic)"
        Dim cmd = New MySqlCommand(query, con)
        cmd.Parameters.AddWithValue("@client_id", (TextBox1.Text))
        cmd.Parameters.AddWithValue("@pro_pic", arrImage)
        Try
            con.Open()
            cmd.ExecuteNonQuery()
            MsgBox("Image Has Been Saved", "Save Picture")
        Catch ex As Exception
            MsgBox(ex.Message)
            Exit Sub
        End Try
        If (con.State = ConnectionState.Open) Then
            con.Close()
        End If

when I use diffrent sizes widths,heights or resolution size images this occure error.
The error say : "Data too long for column 'pro_pic' at row 1"

Now I want to know about why this shows and how to get rid this.....

2
Contributors
1
Reply
10
Views
1 Year
Discussion Span
Last Post by Reverend Jim
0

I'm using MS-SQL rather than MYSQL. Perhaps this code snippet will give you some idea.

'                                                                                       
'   Name:                                                                               
'                                                                                       
'       DBImageStoreRetrieve                                                            
'                                                                                       
'   Description:                                                                        
'                                                                                       
'       Demonstrate how to store, retrieve and display an image file to/from a database 
'       with both SQLDB and OLEDB.                                                      
'                                                                                       
'   Usage:                                                                              
'                                                                                       
'       Replace the image file name with the name of an actual image file. Please note  
'       that you should only do the insert once.                                        
'                                                                                       
'   Audit:                                                                              
'                                                                                       
'       2015-01-27  rj  original code                                                   
'                                                                                       

Imports System.Data
Imports System.Data.SqlClient
Imports System.IO
Imports System.Drawing.Imaging
Imports System.Data.OleDb

Public Class Form1

    Private ConSQL As String = "Server=.\SQLEXPRESS;Database=mydb;Trusted_Connection=yes;"
    Private ConOLE As String = "Provider=SQLNCLI10;Server=.\SQLEXPRESS;Database=mydb;Trusted_Connection=Yes;Connect Timeout=15;"
    Private imgname As String = "D:\temp\test.jpg"

    Private Sub btnStore_Click(sender As System.Object, e As System.EventArgs) Handles btnStore.Click

        Dim con As New SqlConnection(ConSQL)
        Dim cmd As New SqlCommand("INSERT INTO test (picname, picdata) VALUES (@NAME, @DATA)", con)

        'read the image file into a byte array

        Dim fs As New FileStream(imgname, FileMode.Open, FileAccess.Read)
        Dim imgdata(fs.Length() - 1) As Byte
        fs.Read(imgdata, 0, fs.Length)
        fs.Close()

        cmd.Parameters.AddWithValue("@NAME", imgname)
        cmd.Parameters.AddWithValue("@DATA", imgdata)

        'add the image to the database

        con.Open()
        cmd.ExecuteNonQuery()
        con.Close()

    End Sub

    Private Sub btnRetrieve_Click(sender As System.Object, e As System.EventArgs) Handles btnRetrieve.Click

        Dim con As New SqlConnection(ConSQL)
        Dim cmd As New SqlCommand("SELECT picdata FROM test WHERE picname = '" & imgname & "'", con)

        'retrieve the image from the database

        con.Open()
        Dim rdr As SqlDataReader = cmd.ExecuteReader

        'copy the binary data into a memorystream so it can be loaded into the picture box

        If rdr.Read Then
            Dim imgdata() As Byte = rdr("picdata")
            Dim str As New MemoryStream(imgdata)
            pbxImage.Image = Image.FromStream(str)
            str.Close()
        End If

        rdr.Close()
        con.Close()

    End Sub

    Private Sub btnOleDb_Click(sender As System.Object, e As System.EventArgs) Handles btnOleDb.Click

        Dim con As New OleDbConnection(ConOLE)
        Dim cmd As New OleDbCommand("SELECT picdata FROM test WHERE picname = '" & imgname & "'", con)

        'retrieve the image from the database

        con.Open()
        Dim rdr As OleDbDataReader = cmd.ExecuteReader

        'copy the binary data into a memorystream so it can be loaded into the picture box

        If rdr.Read Then
            Dim imgdata() As Byte = rdr("picdata")
            Dim str As New MemoryStream(imgdata)
            pbxImage.Image = Image.FromStream(str)
            str.Close()
        End If

        rdr.Close()
        con.Close()

    End Sub

End Class
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.