none
Save Image to MS Access database from PictureBox ERROR RRS feed

  • Question

  • trying to save PictureBox image to MS Access database but received the following error:

    Data type mismatch in criteria expression.

    Dim ms As New MemoryStream
    
            PictureBox1.Image.Save(ms, PictureBox1.Image.RawFormat)
    
            Dim img() As Byte
    
            img = ms.ToArray()
    
            Dim updateQuery As String = "UPDATE Staff SET photo = @img WHERE sid = " & txtId.Text
    
            Dim command As New OleDbCommand(updateQuery, MDBconn)
            command.Parameters.Add("@img", OleDbType.Binary).Value = img
    
            command.ExecuteNonQuery()

    If save to SQL Server then no problem.

    Dim ms As New MemoryStream
    
            PictureBox1.Image.Save(ms, PictureBox1.Image.RawFormat)
    
            Dim img() As Byte
    
            img = ms.ToArray()
    
            Dim updateQuery As String = "UPDATE Staff SET photo = @img WHERE sid = " & txtId.Text
    
            Dim command As New SqlCommand(updateQuery, Conn)
            command.Parameters.Add("@img", SqlDbType.Binary).Value = img
    
            command.ExecuteNonQuery()
    Where I am wrong with MS Access db?

    Wednesday, March 20, 2019 1:36 PM

All replies

  • Personally I use bindingsources, which does that hot mess for you.

    For example:

    Imports System.Data.OleDb
    
    Public Class AccessConn
        Dim dt As New DataTable
        Dim bs As New BindingSource
        Private Sub AccessConn_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
            Using conn As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;
                                                            Data Source=C:\DATA\Access\AccessDB.accdb;")
                Using DA As New OleDbDataAdapter("SELECT * FROM MyTable", conn)
                    DA.MissingSchemaAction = MissingSchemaAction.AddWithKey
                    DA.FillSchema(dt, SchemaType.Source)
                    DA.Fill(dt)
                    bs.DataSource = dt
                    DataGridView1.DataSource = bs
    
                    PictureBox1.DataBindings.Add("Image", bs, "ImageColName", True)
                End Using
            End Using
    
        End Sub
    
        Private Sub Btn_BrowseForFile_Click(sender As Object, e As EventArgs) Handles Btn_BrowseForFile.Click
            Dim fb As New OpenFileDialog
            fb.InitialDirectory = "c:\"
            fb.Filter = "jpg files (*.jpg)|*.jpg|BMP files (*.bmp)|*.bmp"
            fb.RestoreDirectory = True
            If fb.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
                PictureBox1.Image = Image.FromFile(fb.FileName)
            End If
        End Sub
    
        Private Sub Btn_Update_Click(sender As Object, e As EventArgs) Handles Btn_Update.Click
            Using conn As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;
                                                            Data Source=C:\DATA\Access\AccessDB.accdb;")
                Using DA As New OleDbDataAdapter("SELECT * FROM MyTable", conn)
                    Dim cb As New OleDbCommandBuilder(DA)
    
                    DA.Update(dt)
                End Using
            End Using
        End Sub
    
    End Class
    
    


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Wednesday, March 20, 2019 1:46 PM
  • Hi,
    here is an example how I solve this problem:

      ' paste picture from PictureBox into Ole field
      Public Shared Sub InsertPict(ByVal img As Image)
        Try
          Dim myMemoryStream As New System.IO.MemoryStream
          If img Is Nothing Then
            MsgBox("No Picture")
            Exit Sub
          End If
          img.Save(myMemoryStream, System.Drawing.Imaging.ImageFormat.Jpeg)
          Dim arrImage() As Byte = myMemoryStream.GetBuffer
          Dim strSQL As String = "INSERT INTO xPict (PictOle) VALUES (@Picture)"
          Dim myCommand As New OleDbCommand(strSQL, myOleDbConnection)
          myCommand.Parameters.Add(New OleDbParameter("@Picture", OleDbType.LongVarBinary)).Value = arrImage
          myOleDbConnection.Open()
          myCommand.ExecuteNonQuery()
        Catch ex As Exception
          MsgBox(ex.Message)
        Finally
          myOleDbConnection.Close()
        End Try
      End Sub


    --
    Viele Grüsse / Best Regards
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks

    Wednesday, March 20, 2019 2:37 PM
  • Hello,

    In your ms-access table, is sid field numeric and did you validate txtId.Text is a valid integer? Since the SQL-Server worked okay and the exception points to the WHERE condition it would seem prudent to check sid data type.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, March 20, 2019 2:56 PM
    Moderator