Calling Jet 4.0 Parameterized Queries from ADO.NET

[Otis Mukinfus]

I thought there was a way to do this, but have not been able to figure
it out.

I want to pass the parameters to an Access 2002 Parameterized Query
using an OleDbCommand object.

How is this done?

Otis
Otis Mukinfus
http://www.otismukinfus.com

 

[Miha Markic]

Hi Otis,

You should set CommandText = "query name", CommandType =
CommandType.StoredProcedure and set command's parameters.

 

[Carl Prothman [MVP]]

I want to pass the parameters to an Access 2002 Parameterized Query
using an OleDbCommand object.
How is this done?

' Command to Insert Records.
Dim cmdInsert As New OleDbCommand()
cmdInsert.CommandText = "INSERT INTO AutoIncrementTest (Description) VALUES (?)"
cmdInsert.Connection = cnJetDB
cmdInsert.Parameters.Add(New OleDbParameter("Description", OleDbType.VarChar, 40, "Description"))

For more info, see:
http://support.microsoft.com/default.aspx?scid=kb;en-us;815629&Product=adonet

--

Thanks,
Carl Prothman
Microsoft ASP.NET MVP

Hire top-notch developers at
http://www.able-consulting.com

 

[Scott M.]

No need to set the CommandType to StoredProcedure.

 

[Otis Mukinfus]

Thank you Miha. Your answer was the closest to what I ended up doing.

What I didn't understand was that I could drag the stored procedure
(parameterized query) onto my component design surface from the Server
Explorer and then set the parameters up for the query manually. Silly
me! I was expecting the parameters to be set up automatically,like
they are with a SQL Server stored procedure (

After figuring out that I had to (could) set them up manually I was
home free.

Here is the code sample that uses the stored procedure (parameterized
query):

public DataSet GetCountryData(string callsign)
{
OleDbDataAdapter da = null;
DataSet ds = null;

try
{
da = new OleDbDataAdapter(cmdGetCountryData);
da.SelectCommand.Parameters["Callsign"].Value = callsign;

ds = new DataSet();
da.Fill(ds, "CountryData");
}
catch(OleDbException ex)
{
throw ex;
}
catch(Exception ex)
{
throw ex;
}
return ds;
}

Happy Holidays, my friend.

Hi Otis,

You should set CommandText = "query name", CommandType =
CommandType.StoredProcedure and set command's parameters.

Otis Mukinfus
http://www.otismukinfus.com

 

[Otis Mukinfus]

Carl,

Thanks, that is absolutely the correct way to manually setup a call to
an Access parameterized query by hand. I was looking for the
automatic way by dragging the sp from the Server Explorer in VS 2003.
See my answer to Miha for details...

' Command to Insert Records.
Dim cmdInsert As New OleDbCommand()
cmdInsert.CommandText = "INSERT INTO AutoIncrementTest (Description) VALUES (?)"
cmdInsert.Connection = cnJetDB
cmdInsert.Parameters.Add(New OleDbParameter("Description", OleDbType.VarChar, 40, "Description"))

For more info, see:
http://support.microsoft.com/default.aspx?scid=kb;en-us;815629&Product=adonet

Otis Mukinfus
http://www.otismukinfus.com

 

[Carl Prothman [MVP]]

Otis,
In Access, create a new parameterized query.
e.g.
PARAMETERS CustomerID Short;
SELECT Customer.CustomerID, Customer.LastName, Customer.FirstName
FROM Customer
WHERE (((Customer.CustomerID)=[CustomerID]))
ORDER BY Customer.LastName;

Then in Visual Studio .NET, open up the Server Explorer pane and create a new
connection to the MDB (when the Data Link Properties dialog pops up, click the
Provider tab, then select "Microsoft Jet 4.0 OLE DB Provider", etc..)
http://msdn.microsoft.com/library/en-us/vsintro7/html/vbtskAccessingServerExplorer.asp

Then create a new (or open an existing) WinForm in design view

Then navigate to the Stored Procedure in the Server Explorer and drag and drop the
the Stored Procedure onto the form. Visual Studio .NET will then create a new
OledbConnection and OledbCommand objects for you on the form. The code
for these objects will be found in the form's hidden Region "Windows Form
Designer generated code ".

Note you can also drag and drop a Table, which will create a OledbConnection
and a OledbDataAdapter. Visual Studio .NET will create the CommandInsert,
CommandUpdate, and CommandDelete statements from the CommandSelect
statement within the OledbDataAdapter for you automatically.

Enjoy!

--

Thanks,
Carl Prothman
Microsoft ASP.NET MVP

Hire top-notch developers at
http://www.able-consulting.com

 

[Otis Mukinfus]

Otis,
In Access, create a new parameterized query.
e.g.
PARAMETERS CustomerID Short;
SELECT Customer.CustomerID, Customer.LastName, Customer.FirstName
FROM Customer
WHERE (((Customer.CustomerID)=[CustomerID]))
ORDER BY Customer.LastName;

Then in Visual Studio .NET, open up the Server Explorer pane and create a new
connection to the MDB (when the Data Link Properties dialog pops up, click the
Provider tab, then select "Microsoft Jet 4.0 OLE DB Provider", etc..)
http://msdn.microsoft.com/library/en-us/vsintro7/html/vbtskAccessingServerExplorer.asp

Then create a new (or open an existing) WinForm in design view

Then navigate to the Stored Procedure in the Server Explorer and drag and drop the
the Stored Procedure onto the form. Visual Studio .NET will then create a new
OledbConnection and OledbCommand objects for you on the form. The code
for these objects will be found in the form's hidden Region "Windows Form
Designer generated code ".

Note you can also drag and drop a Table, which will create a OledbConnection
and a OledbDataAdapter. Visual Studio .NET will create the CommandInsert,
CommandUpdate, and CommandDelete statements from the CommandSelect
statement within the OledbDataAdapter for you automatically.

Enjoy!

Yes, but using the drag and drop method on the stored procedure
(parameterized query) will not add the parameters automatically as
when dragging MS SQL Server stored procedures. You must do that
yourself if your sp is in Access. Or have I missed something there?


Otis Mukinfus
http://www.otismukinfus.com

 

[Carl Prothman [MVP]]

On Fri, 26 Dec 2003 09:17:16 -0800, "Carl Prothman [MVP]"
<snip>

Yes, but using the drag and drop method on the stored procedure
(parameterized query) will not add the parameters automatically as
when dragging MS SQL Server stored procedures. You must do that
yourself if your sp is in Access. Or have I missed something there?

Otis,
No, you haven't missed anything. The parameters are NOT created for
you like they are with SQL Server's Stored Procedure drag and drop.

'OleDbCommand1
Me.OleDbCommand1.CommandText = "[Customer_Get_ByCustomerID]"
Me.OleDbCommand1.CommandType = System.Data.CommandType.StoredProcedure
Me.OleDbCommand1.Connection = Me.OleDbConnection1

Hence you must create them manually. Strange! Good Catch!

--

Thanks,
Carl Prothman
Microsoft ASP.NET MVP

Hire top-notch developers at
http://www.able-consulting.com