[SOLVED] Calling MS Access Stored Queries with Parameters using DAAB v5.0

Calling MS Access Stored Queries with Parameters using DAAB v5.0

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.

Solution

I was able to resolve the issue. The issue was with the Northwind sample database. I then imported Northwind database from SQL Server into MS Access and also created the stored queries in MS Access. Here is the detailed discussion that I had with the Enterprise Library DAAB's team: entlib.codeplex.com/Thread/View.aspx?ThreadId=223653 Hope it helps. Access does not care about the @ character you can call the parameter with or without the @ character.