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
FileXcontains spaces and that is screwing up the execution of the SQL?FileXbe quoted in the SQL anyway? Unless it's a integer, it should. I also notice that you must haveOption Strict Offand that's never good. It allows various issues to sneak through that might other wise be caught earlier.