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.A ccess. and Imports Microsoft.Office.Interop.A ccess references to the project and have these imports specified on the page:
Imports Microsoft.Office.Interop.A ccess.Dao
Imports Microsoft.Office.Interop.A ccess
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_Uploa d, 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:\Inetp ub\wwwroot \QMRP_Quar terly_Chec k\App_Data \QMRP_Quar terly.accd b", False, False, "")
rst = db.OpenRecordset(strSQL, dao.RecordsetTypeEnum.dbOp enDynaset, dao.RecordsetOptionEnum.db Consistent , dao.LockTypeEnum.dbOptimis tic) <<-------- 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(Fil eUpload1.P ostedFile. FileName)
rst.Fields("CheckInTime"). Value = Now()
rst.Fields("CheckInFlg").V alue = True
rst.Fields("UpLoader").Val ue = 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_Uploa d, Quarterly_Due.Uploader, Quarterly_Due.CheckInTime, Quarterly_Due.CheckInFlg FROM(Quarterly_Due) WHERE (((Quarterly_Due.DueDate)= '10/13/201 1') 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.
I have also added the Microsoft.Office.Interop.A
Imports Microsoft.Office.Interop.A
Imports Microsoft.Office.Interop.A
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_Uploa
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:\Inetp
rst = db.OpenRecordset(strSQL, dao.RecordsetTypeEnum.dbOp
rst.Edit()
rstChld = rst.Fields("Review_Upload"
rstChld.AddNew()
fldAttach = rstChld.Fields("FileData")
fldAttach.LoadFromFile(Fil
rst.Fields("CheckInTime").
rst.Fields("CheckInFlg").V
rst.Fields("UpLoader").Val
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_Uploa
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Found solution