MS Access Query

Posted on 2006-04-05
Last Modified: 2010-04-24
If I have a query within my database with a parameter who do I call that query using a data command?

Question by:Gunit2507
    LVL 48

    Accepted Solution

    LVL 28

    Assisted Solution

    Compose the query, replacing the values you want to be parameters with
    question marks. Use the query to build a command object.

    Use the command's Parameters.Add method to add parameter objects to the
    command. The database will replace the question marks with the
    parameters in the order in which they are added. In other words, the
    first question mark is replaced with the first parameter, and so forth.

    Execute the command and process the results as usual.

    Imports System.Data.OleDb

    Public Class Form1
        Inherits System.Windows.Forms.Form

    #Region " Windows Form Designer generated code "
    #End Region

        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            ' Get the database file name.
            Dim db_name As String = Application.ExecutablePath
            db_name = db_name.Substring(0, db_name.LastIndexOf("\") + 1)
            If db_name.EndsWith("\bin\") Then
                db_name = db_name.Substring(0, db_name.Length - 4)
            End If
            db_name &= "Users.mdb"

            ' Build the database connect string.
            Me.connUsers.ConnectionString = _
                "Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database Locking Mode=1;" & _
                "Data Source=""" & db_name & """;Mode=Share Deny None;Jet OLEDB:Engine Type=5;" & _
                "Provider=""Microsoft.Jet.OLEDB.4.0"";Jet OLEDB:System database=;Jet OLEDB:SFP=False;" & _
                "persist security info=False;Extended Properties=;Jet OLEDB:Compact Without Replica Repair=False;" & _
                "Jet OLEDB:Encrypt Database=False;Jet OLEDB:Create System Database=False;" & _
                "Jet OLEDB:Don't Copy Locale on Compact=False;User ID=Admin;Jet OLEDB:Global Bulk Transactions=1"

            ' Load the list of names.
            LoadListBoxFromQuery(lstNames, _
                "SELECT FirstName, LastName FROM People ORDER BY FirstName, LastName")
        End Sub

        Private Sub LoadListBoxFromQuery(ByVal lst As ListBox, ByVal query As String)

            ' Open the connection.

            ' Make a SELECT Command.
            Dim cmd As New OleDb.OleDbCommand( _
                query, connUsers)

            ' Execute the query.
            Dim db_reader As OleDbDataReader = _

            ' Display the results.
            Dim txt As String
            Dim i As Integer
            Do While db_reader.Read
                txt = db_reader.Item(0).ToString
                For i = 1 To db_reader.FieldCount - 1
                    txt &= vbTab & db_reader.Item(i).ToString
                Next i

            ' Close the connection.
        End Sub

        Private Sub lstNames_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lstNames.SelectedIndexChanged
            ' Get the selected name.
            Dim names() As String = lstNames.Text.Split(CChar(vbTab))

            ' Display the person's data.
            DisplayPersonData(names(0), names(1))
        End Sub

        Private Sub DisplayPersonData(ByVal first_name As String, ByVal last_name As String)
            ' Open the connection.

            ' Make a Command for this connection
            ' and this transaction.
            Dim cmd As New OleDb.OleDbCommand( _
                "SELECT * FROM People WHERE FirstName=? AND LastName=?", _

            ' Create parameters for the query.
            cmd.Parameters.Add(New OleDb.OleDbParameter("FirstName", first_name))
            cmd.Parameters.Add(New OleDb.OleDbParameter("LastName", last_name))

            ' Execute the query.
            Dim db_reader As OleDbDataReader = _

            ' Display the results.
            If db_reader.HasRows Then
                txtFirstName.Text = db_reader.Item("FirstName").ToString
                txtLastName.Text = db_reader.Item("LastName").ToString
                txtStreet.Text = db_reader.Item("Street").ToString
                txtCity.Text = db_reader.Item("City").ToString
                txtState.Text = db_reader.Item("State").ToString
                txtZip.Text = db_reader.Item("Zip").ToString
                For Each ctl As Control In Me.Controls
                    If TypeOf ctl Is TextBox Then ctl.Text = ""
                Next ctl
            End If

            ' Close the connection.
        End Sub

    End Class

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    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 l…
    Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
    This video discusses moving either the default database or any database to a new volume.
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now