locked
Retrieving Blob Image MySql RRS feed

  • Question

  • User-1402564948 posted

    Hello,

    I've mostly been working with Sql Server, but this solution has a MySql database.  

    I've been able to insert a record with an image, but I'm having trouble getting the image on the page.

    The record shows that there is a blob and has the size.

    Edit Edit
     Copy Copy
     Delete Delete
    30
    497e80e1-772f-4ac4-9964-4eecedf4b898
    2016-04-20 08:48:35
    [BLOB - 34.4 KiB]
    NULL
    Upload Photo
    NULL

    Also, I'm using a Odbc connector which I guess is required to connect to MySql.

    Here is my image handler and code.

    Imports System.Data.SqlClient
    Imports System.Data.Odbc
    
    Public Class getWallImage
        Inherits System.Web.UI.Page
    
        Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
    
            Dim wId As Integer = Convert.ToInt32(Request.QueryString("wId"))
            Dim conn As New OdbcConnection
    
            'Connect to the database and bring back the image contents & MIME type for the specified picture
            conn.ConnectionString = "Driver=MySQL ODBC 5.1 Driver;server=server;User=User;password=Password;port=Port;database=DB"
            Const SQL As String = "SELECT [ALL] FROM [Walls] WHERE [wId = @wId]"
            Dim myCommand As New OdbcCommand(SQL, conn)
            myCommand.Parameters.AddWithValue("@wId", wId)
    
            conn.Open()
            Dim myReader As OdbcDataReader = myCommand.ExecuteReader
    
            If myReader.Read Then
                Response.BufferOutput = True
                Response.BinaryWrite(myReader("wImage"))
                Response.ContentType = "Image/JPG"
                Response.ContentType = "Image/gif"
                Response.ContentType = "Image/png"
                Response.End()
            End If 'nothing in the URL as HTTP GET
    
            myReader.Close()
            conn.Close()
        End Sub
    
    End Class
    If FileUpload1 IsNot Nothing Then
                    Dim strExtension As String = System.IO.Path.GetExtension(FileUpload1.FileName)
                    If (strExtension.ToUpper() = ".JPG") Or (strExtension.ToUpper() = ".GIF") Or (strExtension.ToUpper() = ".PNG") Or (strExtension.ToUpper() = ".PNG") Or (strExtension.ToUpper() = ".JPEG") Then
    
                        ' Resize Image Before Uploading to DataBase
                        Dim imageToBeResized As System.Drawing.Image = System.Drawing.Image.FromStream(FileUpload1.PostedFile.InputStream)
                        Dim imageHeight As Integer = imageToBeResized.Height
                        Dim imageWidth As Integer = imageToBeResized.Width
                        Dim maxHeight As Integer = 640
                        Dim maxWidth As Integer = 480
                        imageHeight = (imageHeight * maxWidth) / imageWidth
                        imageWidth = maxWidth
    
                        If imageHeight > maxHeight Then
                            imageWidth = (imageWidth * maxHeight) / imageHeight
                            imageHeight = maxHeight
                        End If
    
                        Dim bitmap As New Bitmap(imageToBeResized, imageWidth, imageHeight)
                        Dim stream As System.IO.MemoryStream = New MemoryStream()
                        bitmap.Save(stream, System.Drawing.Imaging.ImageFormat.Jpeg)
                        stream.Position = 0
                        Dim image As Byte() = New Byte(stream.Length) {}
                        stream.Read(image, 0, image.Length)
    
                        Dim connstring As String = "string"
                        Dim sqlConn As New OdbcConnection(connstring)
                        'Dim Label126 As DateTime
                        ' Build your query '
                        Dim query = "INSERT INTO Walls (Id, Body, wImage, wDate) VALUES (" + "?, ?, ?, ?)"
                        ' Build your command '
                        Dim cmd As New OdbcCommand(query, sqlConn)
                        sqlConn.Open()
                        
                        ' Add your parameters and execute your query '
    
                        'cmd.Parameters.Add("ID", OdbcType.UniqueIdentifier).Value = ID()
    
                        cmd.Parameters.AddWithValue("@Id", User.Identity.GetUserId().ToString)
                        cmd.Parameters.Add("Body", OdbcType.VarChar).Value = Body.Text()
                        Dim UploadedImage As New OdbcParameter("@wImage", OdbcType.Image, image.Length)
                        UploadedImage.Value = image
                        cmd.Parameters.Add(UploadedImage)
                       cmd.Parameters.Add("wDate", OdbcType.DateTime).Value = DateTime.Now
                        cmd.ExecuteNonQuery()
    
                        Body.Text = String.Empty
                        FileUpload1.Dispose()
                        SettingsUpdatedMessage.Text = "The value was inserted into your database"
                        GridView1.DataBind()
                        sqlConn.Close()
                    End If

    Thanks for any help!

    Wednesday, April 20, 2016 4:11 PM

All replies

  • User-1716253493 posted

    try like this

            If reader.HasRows Then
                Do While reader.Read()
                   'do something
                Loop
            Else
                Console.WriteLine("No rows found.")
            End If

    Thursday, April 21, 2016 3:29 AM