CODEWITHSUNDEEP
excelvba
Akim
Akim

Reputation: 11

Insert Image from VBA UserForm to MySQL Database in Excel

The project is sending Data from VBA Userform in Excel to Mysql via ADODB. How can I send image with that

...
ImageLocation = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)
Image1.Picture = LoadPicture(ImageLocation)

Dim con As ADODB.Connection
Set con = New ADODB.Connection
con.ConnectionString = "DRIVER={MySQL ODBC 8.0 Unicode Driver};SERVER=localhost;DATABASE=db;USER=root;PASSWORD=;"
con.Open

Sql = "insert into event(image_event) value ('" & img & "')"
con.Execute Sql

con.Close

Upvotes: 0

Views: 526

Answers (1)

CDP1802
CDP1802

Reputation: 16357

Use an ADODB.Stream object and read the binary data into the parameter value.

Option Explicit

Sub InsertImage()

    Dim conn As ADODB.Connection, cmd As New ADODB.Command
   
    ' select image
    Dim image
    With Application.FileDialog(msoFileDialogOpen)
         .AllowMultiSelect = False
        .Show
        image = .SelectedItems(1)
    End With

    ' SQL statement
    Const Sql = "INSERT INTO event (ID,image_event) VALUES (?,?)"

    ' sql command with parameters
    Set conn = ... ' your connection
    With cmd
        .ActiveConnection = conn
        .CommandText = Sql
        .Parameters.Append .CreateParameter("P1", adVarChar, adParamInput, 50)
        .Parameters.Append .CreateParameter("P2", adBinary, adParamInput, 1000000)
    End With

    ' load image
    cmd.Parameters(0).Value = image ' filename
    With CreateObject("ADODB.Stream")
        .Open
        .Type = 1  ' adTypeBinary
        .LoadFromFile image
        cmd.Parameters(1).Value = .Read
        .Close
    End With

    ' execute SQL
    Dim n As Long
    cmd.Execute n
    MsgBox image & " inserted"

End Sub

Upvotes: 1

Related Questions