Calling Jet 4.0 Parameterized Queries from ADO.NET

Calling Jet 4.0 Parameterized Queries from ADO.NET

Post by Otis Mukin » Sat, 27 Dec 2003 05:46:03


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.yqcomputer.com/
 
 
 

Calling Jet 4.0 Parameterized Queries from ADO.NET

Post by Miha Marki » Sat, 27 Dec 2003 05:54:30

Hi Otis,

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

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com
www.rhand.com

 
 
 

Calling Jet 4.0 Parameterized Queries from ADO.NET

Post by Carl Proth » Sat, 27 Dec 2003 06:38:19


' 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://www.yqcomputer.com/ ;en-us;815629&Product=adonet

--

Thanks,
Carl Prothman
Microsoft ASP.NET MVP

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

Calling Jet 4.0 Parameterized Queries from ADO.NET

Post by Scott M » Sat, 27 Dec 2003 10:00:31

No need to set the CommandType to StoredProcedure.
 
 
 

Calling Jet 4.0 Parameterized Queries from ADO.NET

Post by Otis Mukin » Sat, 27 Dec 2003 23:15:03

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 :o(

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.


On Thu, 25 Dec 2003 21:54:30 +0100, "Miha Markic" <miha at rthand com>



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

Calling Jet 4.0 Parameterized Queries from ADO.NET

Post by Otis Mukin » Sat, 27 Dec 2003 23:19:30

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...


On Thu, 25 Dec 2003 13:38:19 -0800, "Carl Prothman [MVP]"




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

Calling Jet 4.0 Parameterized Queries from ADO.NET

Post by Carl Proth » Sun, 28 Dec 2003 02:17:16

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://www.yqcomputer.com/

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.yqcomputer.com/
 
 
 

Calling Jet 4.0 Parameterized Queries from ADO.NET

Post by Otis Mukin » Sun, 28 Dec 2003 10:17:08

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



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.yqcomputer.com/
 
 
 

Calling Jet 4.0 Parameterized Queries from ADO.NET

Post by Carl Proth » Sun, 28 Dec 2003 10:33:14


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.yqcomputer.com/