Unable to save pdf file into access database-CodePudding

I'm trying to update an existing record in the access db table and save a pdf file. However, I'm getting "No value given for one or more parameters" error.

Please see the code below. Any help will be much appreciated.

Thanks

Dim strsql As String
Dim settings As String = ConfigurationManager.ConnectionStrings("ABCBO.My.MySettings.db_abcdealsConnectionString").ConnectionString
Dim con As New OleDbConnection
Private Sub printtopdf_Click(sender As Object, e As EventArgs) Handles printtopdf.Click
    con.ConnectionString = settings
    Try
        Dim fs As New FileStream("E:\NewApp\test1.pdf", FileMode.Open, FileAccess.Read)
        strsql = "UPDATE ABC_DEALS SET CONFIRMATION_COPY = @AT1 WHERE DEAL_TICKET = '" & TextBox5.Text & "'"
        Dim cmd As New OleDbCommand(strsql, con)
        Dim byteArr(CInt(fs.Length)) As Byte
        fs.Read(byteArr, 0, fs.Length)
        fs.Close()
        con.Open()
        cmd.Parameters.Add("@AT1", OleDbType.Binary).Value = byteArr
        cmd.ExecuteNonQuery()
        con.Close()
    Catch exc As Exception
        MsgBox(exc.Message)
        con.Close()
    End Try
End Sub

CodePudding user response：

FileStream, OleDbConnection, and OleDbCommand all have a Dispose method that needs to be called so unmanaged resources can be released. Vb.net provides Using...End Using blocks that will handle this for us and also close the connection and the stream. The Using block in the data access code includes both the connection and the command. Note the comma after the connection line.

You are trying to do too many things in the click method. Separate the data access code from the rest.

Always use parameters. User input like TextBox5.Text could introduce malicious code into your database. Parameter values are not considered executable code by the database.

Pass the connection string to the constructor of the connection.

Pass the CommandText and the Connection to the constructor of the command.

Don't open the connection until directly before the Execute...

Even if this doesn't solve the problem, it will offer you a clearer idea of where the problem is. Put in a break point and step through the code checking for variable values.

Private settings As String = ConfigurationManager.ConnectionStrings("ABCBO.My.MySettings.db_abcdealsConnectionString").ConnectionString

Private Sub printtopdf_Click(sender As Object, e As EventArgs) Handles Button1.Click 'printtopdf.Click
    Dim byteArr As Byte()
    Dim path As String = "E:\NewApp\test1.pdf"
    Try
        Using fs As New FileStream(path, FileMode.Open, FileAccess.Read)
            ReDim byteArr(CInt(fs.Length))
            fs.Read(byteArr, 0, CInt(fs.Length))
        End Using
        Dim Updated = UpdateDatabase(byteArr, CInt(TextBox5.Text))
        MessageBox.Show($"{Updated} has been successfully updated.")
    Catch exc As Exception
        MsgBox(exc.Message)
    End Try
End Sub

Private Function UpdateDatabase(PDF As Byte(), Ticket As Integer) As Integer
    Dim strsql = "UPDATE ABC_DEALS SET CONFIRMATION_COPY = @AT1 WHERE DEAL_TICKET = @Ticket;"
    Dim RecordsUpdated As Integer
    Using con As New OleDbConnection(settings),
         cmd As New OleDbCommand(strsql, con)
        cmd.Parameters.Add("@AT1", OleDbType.Binary).Value = PDF
        cmd.Parameters.Add("@Ticket", OleDbType.Integer).Value = Ticket
        con.Open()
        RecordsUpdated = cmd.ExecuteNonQuery()
    End Using
    Return RecordsUpdated
End Function

EDIT

Private Function GetByteArrayFromFile(FilePath As String) As Byte()
    Dim file As Byte()
    Using stream As New FileStream(FilePath, FileMode.Open, FileAccess.Read),
            reader As New BinaryReader(stream)
        file = reader.ReadBytes(CInt(stream.Length))
    End Using
    Return file
End Function

Private Sub printtopdf_Click(sender As Object, e As EventArgs) Handles Button1.Click 'printtopdf.Click
    Dim path As String = "E:\NewApp\test1.pdf"
    Dim byteArr = GetByteArrayFromFile(path)
    Try
        Dim Updated = UpdateDatabase(byteArr, CInt(TextBox5.Text))
        MessageBox.Show($"{Updated} has been successfully updated.")
    Catch exc As Exception
        MsgBox(exc.Message)
    End Try
End Sub

EDIT 2

I see 3 places that the code needs to be changed to accomidate a Long. I have added a validation TryParse to the code to make sure the input is the correct type.

Old code 1

Dim Updated = UpdateDatabase(byteArr, CInt(TextBox5.Text))

New code 1

    Dim ticket As Long
    If Not Long.TryParse(TextBox5.Text, ticket) Then
        MessageBox.Show("Please enter a valid ticket number")
        Exit Sub
    End If
    Dim Updated = UpdateDatabase(byteArr, ticket)

Old code 2

Private Function UpdateDatabase(PDF As Byte(), Ticket As Integer) As Integer

New code 2 Note: The return type stays Integer.

Private Function UpdateDatabase(PDF As Byte(), Ticket As Long) As Integer

Old code 3

cmd.Parameters.Add("@Ticket", OleDbType.Integer).Value = Ticket

New code 3 It seems the the OleDbType.BigInt maps to Int64. In Access the field should be Number 8 bytes. Not positive of this. You may have to play with it a bit.

    cmd.Parameters.Add("@Ticket", OleDbType.BigInt).Value = Ticket