Calling MS Access Stored Queries with Parameters using DAAB

Topics: Data Access Application Block
Aug 16, 2010 at 1:21 AM

I wanted to find out if it is possible to call MS Access Stored Queries with parameters using DAAB.

I am using the Northwind sample database to test this scenario I have created the following Stored Query with parameter in MS Access:

PARAMETERS FirstName Text ( 255 );
SELECT Employees.ID
FROM Employees
WHERE (((Employees.[First Name])=[@FirstName]));

This query is stored with name: GetEmployeeIDByName

 

I have created a wrapper over the DAAB to allow access to various databases like SQL, Oracle, any OLEDB and and ODBC database.

Below is the sample code for my test:

Database db = new GenericDatabase("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Database\Access\Northwind 20071.accdb",OleDbFactory.Instance);

DbCommand cmd = db.GetStoredProcedure("GetEmployeeIDByName");

db.AddInParameter(cmd,"@FirstName",DbType.String,40);

object employeeID = db.ExecuteScalar();

I get an error Invalid Operation. I am not sure if I am calling the stored Queries correctly as I am able to call Stored Queries that do not have any parameter without any errors.

Aug 16, 2010 at 11:50 AM

I got the same error trying to reproduce your problem. Though, I notice that this only happen if I used stored proc (GetStoredProcCommand) and does not if used a Sql String (GetSqlStringCommand). Now I suspect it could probably on how the stored proc is created in Access since I'm not so sure if I did it right. Can you tell me how did you made yours? I'll try to dig more about this and let you know the updates. 

Gino Terrado
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com
  

Aug 17, 2010 at 1:36 AM

The way I created the Stored Query with parameter within Access was through C# code using Enterprise Library. Below is the code I used to create the SP 

Database db = new GenericDatabase("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Database\Access\Northwind 20071.accdb",OleDbFactory.Instance);

 string spCode = "CREATE PROC GetEmployeeIDByFirstName(FirstName VARCHAR(40)) AS SELECT ID FROM Employees Where [First Name] = @FirstName;";  

DbCommand cmd = db.GetStringCommand(spCode );       

 int created = db.ExecuteNonQuery();

once this code was executed it created the Stored Query within Access and when I opened that Query within Access following code was created:

PARAMETERS FirstName Text ( 255 );
SELECT Employees.ID
FROM Employees
WHERE (((Employees.[First Name])=[@FirstName]));

Hope this helps.


Aug 17, 2010 at 4:13 AM

It seems like th problem would be the parameter.  PARAMETERS FirstName Text ( 255 );    And then you use @FirstName in your Where clause.  Please try correcting the parameter name in the stored procedure and in the call to db.AddInParameter.

 

Sarah Urmeneta
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com
  

Aug 17, 2010 at 4:25 AM

Even after correcting the name of the parameter i still get the error of Invalid Operation when connecting to Access database via OleDb

Aug 17, 2010 at 4:41 AM

Hmm, everything works fine on my end.  Have you tried directly executing the stored procedure inside MS Access and pass the same parameter? See if there would be any errors.

 

Sarah Urmeneta
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com
  

Aug 17, 2010 at 4:44 AM

I can execute the Stored Procedure directly within MS Access but I get the error if I try to execute the MS Access SP using Enterprise Library DAAB from my class library within C#. Not sure as to why would that fail.

Aug 17, 2010 at 4:51 AM
Edited Aug 17, 2010 at 4:52 AM

To set things clear here's how your sp GetEmployeeIDByName looked like assuming you used FirstName as parameter:

PARAMETERS FirstName Text ( 255 );
SELECT Employees.ID
FROM Employees
WHERE (((Employees.[First Name])=[FirstName]));

Your call to AddInParameter now looks like:

db.AddInParameter(cmd,"FirstName",DbType.String,40);

You're getting the Invalid Operation in db.ExecuteScalar(cmd).  Could you include the whole exception message so as to be able to understand further what is the error.

 

Sarah Urmeneta
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com
  

Aug 17, 2010 at 4:58 AM

Here is the detailed error:

Error: System.Data.OleDb.OleDbException:"Unspecified error\r\nInvalid operation."

StackTrace: "at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)

     at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)

   at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)

             at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)

         at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteScalar()

   at Microsoft.Practices.EnterpriseLibrary.Data.Database.DoExecuteScalar(IDbCommand command)  in e:\\Builds\\EntLib\\Latest\\Source\\Blocks\\Data\\Src\\Data\\Database.cs:line 476

   at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteScalar(DbCommand command)  in e:\\Builds\\EntLib\\Latest\\Source\\Blocks\\Data\\Src\\Data\\Database.cs:line 998"

 

Hope this give more information.

Aug 17, 2010 at 5:01 AM

Unfortunately, it doesn't.  Is it possible for you to send a repro project? 

 

Sarah Urmeneta
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com
  

Aug 17, 2010 at 5:52 AM

i will send test project to entlib.support@avanade.com with link to the current thread.

The email contains a VS 2010 solution that includes a Class library and a Test project. Also make sure you have the Northwind database for Access available.

Aug 17, 2010 at 5:59 AM
Hello There,

As discussed please find attached the MSAccessTest.txt which is a zip file test project using the Enterprise Library. Please let me know if you need any more information from my side.

Rename MSAccessTest.txt to MSAccess.ZIP before extracting.

On Tue, Aug 17, 2010 at 2:01 PM, AvanadeSupport <notifications@codeplex.com> wrote:

From: AvanadeSupport

Unfortunately, it doesn't.  Is it possible for you to send a repro project? 

 

Sarah Urmeneta
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com
  

Read the full discussion online.

To add a post to this discussion, reply to this email (entlib@discussions.codeplex.com)

To start a new discussion for this project, email entlib@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on CodePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at CodePlex.com




--
Sincerely,

Manthan Gogari
Aug 17, 2010 at 6:37 AM

It' still not on our inbox yet.  Could you send an email without any attachment so I could email you an alternate email address where you could re-send it?

 

Sarah Urmeneta
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com
  

Aug 17, 2010 at 6:44 AM

i think the email went to entlib@discussions.codeplex.com i have sent the email again to entlib.support@avanade.com

Aug 17, 2010 at 6:44 AM
Hello There,

As discussed please find attached the MSAccessTest.txt which is a zip file test project using the Enterprise Library. Please let me know if you need any more information from my side.

Rename MSAccessTest.txt to MSAccess.ZIP before extracting.

On Tue, Aug 17, 2010 at 3:37 PM, AvanadeSupport <notifications@codeplex.com> wrote:

From: AvanadeSupport

It' still not on our inbox yet.  Could you send an email without any attachment so I could email you an alternate email address where you could re-send it?

 

Sarah Urmeneta
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com
  

Read the full discussion online.

To add a post to this discussion, reply to this email (entlib@discussions.codeplex.com)

To start a new discussion for this project, email entlib@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on CodePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at CodePlex.com




--
Sincerely,

Manthan Gogari
Aug 17, 2010 at 7:35 AM

I ran your test right away and found out there isn’t any call to the CreateSp method so I went ahead and modify the test so that it calls it first. Now, when I did this, the CreateSp method throws an exception in the call to ExecuteNonQuery. Your original code calls it like this:

 

int created = db.ExecuteNonQuery(spCode);

 

I added extra lines of code to create a DbCommand since the overload you used is expecting the string parameter to be a name of the stored procedure. Here’s what I added:

 

DbCommand command = db.GetSqlStringCommand(spCode);

 

And then called:

 

int created = db.ExecuteNonQuery(command);

 

After doing this, everything worked fine, I didn’t encounter any error. However, in your original post, it seems like you were able to create the stored procedure and only got the error when executing the stored procedure you created programmatically. So my question now is, where are you really encountering the exception?

 

Sarah Urmeneta
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com
  

Aug 17, 2010 at 7:46 AM

manthangogari wrote:  "yes that is correct i am getting the error when i am trying to execute the stored procedure once it has been created successfully within MS Access database. Were you able to reproduce the error."

Yes.  And that is the expected behavior since you're trying to create a stored procedure which already exists.  I suggest executing first a sql statement if the stored procedure already exists, create if it doesn't.

 

Sarah Urmeneta
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com
  

Aug 17, 2010 at 7:53 AM

i am not creating the SP every time it gets executes. The creation of SP is one off thing. Once it has been created I was trying to execute it via Enterprise Library and that is when i got the Invalid Operation error. Am I missing something here.

Aug 17, 2010 at 8:01 AM

I see, I guess I misunderstood your reply.  Still, I wasn't able to repro your error.  I ran your test and it passed.  The only thing I modified in your project is to change the target framework to .net 3.5 since I don't have .net framework 4 installed on the machine I'm using right now.  I also use a different database since I don't have the Northwind database but I don't think that has anything to do with the error.

 

Sarah Urmeneta
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com
  

Aug 18, 2010 at 12:02 AM

That is really wierd. I changed the target framework to 3.5 and still get the same error. I think I will have to now attach the actual source code of Enterprise Library and see where the error is being thrown from and will update once I find something. 

Aug 18, 2010 at 5:59 AM
Edited Aug 18, 2010 at 6:12 AM

After trying a couple of test it seems that the error encountered only happens in the Northwind database. I don't know if there is anything special about Northwind but anyway one good thing is that it works on a custom created database.

Btw, this is regardless whether DAAB is used or not.

Gino Terrado
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com
 

Aug 19, 2010 at 2:41 AM

@Gino: You are correct. I migrated the Northwind database from SQL Server to MS Access and created the Stored procedures it works now. Not sure what was the issue with the Microsoft's Northwind 2007 Access database. I was using MS Access 2010 maybe that must have been the issue. But I am glad it is solved now.

Thank you Sarah and Gino for all your assistance you guys rock.