Parameterised query with an ms access table

  • Thread starter Thread starter John
  • Start date Start date
J

John

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
 
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.
 
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
 
¤ 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)
 
Back
Top