<

Improve company productivity with a Business Account.Sign Up

x

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

Published on
16,735 Points
10,735 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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Join & Write a Comment

Watch the video to know how one can repair corrupt Exchange OST file effortlessly and convert OST emails to MS Outlook PST file format by using Kernel for OST to PST converter tool. It can convert OST to MSG, MBOX, EML to access them. It can migrate…
Watch the video to know the process of migration of Exchange or Office 365 mailboxes in absence of MS Outlook. It is an eminent tool which can easily migrate Public, Archive user mailboxes from one another Exchange server and Office 365. Kernel Migr…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month