0

I'm using the following code:

Public Sub Add_File(Update_Table As Integer)

    Dim Fpath = My.Settings.Database_Connection_String



    Dim fd As OpenFileDialog = New OpenFileDialog()
    Dim strFileName As String

    fd.Title = "Open File Dialog"
    fd.InitialDirectory = "C:\"
    fd.Filter = "All files (*.*)|*.*|All files (*.*)|*.*"
    fd.FilterIndex = 2
    fd.RestoreDirectory = True
    fd.Multiselect = True


    If fd.ShowDialog() = DialogResult.OK Then
        Dim FileRow
        Try
            FileRow = Form1.Projects_DGV.SelectedCells.Item(0).RowIndex
        Catch ex As Exception
            FileRow = 0
        End Try

        Dim FileX = Form1.Projects_DGV.Item(0, FileRow).Value




        For Each file As String In fd.FileNames

            strFileName = file



            ' this code requires that your project have the following COM Reference:
            '     Microsoft Office 14.0 Access Database Engine Object Library

            Dim dbe As New Microsoft.Office.Interop.Access.Dao.DBEngine
            Dim db As Microsoft.Office.Interop.Access.Dao.Database = dbe.OpenDatabase(My.Settings.Database_Location)
            Dim rstRecord As Microsoft.Office.Interop.Access.Dao.Recordset = db.OpenRecordset( _
                    "SELECT * FROM [Projects] WHERE [Project ID]=" & FileX, _
                    Microsoft.Office.Interop.Access.Dao.RecordsetTypeEnum.dbOpenDynaset)
            rstRecord.Edit()
            Dim rstAttachments As Microsoft.Office.Interop.Access.Dao.Recordset2
            If Update_Table = 1 Then
                rstAttachments = rstRecord.Fields("Invoice").Value
            ElseIf Update_Table = 2 Then
                rstAttachments = rstRecord.Fields("Software Handover").Value
            ElseIf Update_Table = 3 Then
                rstAttachments = rstRecord.Fields("Other Documents").Value
            ElseIf Update_Table = 4 Then
                rstAttachments = rstRecord.Fields("Project Documents").Value
            End If



            rstAttachments.AddNew()
            Dim AttachmentData As Microsoft.Office.Interop.Access.Dao.Field2 = rstAttachments.Fields("FileData")
            AttachmentData.LoadFromFile(strFileName)
            rstAttachments.Update()
            rstAttachments.Close()
            rstRecord.Update()
            rstRecord.Close()
            db.Close()

        Next
        setupdatagrids()
    End If
End Sub

This code is supposed to allow me to add a file as an attachment to a database, and it works just fine in another 2 projects, but for some reason is not working now. The code fails on this line:

Dim rstRecord As Microsoft.Office.Interop.Access.Dao.Recordset = db.OpenRecordset( _
                        "SELECT * FROM [Projects] WHERE [Project ID]=" & FileX, _
                        Microsoft.Office.Interop.Access.Dao.RecordsetTypeEnum.dbOpenDynaset)

with the error:

An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in Alveare - Complete Management Tool.exe

Additional information: Too few parameters. Expected 1.

Anyone have any ideas why this would be, as I am passing a parameter?

Thanks

3
  • Is it possible that the value of FileX contains spaces and that is screwing up the execution of the SQL? Commented Feb 5, 2016 at 9:53
  • Should the value of FileX be quoted in the SQL anyway? Unless it's a integer, it should. I also notice that you must have Option Strict Off and that's never good. It allows various issues to sneak through that might other wise be caught earlier. Commented Feb 5, 2016 at 9:57
  • ah, in all of the other projects the primary key of the database has always been an ID integer, however, in this database, it is a string "F002 - X" for example. What would I need to change to get around this? Commented Feb 5, 2016 at 10:25

1 Answer 1

2

As the Project ID column contains text rather than numbers, change this:

"SELECT * FROM [Projects] WHERE [Project ID]=" & FileX

to this:

"SELECT * FROM [Projects] WHERE [Project ID]='" & FileX & "'"

or this:

String.Format("SELECT * FROM [Projects] WHERE [Project ID]='{0}'", FileX)

My preference would be the second option.

Sign up to request clarification or add additional context in comments.

1 Comment

Thanks, I used your first option and it works a treat

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.