INSERT INTO statement Error

[miss_indie]

I'm modifying a code which can save an image into the database. I've changed everything that i need to change but when i click the save button the error says that i have an error in the INSERT INTO statement. Here is the code:

Dim conn As New OleDbConnection(cn)

        'save Image to database
        Try
            If Me.TextBox1.Text.Trim = "" Then
                MsgBox("Please browse a picture to save!", MsgBoxStyle.Information, "No Picture to Save")
                Exit Sub
            End If

            Dim ms As New MemoryStream
            Me.imgsave.Image.Save(ms, Me.imgsave.Image.RawFormat)

            Dim arrayImage() As Byte = ms.GetBuffer
            ms.Close() ' Closes the Memory Stream

            Dim nStr As String = Me.TextBox1.Text.Substring(Me.TextBox1.Text.LastIndexOf("\") + 1)

            Dim strQuery As String = "INSERT INTO pic (Image) VALUES(@Image)"

            Dim objcommand As New OleDbCommand(strQuery, conn)
            With objcommand
                .Parameters.Add(New OleDbParameter("@Image", SqlDbType.Image)).Value = arrayImage
            End With


            conn.Open()
            objcommand.ExecuteNonQuery()
            MessageBox.Show("Image Saved Into the DataBase", "Save Successfully", MessageBoxButtons.OK, MessageBoxIcon.Information)
            conn.Close()

        Catch ex As Exception

            MsgBox("Error: " & ex.Source & ": " & ex.Message, MsgBoxStyle.OkOnly, "Connection Error !!")
        End Try

The name of my database table is pic and Image is the name of the column. Please. thank you for your replies everyone. :) I just don't know what's wrong with my statement.

[Animal Mother]

Hi

First I must inform you that MS Access only supports Windows Bitmap (.bmp) and Device Independent Bitmap (.dib) images.

and Second . . . . . . Well here you go :twisted:

Dim DatabaseConnection As New OleDbConnection
        Dim DatabaseCommand As OleDbCommand

        DatabaseConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\YourDBFile.mdb;User Id=YourUsername;Password=YourPassword;"
        DatabaseConnection.Open()

        DatabaseCommand = DatabaseConnection.CreateCommand()
        DatabaseCommand.CommandText = "INSERT INTO ImageTable(ImageName, Image) VALUES (@Name, @Image)"
        Dim imgByteArray() As Byte
        Try
            Dim stream As New MemoryStream
            Dim bmp As New Bitmap(stream)

            bmp.Save(stream, ImageFormat.Jpeg)
            imgByteArray = stream.ToArray()
            stream.Close()

            DatabaseCommand.Parameters.AddWithValue("@Name", name)
            DataBaseCommand.Parameters.AddWithValue("@Image", imgByteArray)

            If DirectCast(DatabaseCommand.ExecuteNonQuery(), Integer) > 0 Then
                Return True
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Return False
        End Try
        Try	
            DatabaseConnection.Close()
            DatabaseCommand.Dispose()
            DatabaseConnection.Dispose()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Return False
        End Try

Hope this Helps

[debasisdas]

are you getting any error with the code ?

check for your DB field type and the data that you are passing in the SQL.

[miss_indie]

yay! :) thanks so much for the sample code. my problem was solved. another is. how can i detect if the image is redundant?
for example. the user will add new item on the database. different name but same image?. how can i detect if the image is already there?.

that's another problem now on my system.

[Animal Mother]

I'm glad it worked :)

I don't see a way to check for duplicate images in the database unless you use the size of the image but then it will question every image with that size. The only way to see if it is a duplicate will be via it's name and another problem to remember the image could be the same but the extension might also differ.

Anyway that's up to you.

Glad I could help.

Please remember to mark this thread a solved.

[debasisdas]

different name but same image?. how can i detect if the image is already there?.

That will be difficult to implement unless you are using some hi-end technology for exact image comparison.

almost all the applications will accept the new file as a new time if the name is different, and in most of the cases names also does not matter because the image is stored as an image without bothering about what the source image file name was.

[nkotbox]

What if the images were converted to binary and then checked against one another?

[debasisdas]

That is not a feasible option.

[nkotbox]

I thought this was a front end app utilizing MS access database. If so then you can convert to base64, compare strings and then decide to use pic or not. If this is a database only, I appologize.

Public Function ToBase64(ByVal data() As Byte) As String
        If data Is Nothing Then Throw New ArgumentNullException("data")
        Return Convert.ToBase64String(data)
    End Function

    Public Function FromBase64(ByVal base64 As String) As Byte()
        If base64 Is Nothing Then Throw New ArgumentNullException("base64")
        Return Convert.FromBase64String(base64)
    End Function