No value given for one or more required parameters while trying to access a picture file from an Acc-CodePudding

Im doing a school project. and I was testing a login form for my app. I'm trying separately from my login form and a profile pic form. I have successfully managed to save the image to the access database but I have had quite a few problems trying to display it on a textbox on my form.

This is the whole app code:

Imports System.Data.OleDb
Imports System.IO

Public Class Form2

    Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\geral\source\repos\BD de imagenes\BD de imagenes\DBImagenes.mdb")
    Dim cmd As New OleDbCommand
    Dim sql As String

    Dim da As New OleDb.OleDbDataAdapter
    Dim result As Integer

    Private Sub saveimage(sql As String)
        Try
            Dim arrimage() As Byte
            Dim mstream As New System.IO.MemoryStream
            PictureBox1.Image.Save(mstream, System.Drawing.Imaging.ImageFormat.Png)
            arrimage = mstream.GetBuffer()
            Dim Filesize As UInt32
            Filesize = mstream.Length
            mstream.Close()
            con.Open()
            cmd = New OleDbCommand
            With cmd
                .Connection = con
                .CommandText = sql
                .Parameters.AddWithValue("@Imagen", arrimage)
                .Parameters.Add("@Nombre", OleDbType.VarChar).Value = TextBox1.Text
                .ExecuteNonQuery()

            End With
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            con.Close()
        End Try
    End Sub

    'End Try

    Public conex As New OleDbConnection()
    Public Sub conexion()
        conex.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\geral\source\repos\BD de imagenes\BD de imagenes\DBImagenes.mdb"
        conex.Open()
    End Sub
    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles BTNGuardar.Click
        sql = "Insert into TBImg (Imagen, Nombre) Values (@Imagen, @Nombre)"
        'sql = "Insert into TBImg (Imagen) Values (@Imagen)"
        saveimage(sql)
        MsgBox("Image has been saved in the database")
    End Sub

    Private Sub BtnExaminar_Click(sender As Object, e As EventArgs) Handles BtnExaminar.Click
        OpenFileDialog1.Filter = "Imagenes JPG|*.jpg|Imagenes PNG|*.png"
        OpenFileDialog1.RestoreDirectory = True
        If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then
            PictureBox1.Image = Image.FromFile(OpenFileDialog1.FileName)

        End If
    End Sub

    Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged

    End Sub

    Private Sub PictureBox1_Click(sender As Object, e As EventArgs) Handles PictureBox1.Click
        Try
            With OpenFileDialog1

                'CHECK THE SELECTED FILE IF IT EXIST OTHERWISE THE DIALOG BOX WILL DISPLAY A WARNING.
                .CheckFileExists = True

                'CHECK THE SELECTED PATH IF IT EXIST OTHERWISE THE DIALOG BOX WILL DISPLAY A WARNING.
                .CheckPathExists = True

                'GET AND SET THE DEFAULT EXTENSION
                .DefaultExt = "jpg"

                'RETURN THE FILE LINKED TO THE LNK FILE
                .DereferenceLinks = True

                'SET THE FILE NAME TO EMPTY 
                .FileName = ""

                'FILTERING THE FILES
                .Filter = "(*.jpg)|*.jpg|(*.png)|*.png|(*.jpg)|*.jpg|All files|*.*"
                'SET THIS FOR ONE FILE SELECTION ONLY.
                .Multiselect = False

                'SET THIS TO PUT THE CURRENT FOLDER BACK TO WHERE IT HAS STARTED.
                .RestoreDirectory = True

                'SET THE TITLE OF THE DIALOG BOX.
                .Title = "Select a file to open"

                'ACCEPT ONLY THE VALID WIN32 FILE NAMES.
                .ValidateNames = True

                If .ShowDialog = DialogResult.OK Then
                    Try
                        PictureBox1.Image = Image.FromFile(OpenFileDialog1.FileName)
                    Catch fileException As Exception
                        Throw fileException
                    End Try
                End If

            End With
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, Me.Text)
        End Try

    End Sub

    Private Sub Form2_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        conexion()
        PictureBox1.SizeMode = PictureBoxSizeMode.StretchImage
    End Sub

    Private Sub Label1_Click(sender As Object, e As EventArgs) Handles Label1.Click

    End Sub

    Private Sub BtnBuscar_Click(sender As Object, e As EventArgs) Handles BtnBuscar.Click
        Dim arrimage() As Byte
        Dim conn As New OleDb.OleDbConnection
        Dim Myconnection As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\geral\source\repos\BD de imagenes\BD de imagenes\DBImagenes.mdb"
        conn.ConnectionString = Myconnection
        conn.Open()
        sql = "Select * from TBImg where Nombre=" & (TBBuscar.Text)

        Dim cmd As New OleDbCommand
        With cmd
            .Connection = conex
            .CommandText = sql
        End With
        Dim publictable As New DataTable
        Try
            da.SelectCommand = cmd
            da.Fill(publictable)
            TextBox1.Text = publictable.Rows(1).Item("Nombre").ToString
            arrimage = publictable.Rows(1).Item("Imagen")
            Dim mstream As New System.IO.MemoryStream(arrimage)
            PictureBox1.Image = Image.FromStream(mstream)
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            da.Dispose()
            conn.Close()
        End Try
    End Sub
End Class

the relevant part is at Private Sub BtnBuscar_Click.

I'm trying to search in a textbox for the name that I saved the image with. but I haven't had success all I get is the error of the title.

this is how my database looks like the images are saved as an ole object

This is the error I get

I was following this tutorial https://www.youtube.com/watch?v=zFdjp39mfhQ

but he didn't quite explain how to use the:

TextBox1.Text = publictable.Rows(0).Item(1)
arrimage = publictable.Rows(0).Item(1)'

don't know if it's the cause of the issue.

instructions. The reason why my code looks different is that I was trying to stuff to see if I could make it work.

I have tried to search for answers and people suggest that I may have put the table name wrong or the column but I copied the name exactly how it is in the table with ctrl c and ctrl v.

what I want is that when I type the name in the column name of the database that it brings the designated picture stored as ole object onto my desired picture box on my form app.

Needless to say, I'm not that experienced with vb.net and SQL, Acces. I'm just following tutorials for being able to complete the project.

CodePudding user response：

Do not declare connections or commands or datareaders at the class level. They all need to have their Dispose methods called. Using blocks will have the declare, closing and disposing even if there is an error. Streams also need Using blocks.

Defaults for an OpenFiledialog

Multiselect is False
CheckFileExists is True
CheckPathExists is True
DereferenceLinks is True
ValidateNames is True
FileName is ""

Unless you are getting paid by the line, it is unnecessary to reset these values to their defaults.

I have alerted your Filter to exclude All Files. You also had jpg appearing twice.

I declared a variable to hold the file extension, PictureFormat, of the image file so you could provide the proper parameter for ImageFormat.

When you retrieve the image field from the database it comes as an Object. To get the Byte() a DirectCast should work.

Private PictureFormat As String

Private Sub PictureBox1_Click(sender As Object, e As EventArgs) Handles PictureBox1.Click
    FillPictureBoxFromFile()
End Sub

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles BTNGuardar.Click
    Try
        saveimage()
    Catch ex As Exception
        MessageBox.Show(ex.Message)
        Exit Sub
    End Try
    MsgBox("Image has been saved in the database")
End Sub

Private cnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\geral\source\repos\BD de imagenes\BD de imagenes\DBImagenes.mdb"

Private Sub saveimage()
    Dim arrimage() As Byte
    Using mstream As New System.IO.MemoryStream
        If PictureFormat.ToLower = ".png" Then
            PictureBox1.Image.Save(mstream, System.Drawing.Imaging.ImageFormat.Png)
        ElseIf PictureFormat.ToLower = ".jpg" Then
            PictureBox1.Image.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg)
        End If
        arrimage = mstream.GetBuffer()
        Dim Filesize As Long
        Filesize = mstream.Length
    End Using
    Using con As New OleDbConnection(cnStr),
            cmd As New OleDbCommand("Insert into TBImg (Imagen, Nombre) Values (@Imagen, @Nombre)", con)
        With cmd
            .Parameters.Add("@Imagen", OleDbType.Binary).Value = arrimage
            .Parameters.Add("@Nombre", OleDbType.VarChar).Value = TextBox1.Text
            con.Open()
            .ExecuteNonQuery()
        End With
    End Using
End Sub

    Private Sub BtnExaminar_Click(sender As Object, e As EventArgs) Handles BtnExaminar.Click
    FillPictureBoxFromFile()
End Sub

Private Sub BtnBuscar_Click(sender As Object, e As EventArgs) Handles BtnBuscar.Click
    Dim dt As DataTable
    Try
        dt = GetDataByName(TBBuscar.Text)
    Catch ex As Exception
        MessageBox.Show(ex.Message)
        Exit Sub
    End Try
    TextBox1.Text = dt(0)("Nombre").ToString
    Dim arrimage = DirectCast(dt(0)("Imagen"), Byte())
    Dim mstream As New System.IO.MemoryStream(arrimage)
    PictureBox1.Image = Image.FromStream(mstream)
End Sub

Private Function GetDataByName(name As String) As DataTable
    Dim dt As New DataTable
    Using conn As New OleDb.OleDbConnection(cnStr),
            cmd As New OleDbCommand("Select * from TBImg where Nombre= @Buscar", conn)
        cmd.Parameters.Add("@Buscar", OleDbType.VarChar).Value = TBBuscar.Text
        conn.Open()
        Using reader = cmd.ExecuteReader
            dt.Load(reader)
        End Using
    End Using
    Return dt
End Function

Private Sub FillPictureBoxFromFile()
    With OpenFileDialog1
        .Filter = "(*.jpg)|*.jpg|(*.png)|*.png"
        .RestoreDirectory = True
        .Title = "Select a file to open"
        If .ShowDialog = DialogResult.OK Then
            PictureBox1.Image = Image.FromFile(OpenFileDialog1.FileName)
        End If
        PictureFormat = Path.GetExtension(OpenFileDialog1.FileName)
    End With
End Sub