Creating a Filtered List of Customers on a Mobile Device

Imports System.Data.SqlServerCe
Imports Resco.Controls.AdvancedList

Public Class Form1
	Dim m_sPath As String
	Dim m_sConnectionString As String
	Dim m_Connection As SqlCeConnection
    Dim m_Command As SqlCeCommand

    Private Sub DatabaseInit()
        If (m_Command IsNot Nothing) Then
            m_Command.Dispose()
        End If

        If (m_Connection IsNot Nothing) Then
            m_Connection.Dispose()
            m_Connection.Close()
        End If

        m_Connection = New SqlCeConnection(m_sConnectionString)
        m_Command = New SqlCeCommand("SELECT ContactName, Address, City, Country FROM customers", m_Connection)
    End Sub

    Public Sub New()

        ' This call is required by the Windows Form Designer.
        InitializeComponent()

        ' find out path to this application on the device
        m_sPath = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase)
        ' create a connection string
        m_sConnectionString = "Data source = " + m_sPath + "\Northwind.sdf"

        DatabaseInit()

    End Sub

    ' Gets fired whenever the text in TextBox1 changes.
    Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged
        ' remove all rows from AdvancedList
        Me.advancedList1.DataRows.Clear()
        ' Create a new SQL CE Connection
        m_Command = New SqlCeCommand("SELECT ContactName, Address, City, Country FROM customers WHERE ContactName LIKE '%" + Me.TextBox1.Text + "%'", m_Connection)
        ' Reload the data.
        Me.advancedList1.DbConnector.Command = m_Command
        Me.advancedList1.LoadData()
        ' Highlight the input text in AdvancedList's TextCells.
        CType(Me.advancedList1.Templates(0)(0), TextCell).SelectedText = Me.TextBox1.Text
        CType(Me.advancedList1.Templates(1)(0), TextCell).SelectedText = Me.TextBox1.Text

    End Sub


    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ' set focus to TextBox1 on Form1 load.
        Me.TextBox1.Focus()
        Try
            Me.advancedList1.DbConnector.Command = m_Command
            Me.advancedList1.LoadData()
        Catch ex As SqlCeException
            ' If there is an exception, display the error message and exit
            MessageBox.Show(ex.Message)
            Me.Close()
        End Try
    End Sub



    Private Sub Form1_Closed(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Closed
        ' If the form is closed, close the SqlCe connection.
        m_Connection.Close()
    End Sub



    Private Sub TextBox1_KeyDown(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles TextBox1.KeyDown
        Select Case e.KeyCode
            Case Keys.Down
                If (Me.advancedList1.DataRows.Count - 1 > Me.advancedList1.ActiveRowIndex) Then
                    Me.advancedList1.ActiveRowIndex += 1
                End If
                e.Handled = True
            Case Keys.Up
                If (Me.advancedList1.ActiveRowIndex > 0) Then
                    Me.advancedList1.ActiveRowIndex -= 1
                End If
                e.Handled = True
        End Select
    End Sub

    Private Sub mnuSimple_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuSimple.Click
        Me.advancedList1.LoadXml(m_sPath + "\SimpleRows.xml")
        CType(Me.advancedList1.Templates(0)(0), TextCell).SelectedText = Me.TextBox1.Text
        CType(Me.advancedList1.Templates(1)(0), TextCell).SelectedText = Me.TextBox1.Text
        DatabaseInit()
    End Sub

    Private Sub mnuDetailed_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuDetailed.Click
        Me.advancedList1.LoadXml(m_sPath + "\DetailedRows.xml")
        CType(Me.advancedList1.Templates(0)(0), TextCell).SelectedText = Me.TextBox1.Text
        CType(Me.advancedList1.Templates(1)(0), TextCell).SelectedText = Me.TextBox1.Text
        DatabaseInit()
    End Sub
End Class