Parameterised query with an ms access table

Hi

When using sql server, I can use a sql like below in my data adapter to send
the sql a parameter value;

SELECT <field list>
FROM <table>
WHERE Fieldx = @Para1

My question is, can I do something similar with an access db ie can I send a
parameter to a data adaptor sql which is connected to an ms access table? If
not is there another way to do this?

Thanks

Regards

sloan
Fri Feb 01 16:06:15 CST 2008



SELECT KeyValueTable.Key, KeyValueTable.Value
FROM KeyValueTable
WHERE Key=pKey;



Create a query like the one above. I'm talking about in the program Access.


(I have a simple table, with 2 columns.... Key and Value, both strings of
50 chars)
(The table name is KeyValueTable)
(I put 3 rows in it... "1","One" ........"2","Two".........."3","Three")


Then your code in DotNet will look something like this:


xxxxxxx.AddInParameter ("pKey" , DbType.String , "2" );


Something like that. That is EnterpriseLibrary 3.1 code.. your code will
vary.



If you don't have access to the program Access...then wait for someone else
to chime in.

I'm not sure, since you didn't mention it.






"John" <John@nospam.infovis.co.uk> wrote in message
news:unLtEzRZIHA.1208@TK2MSFTNGP03.phx.gbl...
> Hi
>
> When using sql server, I can use a sql like below in my data adapter to
> send the sql a parameter value;
>
> SELECT <field list>
> FROM <table>
> WHERE Fieldx = @Para1
>
> My question is, can I do something similar with an access db ie can I send
> a parameter to a data adaptor sql which is connected to an ms access
> table? If not is there another way to do this?
>
> Thanks
>
> Regards
>

Scott
Fri Feb 01 19:03:28 CST 2008

Yes, you do the exact same thing, but using OleDb.OleDbParameter objects.

The only thing that is different (and very important) is that with Access,
the name of the parameter is meaningless, it's the order that they are added
to the parameters collection that matters - - i.e. the first parameter you
reference in your SQL statement will fetch the first parameter in the
parameters collection, regardless of what the @name is you type in the SQL
statement. As a result, just use ? as parameter identifiers.

-Scott

"John" <John@nospam.infovis.co.uk> wrote in message
news:unLtEzRZIHA.1208@TK2MSFTNGP03.phx.gbl...
> Hi
>
> When using sql server, I can use a sql like below in my data adapter to
> send the sql a parameter value;
>
> SELECT <field list>
> FROM <table>
> WHERE Fieldx = @Para1
>
> My question is, can I do something similar with an access db ie can I send
> a parameter to a data adaptor sql which is connected to an ms access
> table? If not is there another way to do this?
>
> Thanks
>
> Regards
>

Paul
Mon Feb 04 10:21:11 CST 2008

On Fri, 1 Feb 2008 21:52:44 -0000, "John" <John@nospam.infovis.co.uk> wrote:

¤ Hi
¤
¤ When using sql server, I can use a sql like below in my data adapter to send
¤ the sql a parameter value;
¤
¤ SELECT <field list>
¤ FROM <table>
¤ WHERE Fieldx = @Para1
¤
¤ My question is, can I do something similar with an access db ie can I send a
¤ parameter to a data adaptor sql which is connected to an ms access table? If
¤ not is there another way to do this?

Yes, you just need to create the Command object for the query and assign it to the corresponding
Command of the DataAdapter. Below is an example:

Dim AccessConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Test Files\db1 XP.mdb")

Dim AccessCommand As New OleDbCommand("SELECT * FROM Table1 WHERE [Date Field] BETWEEN ? AND ?",
AccessConnection)
AccessConnection.Open()

Dim StartDate As New System.DateTime(2005, 1, 25)
Dim EndDate As New System.DateTime(2006, 5, 5)

AccessCommand.Parameters.Add("@DateValStart", System.Data.OleDb.OleDbType.DBDate).Value = StartDate
AccessCommand.Parameters.Add("@DateValEnd", System.Data.OleDb.OleDbType.DBDate).Value = EndDate

Dim da As New System.Data.OleDb.OleDbDataAdapter
da.SelectCommand = AccessCommand
Dim ds As New DataSet

da.Fill(ds, "Table1")


Paul
~~~~
Microsoft MVP (Visual Basic)