Member Avatar for sushilsth 
  Private Sub btnsave_Click(sender As Object, e As EventArgs) Handles btnsave.Click

        'to check if the records already exists...
        Dim sqlQRY As String = "SELECT COUNT(namee) AS namecount from staff WHERE namee='" & TextBox7.Text & "'"
        Dim queryResult As Integer

        Dim com As New OleDbCommand(sqlQRY, conn)
        queryResult = com.ExecuteScalar()

        If queryResult > 0 Then
            MessageBox.Show("Data Already Exists!", "REMINDER ", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Else

            'if above if.. statement is false then else part is executed



            Dim arrImage() As Byte
            Dim strImage As String
            Dim myMs As New IO.MemoryStream
            '
            If Not IsNothing(Me.PictureBox1.Image) Then
                Me.PictureBox1.Image.Save(myMs, Me.PictureBox1.Image.RawFormat)
                arrImage = myMs.GetBuffer
                strImage = "?"
            Else
                arrImage = Nothing
                strImage = "NULL"
            End If

            Dim myCmd As New OleDb.OleDbCommand
            myCmd.Connection = conn

            myCmd.CommandText = "INSERT INTO staff(namee,address,contact,position,dob,entry_date,salary,picture) VALUES('" & Me.TextBox7.Text & "','" & Me.TextBox8.Text & "','" & Me.TextBox9.Text & "','" & Me.ComboBox5.Text & "','" & Me.DateTimePicker4.Text & "','" & Me.DateTimePicker3.Text & "','" & Me.TextBox10.Text & "'," & strImage & ")"
            If strImage = "?" Then
                myCmd.Parameters.Add(strImage, OleDb.OleDbType.Binary).Value = arrImage
            End If
            MessageBox.Show("Data sucessfully saved..", "Sucess!!!", MessageBoxButtons.OK, MessageBoxIcon.Information)
            myCmd.ExecuteNonQuery()
        End If

    End Sub

hi sir.. i couldnt figure out the problem heres my code for inserting images to access database. it always throws error oledbexception was unhandeled in insert into query.. whats the actual problem.. plz help me...

  • 3 Contributors
  • 4 Replies
  • 214 Views
  • 1 Month Discussion Span
  • Latest Post Latest Post by tareywolf

Dani AI

Generated

Common causes for the "OLEDBException" when inserting images into Access are: building SQL with string concatenation (dates and blobs get mangled), using the wrong parameter placeholder/order for OleDb, using MemoryStream.GetBuffer (returns extra unused bytes), and using an Access field type that doesn't accept raw binary (the newer Attachment type behaves differently than OLE Object). noted the problem and later resolved it without posting the fix; correctly pointed out that binary storage is possible. Below is a concise, safe pattern and a short checklist that addresses the usual pitfalls.

' assume "conn" is an open OleDb.OleDbConnection
Dim sql As String = "INSERT INTO staff(namee,address,contact,position,dob,entry_date,salary,picture) VALUES (?,?,?,?,?,?,?,?)"

Using cmd As New OleDb.OleDbCommand(sql, conn)
    cmd.Parameters.AddWithValue("p1", TextBox7.Text)
    cmd.Parameters.AddWithValue("p2", TextBox8.Text)
    cmd.Parameters.AddWithValue("p3", TextBox9.Text)
    cmd.Parameters.AddWithValue("p4", ComboBox5.Text)

    cmd.Parameters.Add(New OleDb.OleDbParameter("p5", OleDb.OleDbType.Date)).Value = DateTimePicker4.Value
    cmd.Parameters.Add(New OleDb.OleDbParameter("p6", OleDb.OleDbType.Date)).Value = DateTimePicker3.Value
    cmd.Parameters.Add(New OleDb.OleDbParameter("p7", OleDb.OleDbType.Decimal)).Value = Decimal.Parse(TextBox10.Text)

    If PictureBox1.Image IsNot Nothing Then
        Using ms As New IO.MemoryStream()
            PictureBox1.Image.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg)
            cmd.Parameters.Add(New OleDb.OleDbParameter("p8", OleDb.OleDbType.Binary)).Value = ms.ToArray()
        End Using
    Else
        cmd.Parameters.AddWithValue("p8", DBNull.Value)
    End If

    cmd.ExecuteNonQuery()  ' execute before showing success
End Using

Troubleshooting checklist

  • Use parameterized SQL with question-mark placeholders and add parameters in the same order.
  • Use MemoryStream.ToArray(), not GetBuffer(), to avoid trailing zeros.
  • Use DateTimePicker.Value (and OleDbType.Date) for dates; avoid embedding date text.
  • Verify the Access column type: store raw bytes into an OLE Object field; the Attachment field type (ACCDB) is different and not handled by a simple INSERT.
  • Enclose any potentially reserved column names in square brackets (for example [position]).
  • Show a success message only after ExecuteNonQuery succeeds, and catch and log the exception message for details.

This pattern should fix the common errors described in the thread and gives a ready example to adapt.

Recommended Answers

All 4 Replies

Member Avatar for sushilsth

thanx for reply.. yes access supports n i solved the problem..

Member Avatar for G_Waddell

No Worries - remember to mark as solved!

Member Avatar for tareywolf

Hey sushilsth, I have a similar issue. Can you post your solution?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.