[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-10-06
3
Medium Priority
?
1,058 Views
Last Modified: 2012-06-21
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.  
 
0
Comment
Question by:allej0ly
  • 2
3 Comments
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 36925616
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.
0
 

Accepted Solution

by:
allej0ly earned 0 total points
ID: 36980768
Error was in the namespace in the opendatabase statement:
C:\Inetpub\wwwroot\QMRP_Quarterly_Check\App_Data\QMRP_Quarterly.accdb
was pointing to a directory outside of the solution.  Correcting the path resolved the issue.
0
 

Author Closing Comment

by:allej0ly
ID: 37010538
Found solution
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Screencast - Getting to Know the Pipeline
Suggested Courses
Course of the Month20 days, 14 hours left to enroll

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question