vb.net dao connection to access 2007 database with attachment field
Posted on 2011-10-06
I am working on an asp.net project in VS2010 using vb.NET (not VBA) designing an interface to allow a user to upload a file to an access 2007 table attachment field. My understanding is that in order to interface with a table with an attachment field you must use DAO to connect to the database so that you can expose the attachment field properties. When I try to open the DAO recordset I am getting "COM exception was unhandled by user code" with a message stating "Too few parameters. Expected 3". The error code is -2146825227 and the stacktrace message is "at dao.Database.OpenRecordset(String Name, Object Type, Object Options, Object LockEdit)"
I have also added the Microsoft.Office.Interop.Access. and Imports Microsoft.Office.Interop.Access references to the project and have these imports specified on the page:
Below is the code that I am trying to use:
' Set up DAO database connection
Dim strSQL As String = "SELECT Quarterly_Due.DueDate, Quarterly_Due.Register, Quarterly_Due.QMRP_Name, Quarterly_Due.Review_Upload, Quarterly_Due.Uploader, Quarterly_Due.CheckInTime, Quarterly_Due.CheckInFlg FROM(Quarterly_Due) WHERE (((Quarterly_Due.DueDate)='" & DueDate_var & "') AND ((Quarterly_Due.Register)='" & RegNum_var & "'));"
Dim dbe = New DBEngine()
Dim db As dao.Database
Dim rst As dao.Recordset
Dim rstChld As dao.Recordset
Dim fldAttach As Field2
Dim fldCheckFlg As Field
Dim fldTimeStamp As Field
db = dbe.OpenDatabase("C:\Inetpub\wwwroot\QMRP_Quarterly_Check\App_Data\QMRP_Quarterly.accdb", False, False, "")
rst = db.OpenRecordset(strSQL, dao.RecordsetTypeEnum.dbOpenDynaset, dao.RecordsetOptionEnum.dbConsistent, dao.LockTypeEnum.dbOptimistic) <<-------- This is the line where the exception is thrown
rstChld = rst.Fields("Review_Upload").Value
fldAttach = rstChld.Fields("FileData")
rst.Fields("CheckInTime").Value = Now()
rst.Fields("CheckInFlg").Value = True
rst.Fields("UpLoader").Value = UpLoader_var
The sql statement executes without error in the Access query design window and enumerates to:
"SELECT Quarterly_Due.DueDate, Quarterly_Due.Register, Quarterly_Due.QMRP_Name, Quarterly_Due.Review_Upload, Quarterly_Due.Uploader, Quarterly_Due.CheckInTime, Quarterly_Due.CheckInFlg FROM(Quarterly_Due) WHERE (((Quarterly_Due.DueDate)='10/13/2011') AND ((Quarterly_Due.Register)='10659'));"
I have played around with modifying that and removing the reference to the attachment field (Review_Upload) in the sql statement, but I continue to get the exception message, but the number of parameters missing message will change from 3 to 1. At this point I am not sure whether I am dealing with a problem with the sql statement or a syntax error in the DAO connection code. I have scoured all of the resources that I can find but have not been able to resolve the problem. Any assistance would be greatly appreciated.