Error while inserting image into access database

Question

0.00/5 (No votes)

See more:

Hi All,
I am trying to insert an image into my MS Access 2007 database. The datatype I chose is "OLEObject" and Fieldname as "Image".
I tried the following code which executes when a button is pressed:

Private Sub ButtonPress()

    Dim cmd As New OleDbCommand
    Dim MemStream As New IO.MemoryStream
    Dim DataPic_Update As Byte()
    Dim strImage As String
    
    If Not IsNothing(PictureBox1.Image) Then
    
        PictureBox1.Image.Save(MemStream, Imaging.ImageFormat.Png)
        DataPic_Update = MemStream.GetBuffer
        MemStream.Read(DataPic_Update, 0, MemStream.Length)
        strImage = "?"
        MemStream.Close()
    
    Else
        DataPic_Update = Nothing
        strImage = "NULL"
    End If
    
    con.Open()
    
    cmd.CommandText = "INSERT INTO Inventory([Image])" + "VALUES(@Image)"
    
    cmd.Parameters.Add("@Image", OleDbType.Binary).Value = DataPic_Update
    cmd.Connection = con
    cmd.ExecuteNonQuery()
    con.Close()

End Sub

While executing the command "ExecuteNonQuery", I am getting following Error:

"Data type mismatch in criteria expression."

I am not able to solve this error. Can someone please help me with any suggestions or modifications required in my existing code?
I want to insert the image and then retrieve from the access database.

Thanks,
Raj S.

Posted 2-Sep-13 2:59am

Member 3099196

Updated 2-Sep-13 9:55am

Richard C Bishop

v2

Add a Solution

Comments

[no name] 2-Sep-13 9:24am

Check your command text. I do not believe that "INSERT INTO Inventory([Image])VALUES(@Image)" would be valid.

syed shanu 2-Sep-13 22:17pm

hi ,
Change from OleDbType.Binary to OleDbType.Image and test now.hope this will save ur image to MS Access.

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mohan Gopi · Answer 1 · 2013-09-02T18:39:00

Solution 1

Hi member,

Instead of this line

C#

cmd.Parameters.Add("@Image", OleDbType.Binary).Value = DataPic_Update

you can try below line

C#

cmd.Parameters.Add("@Image", OleDbType.LongVarBinary).Value = DataPic_Update

I hope this will help you.

Thank's
Mohan G

Posted 2-Sep-13 18:39pm

Mohan Gopi

dinesh pilani · Answer 2 · 2013-09-08T07:01:00

Solution 4

this is will help u or not i dont know but
i want to say that there is missing one line

cmd.displose

it will written before con.close

Posted 8-Sep-13 7:01am

dinesh pilani

**Manoj K Bhoir** · Answer 3 · 2013-09-08T19:20:00

Your command should be :

VB

cmd.CommandText = "INSERT INTO Inventory([Image]) VALUES(@Image)"

Also In Your Code

VB

DataPic_Update = MemStream.GetBuffer
MemStream.Read(DataPic_Update, 0, MemStream.Length) 'This Line Not Required because you are already retriving bytes form Stream on above line of code.

Also ensure that in database data type of Image column field is Binary.

For more information see following link. It will help you :
http://www.sattsoft.com/sourcecodes/details/1/2/save-and-retrieve-image-from-access-database-using-vb-net.html[^]
I hope it will help you. :)

basurajkumbhar · Answer 4 · 2013-09-13T02:58:00

Imports System.Data.OleDb
Imports System.IO

Public Class Profile

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Load_GridView()
    End Sub

    Private Sub btnBrowse_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBrowse.Click
        OpenFileDialog1.FileName = ""
        OpenFileDialog1.Filter = "JPEG files (*.jpg)|*.jpg|GIF files (*.gif)|*.gif|All files (*.*)|*.*"
        OpenFileDialog1.ShowDialog()
        Me.Label1.Text = OpenFileDialog1.FileName
        Me.PictureBox1.ImageLocation = Me.Label1.Text
    End Sub
    Private Sub btnAddNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddNew.Click
        Me.PictureBox1.ImageLocation = ""
        Me.txtDescription.Text = ""
        Me.txtDescription.Tag = 0
        Me.Label1.Text = ""

    End Sub
    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub
    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Save_Image()
        Form1_Load(sender, e)
    End Sub
    Private Sub Save_Image()
        Dim conn As New OleDbConnection
        Dim sConnString As String
        Dim cmd As New OleDbCommand
        Dim sSQL As String = String.Empty
        Dim arrImage() As Byte
        Dim myMs As New IO.MemoryStream
        Dim bSaveImage As Boolean = False
        Dim strImg As String = String.Empty

        'Try
        If Not IsNothing(Me.PictureBox1.Image) Then
            Me.PictureBox1.Image.Save(myMs, Me.PictureBox1.Image.RawFormat)
            arrImage = myMs.GetBuffer
            bSaveImage = True
        Else
            arrImage = Nothing
            bSaveImage = False
        End If
        If bSaveImage = True Then
            If Microsoft.VisualBasic.Right(Application.StartupPath, 1) = "\" Then
                sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "trial.mdb;Persist Security Info=False;"
            Else
                sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\trial.mdb;Persist Security Info=False;"
            End If
            conn = New OleDbConnection(sConnString)
            conn.Open()
            cmd.Connection = conn
            cmd.CommandType = CommandType.Text
            If Me.txtDescription.Tag = 0 Then
                cmd.CommandText = "INSERT INTO client ( firstname,lastname, [pictures],address,mobile ) VALUES(@firstname,@lastname, @img,@address,@mobile)"
            Else
                cmd.CommandText = "UPDATE client set firstname = @firstname,lastname=@lastname,[pictures]= @img, address=@address,mobile=@mobile  where id=@id"
            End If
            cmd.Parameters.Add("@firstname", OleDbType.VarChar).Value = Me.txtDescription.Text
            cmd.Parameters.Add("@lastname", OleDbType.VarChar).Value = Me.txtlastname.Text
            cmd.Parameters.Add("@img", OleDb.OleDbType.Binary).Value = arrImage
            cmd.Parameters.Add("@address", OleDbType.VarChar).Value = Me.txtaddress.Text
            cmd.Parameters.Add("@mobile", OleDbType.VarChar).Value = Me.txtmobile.Text
            cmd.Parameters.Add("@id", OleDbType.Integer).Value = Me.txtid.Text
            cmd.ExecuteNonQuery()
            MsgBox("Image has been save.")
        Else
            MsgBox("Please add an image")
        End If
        'Catch ex As Exception
        MsgBox(ErrorToString)
        'Finally
        conn.Close()
        ' End Try

    End Sub
    Private Sub Load_Image(ByVal iIMageID As Long)
        Dim conn As New OleDbConnection
        Dim sConnString As String
        Dim cmd As New OleDbCommand
        Dim dr As OleDbDataReader
        Dim arrImage() As Byte
        Dim myMS As New IO.MemoryStream

        Try
            If Microsoft.VisualBasic.Right(Application.StartupPath, 1) = "\" Then
                sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "trial.mdb;Persist Security Info=False;"
            Else
                sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\trial.mdb;Persist Security Info=False;"
            End If
            conn = New OleDbConnection(sConnString)
            conn.Open()
            cmd.Connection = conn
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "select id, firstname as description,lastname, [pictures],address,mobile from client where id= " & iIMageID

            dr = cmd.ExecuteReader
            If dr.HasRows Then
                While dr.Read
                    Me.txtDescription.Tag = dr("id")
                    Me.txtDescription.Text = dr("description")
                    Me.txtlastname.Text = dr("lastname")
                    arrImage = dr("pictures")
                    Me.txtaddress.Text = dr("address")
                    Me.txtmobile.Text = dr("mobile")
                    For Each ar As Byte In arrImage
                        myMS.WriteByte(ar)
                    Next
                    '
                    Me.PictureBox1.Image = System.Drawing.Image.FromStream(myMS)
                End While
            End If

        Catch ex As Exception
            MsgBox(ErrorToString)
        Finally
            conn.Close()
        End Try
    End Sub
    Private Sub Load_GridView()
        Dim conn As New OleDbConnection
        Dim sConnString As String
        Dim cmd As New OleDbCommand
        Dim da As New OleDbDataAdapter
        Dim dt As New DataTable

        Try
            If Microsoft.VisualBasic.Right(Application.StartupPath, 1) = "\" Then
                sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "trial.mdb;Persist Security Info=False;"
            Else
                sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\trial.mdb;Persist Security Info=False;"
            End If
            conn = New OleDbConnection(sConnString)
            conn.Open()
            cmd.Connection = conn
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "select id, firstname as description,lastname,address,mobile from client"
            da.SelectCommand = cmd
            da.Fill(dt)
            Me.DataGridView1.DataSource = dt
        Catch ex As Exception
            MsgBox(ErrorToString)
        Finally
            conn.Close()
        End Try
    End Sub


    Private Sub DataGridView1_DoubleClick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DataGridView1.DoubleClick
        Load_Image(Me.DataGridView1.Item(0, Me.DataGridView1.CurrentRow.Index).Value)
    End Sub
End Class

Error while inserting image into access database

4 solutions

Solution 1

Solution 4

Solution 5

Solution 6

Add your solution here

Preview 0