Click here to Skip to main content
15,891,513 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi.. I learn how to make database program using VB.Net and I use access 2010 (accdb extension) as my database. One of the field in tbl_students is OLE Object where it saves images. Every time I insert an image (using SQL Statement and MemoryStream) the program closes itself immediately. So, instead of access 2010 I use access 2003 format (mdb extension) and it runs well. My question is; Is it a bug in access 2007/2010 to save images as Ole Object??
Note: My code is well tested in sql server 2005 and access 2003 and there's no single problem. The problem appears only when I use access 2007/2010. Thank you.

Private Sub SaveDataImage
    Dim sqlSaveImage As String = "INSERT INTO tblpasien VALUES(@no,@nama,@alamat,@usia,@kelamin,@gambar)"
        Try
            Dim OdbCommand As OleDbCommand = New OleDbCommand(sqlSaveImage,Koneksi)
            If isTextEmpty(Me) = False  Then '<-- This is my own sub procedure
                With OdbCommand.Parameters
                    .AddWithValue("@no",txtNoPasien.Text)
                    .AddWithValue("@nama",txtNama.Text)
                    .AddWithValue("@alamat",txtAlamat.Text)
                    .AddWithValue("@usia",txtUsia.Text)
                    .AddWithValue("@kelamin", cmbKelamin.Text)
                End With

                Dim StreamImage As New MemoryStream()
                PictureBox1.Image.Save(StreamImage, PictureBox1.Image.RawFormat)
                Dim BufferImage As byte() = StreamImage.GetBuffer 
                OdbCommand.Parameters.AddWithValue("@gambar", SqlDbType.Image).Value = BufferImage

                'Execution
                Koneksi.Open 
                OdbCommand.ExecuteNonQuery
                Koneksi.Close 

                'Clear text
                ClearAllText(Me)
                txtNoPasien.Focus 

                'Show in grid after saving
                mdlConnection.ShowPatientsGrid(DGPatients)
                GridPlan

                'pict variable must be empty after execution
                pict = String.Empty 
                Me.PictureBox1.Image = My.Resources.Label
            Else
                MsgBox("Entri data kosong",vbInformation)
                pict = String.Empty
                Exit Sub
            End if
            
        Catch ex As Exception
            Koneksi.Close 
            MsgBox("Tidak bisa menyimpan data. Terdapat kesalahan penulisan",vbExclamation)
        End Try

End Sub
Posted
Updated 19-Aug-11 6:11am
v3
Comments
Kschuler 17-Aug-11 11:22am    
Try clicking Improve Question and add the code you are using that works in 2003 but not 2010. Then perhaps you will get some suggestions.
derodevil 19-Aug-11 12:04pm    
Ok. Let's see it. I've improved the question

1 solution

Without seeing your code that is adding this data, it's impossible to tell you where you went wrong.

No, there is no bug with inserting images into an Access 2007/2010 database.
 
Share this answer
 
Comments
derodevil 19-Aug-11 12:05pm    
I have improved my question.
Dave Kreskowiak 19-Aug-11 13:01pm    
First, don't use ODBC, use OleDB with Access databases instead. Second, the Jet database engine (Access) doesn't support named parameters and I believe the parameter place hold was a question mark. Next, if you're going to use .AddWithValue, specify the type of the SQL parameter. Since your value is a String, the method makes certain assumptions about what the type and size of the underying parameter is. If this assumption is not valid, the code will bomb.

I recommend against using AddWithValue and instead using one of the Add methods that allows you to describe the SQL parameter type and size so there is no confusion about what's expected.
derodevil 19-Aug-11 13:31pm    
As you can see, I'm using OleDB. If the problem is the type of the SQL parameter why does this code seem to be perfect in SQL server and Access 2003 and doesn't work correctly in Access 2010?
Dave Kreskowiak 19-Aug-11 14:25pm    
My bad, and yours. Your variable names all start with OdbC... ;)

In your database, are the columns defining this table in the exact same order as the 2003 version?
derodevil 20-Aug-11 8:43am    
Yes, the columns are exact the same as the 2003 version. Actually, I used 2010 version and since it didn't work in that version I converted it to 2003 and it works fine.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900