<

VB .NET :: Using OleDB Connection to Pass Parameters Into a mdb Query

Published on
17,742 Points
11,742 Views
Last Modified:
Approved
If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place!

Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and looks like this:

qryCust:
SELECT DISTINCT Customers.CustomerID FROM SalesItem
WHERE (((SalesItems.Name)="Redeemed" Or (SalesItems.Name)="Issued") AND ((Transact.DateStamp)>DateAdd('h',28,[?])));

This query takes a Date parameter that needs to be supplied when the query is run. So if you're writing a program in VB .NET that calls this query you need to somehow provide the parameter also. So to do this you need an OleDBCommand object and then you can add parameters onto your query.

The solution to writing a query that uses this pre-existing query will need to look like the below code:
Private Function queryDB(ByVal sql As String, ByVal tbl_qry As String) As DataSet
        Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & databasePath
        Dim oleConn As New OleDb.OleDbConnection(connStr)
        ds = New DataSet
        Dim dsAdapter As New OleDb.OleDbDataAdapter(sql, oleConn)
        'dsAdapter.Fill(ds, tbl_qry)

        Dim cmd As System.Data.OleDb.OleDbCommand = New System.Data.OleDb.OleDbCommand(sql, oleConn)
        dsAdapter.SelectCommand = cmd

        ' Add parameters and set values.
        Dim inactiveDate As String = Format(dtp.Value, "M/d/yyyy")
        cmd.Parameters.Add("@Date", System.Data.OleDb.OleDbType.VarChar, 15).Value = inactiveDate
        cmd.Parameters(0).Direction = ParameterDirection.Input
        dsAdapter.Fill(ds, tbl_qry)
        Return ds
    End Function

Open in new window


This supplies the query in Access with the required Date parameter while allowing also another query to be written on top!
0
Author:Bruce Smith
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Get 7 days free