Passing Params to Access Query with VB.Net 1

[msmith425]

I have a query in Access that I would like to pass parameters to from VB.Net.

I don't know how to...

1. Designate the variable in Access
2. Designate the variable in VB.Net
3. Pass the variable to the Access query via VB.Net/ADO.Net

Does anyone have links to something that would help me with this?

Thanks in advance.

 

[jfrost10]

In access, in the query view, right click and select Parameters. Then add the parameters there.

In your code, you set up the code as if you were going to access a stored proc from sql server, but use the oledb data access classes instead.

You add the parameters in the same way

Command.Paramaters.Add("ParamName", paramvalue)
(its overloaded, so there's different ways to "add" the parameter)

Just make sure you add the parameters in the exact order that they're listed in the stored query (Access is picky that way)

hth

D'Arcy

 

[msmith425]

Thanks for the info. I'm not quite getting the Access query part. I've added the parameter but when I run the query it still gives me all of the suppliers (see code below). What am I missing?


PARAMETERS Supplier Text ( 255 );
SELECT tSuppliers.SupplierID, tSuppliers.SupplierName
FROM tSuppliers;


Thanks.

 

[msmith425]

Never mind. I figured it out. Had to get away from it for a few minutes to see past all those trees!

PARAMETERS Supplier Text ( 255 );
SELECT tSuppliers.SupplierID, tSuppliers.SupplierName
FROM tSuppliers
WHERE (((tSuppliers.SupplierName)=[Supplier]));


Thanks.

 

[msmith425]

For those that want to know the VB.Net portion (or for when I forget and have to come back here)...

Dim DS As DataSet
Dim oCmd As New OleDb.OleDbCommand("SuppliersTest", oConn1)
oCmd.CommandType = CommandType.StoredProcedure

Dim myParm As OleDb.OleDbParameter = oCmd.Parameters.Add("@Suppliers", OleDb.OleDbType.VarChar, 15)

myParm.Value = "%"

Me.oConn1.Open()
Me.DataGrid1.DataSource = oCmd.ExecuteReader
Me.DataGrid1.DataBind()
Me.oConn1.Close()