none
Dynamic text box search and alert display RRS feed

  • Question

  • Hi,

    Please help us with the below challenge.

    We have a MS access database which contains a large list of names.

    When user type in rich text box / input box of the from and if the name matches any of the entries in DB it should give an alert or message. the result should be instant and do not freeze or interrupt the typing. 

    PS: The name can be anywhere in the text box.

    We use VB 2015 and MSAccess database.


    Coderv9

    Tuesday, January 22, 2019 1:12 PM

Answers

  • Here we have a check by full name and user name, user name and full name.

    Imports System.Data.OleDb
    Imports System.IO
    Imports KarensBaseClasses
    
    Public Class DataOperations
        Inherits AccessConnection
    
        ''' <summary>
        ''' Default our connection to a database in the executable folder when not using a password
        ''' </summary>
        ''' <remarks>
        ''' Not used in the code sample but this is how to do a connection not encrypted.
        ''' </remarks>
        Public Sub New()
            DefaultCatalog = Path.Combine(
                AppDomain.CurrentDomain.BaseDirectory, "Database1.accdb")
        End Sub
        Public Function IsBlackListed(userName As String, fullName As String) As Boolean
    
            Using cn As New OleDbConnection(ConnectionString)
    
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText =
                        <SQL>
                            SELECT ID
                            FROM 
                                tbl_BlackList
                            WHERE 
                                User_Name=@UserName OR Full_Name=@FullName;
                        </SQL>.Value
                    cn.Open()
    
                    cmd.Parameters.AddWithValue("@UserName", userName)
                    cmd.Parameters.AddWithValue("@FullName", fullName)
    
                    Dim reader = cmd.ExecuteReader()
    
                    Return reader.HasRows
    
                End Using
            End Using
    
        End Function
        Public Function IsBlackListedByUserName(userName As String) As String
    
            Dim result As String = ""
    
            Using cn As New OleDbConnection(ConnectionString)
    
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText =
                        <SQL>
                            SELECT Full_Name
                            FROM 
                                tbl_BlackList
                            WHERE 
                                User_Name=@UserName
                        </SQL>.Value
                    cn.Open()
    
                    cmd.Parameters.AddWithValue("@UserName", userName)
    
                    Dim reader = cmd.ExecuteReader()
    
                    If reader.HasRows Then
                        reader.Read()
                        result = reader.GetString(0)
                    End If
    
                End Using
            End Using
    
            Return result
        End Function
        Public Function IsBlackListedByFullName(fullName As String) As String
    
            Dim result As String = ""
    
            Using cn As New OleDbConnection(ConnectionString)
    
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText =
                        <SQL>
                            SELECT  User_Name
                            FROM 
                                tbl_BlackList
                            WHERE 
                                Full_Name=@FullName
                        </SQL>.Value
                    cn.Open()
    
                    cmd.Parameters.AddWithValue("@FullName", fullName)
    
                    Dim reader = cmd.ExecuteReader()
    
                    If reader.HasRows Then
                        reader.Read()
                        result = reader.GetString(0)
                    End If
    
                End Using
            End Using
    
            Return result
        End Function
    End Class
    

    Form code for full name to get user name

    ''' <summary>
    ''' In this example Database1.accdb resides in the bin\debug folder
    ''' </summary>
    Public Class Form1
        ''' <summary>
        ''' Press button check (see leave check below)
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        Private Sub isBlackListedExecuteButton_Click(sender As Object, e As EventArgs) _
            Handles isBlackListedExecuteButton.Click
    
            IsBlackListCheck()
    
        End Sub
        ''' <summary>
        ''' On leave one of these TextBox controls check for black list
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        Private Sub fullNameTextBox_Leave(sender As Object, e As EventArgs) _
            Handles fullNameTextBox.Leave, userNameTextBox.Leave
    
            IsBlackListCheck()
    
        End Sub
        Private Sub IsBlackListCheck()
            Dim ops = New DataOperations
            Dim result = ops.IsBlackListedByFullName(fullNameTextBox.Text)
            If Not String.IsNullOrWhiteSpace(result) Then
                MessageBox.Show($"{result} is black listed")
            End If
        End Sub
    End Class
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Coderv9 Wednesday, January 23, 2019 1:48 PM
    Tuesday, January 22, 2019 10:57 PM
    Moderator

All replies

  • Hello,

    How many records are being search?

    Is the field to search on indexed and if so are duplicated allowed?

    Would an option be to load names into a list (could be a list of name or perhaps a list containing the primary key and the name) and search the list rather than the database table.

    What is the end goal by showing the alert e.g. duplicates not allowed etc.

    Here is a pattern that may help yet unsure as your requirements are vague.

    Load names, in this case company names into a list(of string)

    Public Function LoadCompanyNames() As List(Of String)
        Dim nameList As New List(Of String)
        Using cn As New OleDbConnection(ConnectionString)
            Using cmd As New OleDbCommand With {.Connection = cn}
                cmd.CommandText = "SELECT  CompanyName FROM Customers"
                cn.Open()
                Dim reader = cmd.ExecuteReader()
                While reader.Read()
                    nameList.Add(reader.GetString(0))
                End While
            End Using
        End Using
        Return nameList
    End Function

    In the form load the names in form load then in a TextBox TextChanged event check if what is typed in exist case insensitive. Uses a label to alert if found or not. I used InfexOf with OrdinalIgnoreCase as the Contains method is not case insensitive. 

    Public Class FilterForm
        Private nameList As List(Of String)
    
        Private Sub FilterForm_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Dim ops As New DatabaseOperations
            nameList = ops.LoadCompanyNames()
        End Sub
        Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged
            Dim result = nameList.FirstOrDefault(
                Function(item) item.IndexOf(TextBox1.Text, StringComparison.OrdinalIgnoreCase) >= 0)
    
            If result IsNot Nothing Then
                Label1.Text = "Found"
            Else
                Label1.Text = ""
            End If
        End Sub
    End Class
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Tuesday, January 22, 2019 1:41 PM
    Moderator
  • When user type in rich text box / input box of the from and if the name matches any of the entries in DB it should give an alert or message. the result should be instant and do not freeze or interrupt the typing. 

    Load the names into an array/list/... at the startup
    Tuesday, January 22, 2019 2:05 PM
  • Hi Karen, 

    Thank you for the code.

    Below is our DB this contains certain list of User names and their full name.

    Below is the main form

    When user name is entered in the name field or the the full name in remarks field (rich text), the text should be searched with the DB and if there is an exact match an alert is triggered like "Black Listed user"


    Coderv9

    Tuesday, January 22, 2019 4:28 PM
  • Create the following class, change the name of the database (here no path is given as it's in the bin\debug folder).

    Project structure

    Table

    I show in code two ideas, one on leave event of either text box, one for a button click, sorry I don't have time for a rich text control.

    Data class

    Imports System.Data.OleDb
    
    Public Class DataOperations
    
        Private ConnectionString As String =
                    "Provider=Microsoft.ACE.OLEDB.12.0;" &
                    "Data Source=Database1.accdb"
    
        Public Function IsBlackListed(userName As String, fullName As String) As Boolean
    
            Using cn As New OleDbConnection(ConnectionString)
    
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText = <SQL>
                                        SELECT ID
                                        FROM tbl_BlackList
                                        WHERE User_Name=@UserName OR Full_Name=@FullName;
                                      </SQL>.Value
                    cn.Open()
    
                    cmd.Parameters.AddWithValue("?", userName)
                    cmd.Parameters.AddWithValue("?", fullName)
    
                    Dim reader = cmd.ExecuteReader()
    
                    Return reader.HasRows
    
                End Using
            End Using
    
        End Function
    
    End Class
    
    Form code
    ''' <summary>
    ''' In this example Database1.accdb resides in the bin\debug folder
    ''' </summary>
    Public Class Form1
        ''' <summary>
        ''' Press button check (see leave check below)
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        Private Sub isBlackListedExecuteButton_Click(sender As Object, e As EventArgs) _
            Handles isBlackListedExecuteButton.Click
    
            IsBlackListCheck()
    
        End Sub
        ''' <summary>
        ''' On leave one of these TextBox controls check for black list
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        Private Sub fullNameTextBox_Leave(sender As Object, e As EventArgs) _
            Handles fullNameTextBox.Leave, userNameTextBox.Leave
    
            IsBlackListCheck()
    
        End Sub
        Private Sub IsBlackListCheck()
            Dim ops = New DataOperations
            If ops.IsBlackListed(userNameTextBox.Text, fullNameTextBox.Text) Then
                MessageBox.Show("Black listed")
            End If
        End Sub
    End Class
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, January 22, 2019 6:09 PM
    Moderator
  • Thank You Karen. As always code works perfect when we enter the name. How is it possible to scan through all the words typed in the text box. Eg: If i type Mark Dan Miller this should trigger the popup. That's the biggest challenge :(

    Coderv9

    Tuesday, January 22, 2019 10:14 PM
  • Here we have a check by full name and user name, user name and full name.

    Imports System.Data.OleDb
    Imports System.IO
    Imports KarensBaseClasses
    
    Public Class DataOperations
        Inherits AccessConnection
    
        ''' <summary>
        ''' Default our connection to a database in the executable folder when not using a password
        ''' </summary>
        ''' <remarks>
        ''' Not used in the code sample but this is how to do a connection not encrypted.
        ''' </remarks>
        Public Sub New()
            DefaultCatalog = Path.Combine(
                AppDomain.CurrentDomain.BaseDirectory, "Database1.accdb")
        End Sub
        Public Function IsBlackListed(userName As String, fullName As String) As Boolean
    
            Using cn As New OleDbConnection(ConnectionString)
    
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText =
                        <SQL>
                            SELECT ID
                            FROM 
                                tbl_BlackList
                            WHERE 
                                User_Name=@UserName OR Full_Name=@FullName;
                        </SQL>.Value
                    cn.Open()
    
                    cmd.Parameters.AddWithValue("@UserName", userName)
                    cmd.Parameters.AddWithValue("@FullName", fullName)
    
                    Dim reader = cmd.ExecuteReader()
    
                    Return reader.HasRows
    
                End Using
            End Using
    
        End Function
        Public Function IsBlackListedByUserName(userName As String) As String
    
            Dim result As String = ""
    
            Using cn As New OleDbConnection(ConnectionString)
    
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText =
                        <SQL>
                            SELECT Full_Name
                            FROM 
                                tbl_BlackList
                            WHERE 
                                User_Name=@UserName
                        </SQL>.Value
                    cn.Open()
    
                    cmd.Parameters.AddWithValue("@UserName", userName)
    
                    Dim reader = cmd.ExecuteReader()
    
                    If reader.HasRows Then
                        reader.Read()
                        result = reader.GetString(0)
                    End If
    
                End Using
            End Using
    
            Return result
        End Function
        Public Function IsBlackListedByFullName(fullName As String) As String
    
            Dim result As String = ""
    
            Using cn As New OleDbConnection(ConnectionString)
    
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText =
                        <SQL>
                            SELECT  User_Name
                            FROM 
                                tbl_BlackList
                            WHERE 
                                Full_Name=@FullName
                        </SQL>.Value
                    cn.Open()
    
                    cmd.Parameters.AddWithValue("@FullName", fullName)
    
                    Dim reader = cmd.ExecuteReader()
    
                    If reader.HasRows Then
                        reader.Read()
                        result = reader.GetString(0)
                    End If
    
                End Using
            End Using
    
            Return result
        End Function
    End Class
    

    Form code for full name to get user name

    ''' <summary>
    ''' In this example Database1.accdb resides in the bin\debug folder
    ''' </summary>
    Public Class Form1
        ''' <summary>
        ''' Press button check (see leave check below)
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        Private Sub isBlackListedExecuteButton_Click(sender As Object, e As EventArgs) _
            Handles isBlackListedExecuteButton.Click
    
            IsBlackListCheck()
    
        End Sub
        ''' <summary>
        ''' On leave one of these TextBox controls check for black list
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        Private Sub fullNameTextBox_Leave(sender As Object, e As EventArgs) _
            Handles fullNameTextBox.Leave, userNameTextBox.Leave
    
            IsBlackListCheck()
    
        End Sub
        Private Sub IsBlackListCheck()
            Dim ops = New DataOperations
            Dim result = ops.IsBlackListedByFullName(fullNameTextBox.Text)
            If Not String.IsNullOrWhiteSpace(result) Then
                MessageBox.Show($"{result} is black listed")
            End If
        End Sub
    End Class
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Coderv9 Wednesday, January 23, 2019 1:48 PM
    Tuesday, January 22, 2019 10:57 PM
    Moderator
  • Hello,

    Just checking in to see how this task is going?


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, January 23, 2019 12:36 AM
    Moderator
  • Here's an example using a list of names that populates a ListBox as you type in the textbox

    'TextBox named TxtFilter
    'ListBox named ListBoxNames
    Option Strict On
    Public Class Form1
        Private RNG As New Random
        Private Names As New List(Of String)
        Private Sub TxtFilter_TextChanged(sender As Object, e As EventArgs) Handles TxtFilter.TextChanged
            Dim CharsNeeded As Integer = 2
            Dim SrchTXT As String = ""
            Dim WC As Boolean = False
            Dim PromptSize As New PointF
            CharsNeeded = 1
            SrchTXT = TxtFilter.Text
            If TxtFilter.Text.Length >= CharsNeeded Then
                ListBoxNames.Items.Clear()
                For Index = 0 To Names.Count - 1
                    If Names(Index).ToUpper.StartsWith(SrchTXT.ToUpper) Then
                        ListBoxNames.Items.Add(Names(Index))
                    End If
                Next
            Else
                ListBoxNames.Items.Clear()
            End If
        End Sub
    
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Dim FirstNames As New List(Of String) From
    {"Abigail", "Adam", "Adrian", "Alan", "Alexander", "Alexandra", "Alison", "Amanda", "Amelia", "Amy",
    "Andrea", "Andrew", "Angela", "Anna", "Anne", "Anthony", "Audrey", "Austin", "Ava", "Bella",
    "Benjamin", "Bernadette", "Blake", "Boris", "Brandon", "Brian", "Cameron", "Carl", "Carol",
    "Caroline", "Carolyn", "Charles", "Chloe", "Christian", "Christopher", "Claire", "Colin",
    "Connor", "Dan", "David", "Deirdre", "Diane", "Dominic", "Donna", "Dorothy", "Dylan", "Edward",
    "Elizabeth", "Ella", "Emily", "Emma", "Eric", "Evan", "Faith", "Felicity", "Fiona", "Frank",
    "Gabrielle", "Gavin", "Gordon", "Grace", "Hannah", "Harry", "Heather", "Ian", "Irene", "Isaac",
    "Jack", "Jacob", "Jake", "James", "Jan", "Jane", "Jasmine", "Jason", "Jennifer", "Jessica",
    "Joan", "Joanne", "Joe", "John", "Jonathan", "Joseph", "Joshua", "Julia", "Julian", "Justin",
    "Karen", "Katherine", "Keith", "Kevin", "Kimberly", "Kylie", "Lauren", "Leah", "Leonard", "Liam",
    "Lillian", "Lily", "Lisa", "Lucas", "Luke", "Madeleine", "Maria", "Mary", "Matt", "Max", "Megan",
    "Melanie", "Michael", "Michelle", "Molly", "Natalie", "Nathan", "Neil", "Nicholas", "Nicola",
    "Oliver", "Olivia", "Owen", "Paul", "Penelope", "Peter", "Phil", "Piers", "Pippa", "Rachel",
    "Rebecca", "Richard", "Robert", "Rose", "Ruth", "Ryan", "Sally", "Sam", "Samantha", "Sarah",
    "Sean", "Sebastian", "Simon", "Sonia", "Sophie", "Stephanie", "Stephen", "Steven", "Stewart",
    "Sue", "Theresa", "Thomas", "Tim", "Tracey", "Trevor", "Una", "Vanessa", "Victor", "Victoria",
    "Virginia", "Wanda", "Warren", "Wendy", "William", "Yvonne", "Zoe"}
    
            Dim LastNames As New List(Of String) From
    {"Abraham", "Allan", "Alsop", "Anderson", "Arnold", "Avery", "Bailey", "Baker", "Ball", "Bell", "Berry",
    "Black", "Blake", "Bond", "Bower", "Brown", "Buckland", "Burgess", "Butler", "Cameron", "Campbell",
    "Carr", "Chapman", "Churchill", "Clark", "Clarkson", "Coleman", "Cornish", "Davidson", "Davies",
    "Dickens", "Dowd", "Duncan", "Dyer", "Edmunds", "Ellison", "Ferguson", "Fisher", "Forsyth", "Fraser",
    "Gibson", "Gill", "Glover", "Graham", "Grant", "Gray", "Greene", "Hamilton", "Hardacre", "Harris",
    "Hart", "Hemmings", "Henderson", "Hill", "Hodges", "Howard", "Hudson", "Hughes", "Hunter", "Ince",
    "Jackson", "James", "Johnston", "Jones", "Kelly", "Kerr", "King", "Knox", "Lambert", "Langdon",
    "Lawrence", "Lee", "Lewis", "Lyman", "MacDonald", "Mackay", "Mackenzie", "MacLeod", "Manning",
    "Marshall", "Martin", "Mathis", "May", "McDonald", "McGrath", "McLean", "Metcalfe", "Miller",
    "Mills", "Mitchell", "Morgan", "Morrison", "Murray", "Nash", "Newman", "Nolan", "North", "Ogden",
    "Oliver", "Paige", "Parr", "Parsons", "Paterson", "Payne", "Peake", "Peters", "Piper", "Poole",
    "Pullman", "Quinn", "Rampling", "Randall", "Rees", "Reid", "Roberts", "Robertson", "Ross", "Russell",
    "Rutherford", "Sanderson", "Scott", "Sharp", "Short", "Simpson", "Skinner", "Slater", "Smith",
    "Springer", "Stewart", "Sutherland", "Taylor", "Terry", "Thomson", "Tucker", "Turner", "Underwood",
    "Vance", "Vaughan", "Walker", "Wallace", "Walsh", "Watson", "Welch", "White", "Wilkins", "Wilson", "Wright", "Young"}
    
            For I As Integer = 0 To 9999 'Create 10000 names
                Names.Add(FirstNames(RNG.Next(0, FirstNames.Count)) & " " & LastNames(RNG.Next(0, LastNames.Count)))
            Next
        End Sub
    End Class
    

    Wednesday, January 23, 2019 3:21 AM
  • Hi Karen,

    As usual the code is awesome and works perfect with the text box. Would you be able to suggest a way to do the same for rich text control.

    I was trying the below method but doesn't seems to be much effective.

    Convert each word in rich text to an array then search db for with each array element.

    So that if some one type the names along with other words example "am trying to mess with system Dany miller you cant catch me" would also trigger an alert.


    Coderv9

    Wednesday, January 23, 2019 12:56 PM
  • Hi Karen,

    As usual the code is awesome and works perfect with the text box. Would you be able to suggest a way to do the same for rich text control.

    I was trying the below method but doesn't seems to be much effective.

    Convert each word in rich text to an array then search db for with each array element.

    So that if some one type the names along with other words example "am trying to mess with system Dany miller you cant catch me" would also trigger an alert.


    Coderv9

    In my entire time working with .NET VB or C# have never even touched a RichTextBox control so I have nothing to offer. Best advice is to access the Text property and treat the data like any string and use the same logic I presented before as in this conceptual example looking for Dan Miller.

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim found = RichTextBox1.
                Text.
                IndexOf("Dan Miller", StringComparison.OrdinalIgnoreCase) >= 0
        MessageBox.Show($"Dan is found? {found}")
    End Sub


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, January 23, 2019 1:18 PM
    Moderator
  • Thank you so much Karen :) We will use the logic for rich text box...

    Coderv9

    Wednesday, January 23, 2019 1:48 PM
  • Thank You Devon, We will have this code for future implementations :)

    Coderv9

    Wednesday, January 23, 2019 2:04 PM