<

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

Published on
17,462 Points
11,462 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
Enjoy this complimentary article view.

Get unlimited access to our entire library of technical procedures, guides, and tutorials written by certified industry professionals.

Get 7 days free
Click here to view the full article

Using this article for work? Experts Exchange can benefit your whole team.

Learn More
COLLABORATE WITH CERTIFIED PROFESSIONALS
Experts Exchange is a tech solutions provider where users receive personalized tech help from vetted certified professionals. These industry professionals also write and publish relevant articles on our site.
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.
Learn from the best.