<

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

Published on
16,492 Points
10,492 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
Comment
Author:Bruce Smith
0 Comments

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Join & Write a Comment

Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Is your organization moving toward a cloud and mobile-first environment? In this transition, your IT department will encounter many challenges, such as navigating how to: Deploy new applications and services to a growing team Accommodate employee…
Suggested Courses

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month