We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

MS Access Query

Gunit2507
Gunit2507 asked
on
Medium Priority
310 Views
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?

Comment
Watch Question

CERTIFIED EXPERT
Commented:

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
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)
        lst.Items.Clear()

        ' Open the connection.
        connUsers.Open()

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

        ' Execute the query.
        Dim db_reader As OleDbDataReader = _
            cmd.ExecuteReader(CommandBehavior.Default)

        ' 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
            lst.Items.Add(txt)
        Loop

        ' Close the connection.
        connUsers.Close()
    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.
        connUsers.Open()

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

        ' 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 = _
            cmd.ExecuteReader(CommandBehavior.SingleRow)

        ' Display the results.
        If db_reader.HasRows Then
            db_reader.Read()
            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
        Else
            For Each ctl As Control In Me.Controls
                If TypeOf ctl Is TextBox Then ctl.Text = ""
            Next ctl
        End If

        ' Close the connection.
        connUsers.Close()
    End Sub

End Class
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.