Link to home
Start Free TrialLog in
Avatar of allej0ly
allej0ly

asked on

vb.net dao connection to access 2007 database with attachment field

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:
Imports Microsoft.Office.Interop.Access.Dao
Imports Microsoft.Office.Interop.Access
Imports System.Net.Mail


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
        rst.Edit()
        rstChld = rst.Fields("Review_Upload").Value
        rstChld.AddNew()
        fldAttach = rstChld.Fields("FileData")
        fldAttach.LoadFromFile(FileUpload1.PostedFile.FileName)
        rst.Fields("CheckInTime").Value = Now()
        rst.Fields("CheckInFlg").Value = True
        rst.Fields("UpLoader").Value = UpLoader_var
        rstChld.Update()
        rstChld.Close()
        rst.Update()
        rst.Close()

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.  
 
Avatar of Nasir Razzaq
Nasir Razzaq
Flag of United Kingdom of Great Britain and Northern Ireland image

Do you have to use Access? And do you have to store file in Access? One option is to store only path of file in Access.
ASKER CERTIFIED SOLUTION
Avatar of allej0ly
allej0ly

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of allej0ly
allej0ly

ASKER

Found solution