Mastering the Art of Navigating and Searching Records: Your Ultimate Guide to Effortless Data Discovery in VB.Net 2023
Navigating and Searching Records
Let’s expand our this chapter a little more as we have successfully inserted some recods in “std_registartion” table so it is time to allow users to navigate through the records in the “std_registration” table, we can use the BindingSource and CurrencyManager objects. First, we need to create a new instance of the BindingSource object and set its DataSource property to the DataTable that contains our records. Then, we create a new instance of the CurrencyManager object and set its BindingSource property to the BindingSource we just created.
Let’s first create a function that will move our user to the last record.
Public Sub move_last()
Using connection As SqlConnection = New SqlConnection(connectionString)
Dim cm As CurrencyManager = CType(bindingSource.CurrencyManager, CurrencyManager)
If connection.State = ConnectionState.Closed Then
connection.Open()
End If
currencyManager.Position = currencyManager.Count - 1
End Using
End Sub
The provided code is used to move to the last record in the dataset. It first creates a SqlConnection object using the connection string specified in the code. The CurrencyManager object is then obtained from the BindingSource object. The Connection object is then opened if it is in a closed state. Finally, the Position property of the CurrencyManager object is set to the index of the last record in the dataset by subtracting 1 from the Count property.
In simpler terms, the function sets the current position to the last record in the dataset, allowing the user to view the last record of the table.
The next function will move back to the first record”
Public Sub move_first()
Using connection As SqlConnection = New SqlConnection(connectionString)
Dim cm As CurrencyManager = CType(bindingSource.CurrencyManager, CurrencyManager)
If connection.State = ConnectionState.Closed Then
connection.Open()
End If
currencyManager.Position = 0
End Using
End Sub
The “move_first” function is used to navigate to the first record in the dataset. We set the position of the CurrencyManager object to 0, which will move the current record to the first record in the dataset. Finally, we close the connection using the “End Using” statement.
In summary, this function navigates to the first record in the dataset by setting the position of the CurrencyManager to 0.
Okay! Now let’s move to the next record one by one.
Public Sub move_next()
Using connection As SqlConnection = New SqlConnection(connectionString)
Dim cm As CurrencyManager = CType(bindingSource.CurrencyManager, CurrencyManager)
If connection.State = ConnectionState.Closed Then
connection.Open()
End If
currencyManager.Position += 1
End Using
End Sub
This function is used to move to the next record in the data set. It first creates a new SqlConnection object using the connection string provided. Then, it gets a reference to the CurrencyManager object associated with the BindingSource object.
If the connection state is not open, it opens the connection. Finally, it increments the position of the CurrencyManager object by 1, which moves the data cursor to the next record in the data set.
Now let’s move in the reverse direction:
Public Sub move_previoust()
Using connection As SqlConnection = New SqlConnection(connectionString)
Dim cm As CurrencyManager = CType(bindingSource.CurrencyManager, CurrencyManager)
If connection.State = ConnectionState.Closed Then
connection.Open()
End If
currencyManager.Position -= 1
End Using
End Sub
This function is used to move the position of the record to the previous record in the data source using the CurrencyManager object. The code begins by creating a new instance of the SqlConnection object using the connection string to connect to the database. The CurrencyManager object is retrieved from the BindingSource object using the CurrencyManager property.
The code then checks if the connection to the database is already open, and if not, it is opened using the Open() method. Finally, the Position property of the CurrencyManager object is decremented by 1 to move to the previous record in the data source.
Overall, this function enables users to navigate to the previous record in the data source when viewing data on the form.
We need now to create a menu for navigation in the MDIParent1 form, therefore open the MDIParent1 form in the design view and make a new menu for naviagtion like the one shown below in the iamge.
Now double click “First Record” sub menu and code it as below.
Try
Dim newmdichild As New Form1
newmdichild = CType(ActiveMdiChild, Form1)
newmdichild.move_first()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
Double click “Next Record” sub menu and code it as below.
Try
Dim newmdichild As New Form1
newmdichild = CType(ActiveMdiChild, Form1)
newmdichild.move_next()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
Double click “Previous Record” sub menu and code it as below.
Try
Dim newmdichild As New Form1
newmdichild = CType(ActiveMdiChild, Form1)
newmdichild.move_previous()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
Double click “Last Record” sub menu and code it as below.
Try
Dim newmdichild As New Form1
newmdichild = CType(ActiveMdiChild, Form1)
newmdichild.move_last()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
Okay! Now run your app and check the navigation menu to see how it works.
Now let’s go to start our next task that is how the user will find a specific record. To do this possible, let first drag and drop a GroupBox onto the Form1. Add a Textbox, a Label and a button control from tools menu to the group box as shown below in the image.
Set all the caption, position etc in properties windows as shown in the above image. Also make the visible property of the groupbox to false. we will make it visible whenever it is required to the users.
Now let’s create a function in the Form1 code window that will be responsible to search a record by ID.
Public Sub search_by_id(id As Integer)
Using connection As SqlConnection = New SqlConnection(connectionString)
Dim query As String = "SELECT * FROM std_registration WHERE id = @id"
Dim command As SqlCommand = New SqlCommand(query, connection)
command.Parameters.AddWithValue("@id", id)
If connection.State = ConnectionState.Closed Then
connection.Open()
End If
Dim dataTable As DataTable = New DataTable()
dataTable.Load(command.ExecuteReader())
If dataTable.Rows.Count > 0 Then
currencyManager.Position = bindingSource.Find("id", id)
Else
MessageBox.Show("No record found with the specified ID.")
End If
End Using
End Sub
This code defines a ‘search_by_id’ method that takes an integer ‘id’ parameter. It creates a SQL query to select all fields from the ‘std_registration’ table where the ‘id’ field matches the provided ‘id’ parameter.
Then it creates a SqlCommand object with the query and adds the ‘id’ parameter to it. It checks the connection state, opens the connection if it is closed, and loads the result of the query into a dataTable.
If the dataTable has at least one row, it uses the Find method of the bindingSource object to find the position of the row in the data source that matches the provided ‘id’. Finally, it sets the currencyManager.Position to that position so that the user can navigate to that record.
If no records are found with the specified ‘ID’, it displays a message box informing the user that no records were found.
Next create 2nd function in the same windows of Form1. This function will be responsible to search a specific record by student’s name.
Public Sub search_by_name(name As String)
Using connection As SqlConnection = New SqlConnection(connectionString)
Dim query As String = "SELECT * FROM std_registration WHERE name LIKE @name"
Dim command As SqlCommand = New SqlCommand(query, connection)
command.Parameters.AddWithValue("@name", "%" + name + "%")
If connection.State = ConnectionState.Closed Then
connection.Open()
End If
Dim dataTable As DataTable = New DataTable()
dataTable.Load(command.ExecuteReader())
If dataTable.Rows.Count > 0 Then
currencyManager.Position = bindingSource.Find("name", name)
Else
MessageBox.Show("No record found with the specified name.")
End If
End Using
End Sub
This code defines a method search_by_name that searches for records in the std_registration table by the student’s name. The method takes a name parameter as input, which is used to create a parameterized SQL query that searches for all records with a name that contains the input string.
The method then opens a new SQL connection and executes the query. If any records are found, the method sets the position of the currencyManager to the index of the first record that matches the input name. If no records are found, a message box is displayed informing the user that no record was found with the specified name.
Open the Form1 in the design view and double click the button with in the groupbox to open its click event and code it as below:
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
If Label12.Text = "Find a record by ID" Then
search_by_id(Val(TextBox12.Text))
Else
search_by_name(TextBox12.Text)
End If
GroupBox1.Visible = False
End Sub
Let’s go back to the MDIParent1 form and open it in the design view. Create the Search Menu as shown below in the image.
Double click ‘Find by ID’ and code it as below.
Try
Dim newmdichild As New Form1
newmdichild = CType(ActiveMdiChild, Form1)
newmdichild.Label12.Text = "Find a record by ID"
newmdichild.GroupBox1.Visible = True
newmdichild.TextBox12.Text = ""
newmdichild.TextBox12.Focus()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
Double click the next menu option that is ‘Find by Name’ and code it as below.
Try
Dim newmdichild As New Form1
newmdichild = CType(ActiveMdiChild, Form1)
newmdichild.Label12.Text = "Find a record by name"
newmdichild.GroupBox1.Visible = True
newmdichild.TextBox12.Text = ""
newmdichild.TextBox12.Focus()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
Run the app and check your both functions to search records by ID as well as by Name.
Let’s move further to complete our remaining task of the previous lesson thats how our users will be able if they wish to change any existing record. For this purpose, let’s create a function in the Form1 code, that will be responsible to update the modified record as shown below.
Public Sub update_record()
Using connection As SqlConnection = New SqlConnection(connectionString)
Dim id As Integer = Val(TextBox1.Text)
Dim query As String = "UPDATE std_registration SET name = @name, father_name = @father_name, gender = @gender, class = @class, section = @section, remarks = @remarks, date_of_birth = @date_of_birth, date_of_admission = @date_of_admission, date_of_withdrawal = @date_of_withdrawal, address = @address WHERE id = @id"
Dim command As SqlCommand = New SqlCommand(query, connection)
command.Parameters.AddWithValue("@id", id)
command.Parameters.AddWithValue("@name", TextBox2.Text)
command.Parameters.AddWithValue("@father_name", TextBox3.Text)
command.Parameters.AddWithValue("@gender", TextBox4.Text)
command.Parameters.AddWithValue("@class", TextBox5.Text)
command.Parameters.AddWithValue("@section", TextBox6.Text)
command.Parameters.AddWithValue("@remarks", TextBox7.Text)
command.Parameters.AddWithValue("@date_of_birth", TextBox8.Text)
command.Parameters.AddWithValue("@date_of_admission", TextBox9.Text)
command.Parameters.AddWithValue("@date_of_withdrawal", TextBox10.Text)
command.Parameters.AddWithValue("@address", TextBox11.Text)
If connection.State = ConnectionState.Closed Then
connection.Open()
End If
Dim rowsAffected As Integer = command.ExecuteNonQuery()
If rowsAffected > 0 Then
MessageBox.Show("Record updated successfully.")
Else
MessageBox.Show("No record found with the specified ID.")
End If
End Using
locktextboxes()
End Sub
Create one function more that will allow the user to do modifications as we have already locked our controls.
Public Sub modify_existing_record()
unlocktextboxes()
TextBox2.Focus()
End Sub
Now go to the MDIParent1 Form, open it in the design view and double click the Edit toolstripmenu to open it in the code window. Past the following code in it.
Try
addnew_edit = 2 ' it means that the user has clicked the add new
Dim newmdichild As New Form1
newmdichild = CType(ActiveMdiChild, Form1)
newmdichild.modify_existing_record()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
Again open the MDIParent1 form in the design view and double click the Save menu option and modify the existing code with the following.
Try
Dim newmdichild As New Form1
newmdichild = CType(ActiveMdiChild, Form1)
If addnew_edit = 1 Then
newmdichild.save_add_new_data()
ElseIf addnew_edit = 2 Then
newmdichild.update_record()
End If
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
Run your application, click the Edit, make changes to the record and click the Save option.
Now we have only one option that is if the user change the mind to not save the modifications and wish to undo his/her work if done in any textbox, so let them do it by clicking the Undo option.
Open Form1 in code view and create a function like this.
Public Sub undo_changes()
If DataTable.Rows.Count > 0 Then
Dim originalRow As DataRow = DataTable.Rows(currencyManager.Position)
Dim currentRow As DataRowView = CType(currencyManager.Current, DataRowView)
currentRow.Row.ItemArray = originalRow.ItemArray
MessageBox.Show("Original data has been restored.")
Else
MessageBox.Show("No record to restore.")
End If
locktextboxes()
End Sub
This function, in searching records code, first checks if there are any rows in the dataTable. If there are, it retrieves the original row from the dataTable using the originalRowIndex variable. It then retrieves the current row using the currencyManager and sets its ItemArray property to that of the original row. This effectively restores the original data. A message box is displayed to indicate that the original data has been restored. If there are no rows in the dataTable, a message box is displayed to indicate that there is no record to restore.
Okay! let’s call this function from our main menu. Open the MDIParent1 form in the design view and double click the undo menu open to open its code window and past the following code in it.
Try
Dim newmdichild As New Form1
newmdichild = CType(ActiveMdiChild, Form1)
newmdichild.undo_changes()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
That’s all, we have completed our chapter successfully. Now let’s give a final touch to our this lesson. First of all download some 32×32 bits png image from any free site and save them in a local folder. Now go back to the design view of MDIParent1 and design the toolbar menu by following these steps.
- Create first toolmenu for Add New Record. In properties window set the tooltiptext to “Add New Record”.
- Click the three dotteds button of the image setting in the same property window. A new dialog box will appear.
- Click the import button and brows the image/icon that you have download for the ‘Add New’ record.
Do the same steps for all the the remainig toolbars in the toolstrip menu giving appropriate tooltipstext to each one to make best app in searching records . Your toolmenustrip should be like one as shown below in the image.
In searching records main Form i Mean , Open the MDIParent1 in the design view and double click the ‘Add New’ from the menustrip and copy the line upto AddNewToolStripMenuItem_Click. Go back to the design view and double click your first toolmenustrip that is for Add New and code it as below.
AddNewToolStripMenuItem_Click(Nothing, Nothing)
follow the same steps and code all the remaining toolbarstrips one by one as for making it easy how to do searching records :
Private Sub ToolStripButton2_Click(sender As Object, e As EventArgs) Handles ToolStripButton2.Click
EditToolStripMenuItem_Click(Nothing, Nothing)
End Sub
Private Sub ToolStripButton3_Click(sender As Object, e As EventArgs) Handles ToolStripButton3.Click
UndoToolStripMenuItem_Click(Nothing, Nothing)
End Sub
Private Sub ToolStripButton4_Click(sender As Object, e As EventArgs) Handles ToolStripButton4.Click
SaveToolStripMenuItem_Click(Nothing, Nothing)
End Sub
Private Sub ToolStripButton5_Click(sender As Object, e As EventArgs) Handles ToolStripButton5.Click
FirstRecordToolStripMenuItem_Click(Nothing, Nothing)
End Sub
Private Sub ToolStripButton7_Click(sender As Object, e As EventArgs) Handles ToolStripButton7.Click
NextRecordToolStripMenuItem_Click(Nothing, Nothing)
End Sub
Private Sub ToolStripButton6_Click(sender As Object, e As EventArgs) Handles ToolStripButton6.Click
PreviousRecordToolStripMenuItem_Click(Nothing, Nothing)
End Sub
Private Sub ToolStripButton8_Click(sender As Object, e As EventArgs) Handles ToolStripButton8.Click
LastRecordToolStripMenuItem_Click(Nothing, Nothing)
End Sub
Private Sub ToolStripButton9_Click(sender As Object, e As EventArgs) Handles ToolStripButton9.Click
FindByIDToolStripMenuItem_Click(Nothing, Nothing)
End Sub
Private Sub ToolStripButton10_Click(sender As Object, e As EventArgs) Handles ToolStripButton10.Click
FindByNameToolStripMenuItem_Click(Nothing, Nothing)
End Sub
You have successfully linked your all tool bars icons with the menu bar options. Now run the application and check how these icons do the given task and how you can use searching records option.
Conclusion
In the dynamic landscape of software development, the process of searching records stands as a fundamental pillar, forming the backbone of data-driven applications. Throughout this exploration of searching records in VB.NET, we’ve delved into an array of techniques and strategies that empower developers to retrieve, filter, and present data efficiently and intuitively.
By harnessing the power of various algorithms, from basic linear searches to more sophisticated binary searches, developers can navigate datasets with precision. The integration of searching records in SQL databases adds an extra dimension to data manipulation, as structured queries provide the means to extract specific information with remarkable accuracy.
The advent of LINQ in VB.NET ushers in a new era of querying, enabling seamless interaction with datasets using a familiar syntax. By tapping into LINQ’s capabilities, developers can streamline their code, enhance readability, and effortlessly perform complex data retrieval tasks.
As applications grow and user expectations evolve, the presentation of search results gains paramount importance. Designing user interfaces that empower users to define parameters, apply filters, and receive results that align with their intentions ensures a user-centric approach to searching records.
The journey of searching records in VB.NET is one of innovation, optimization, and adaptability. As technology advances and data volumes surge, staying attuned to emerging techniques and best practices remains essential. With each line of code dedicated to enhancing searching records, developers contribute to the seamless functioning of applications, shaping user experiences, and driving the progression of software development.
Q: 1. What does “searching records” mean in VB.NET?
A: Searching records in VB.NET refers to the process of retrieving specific data entries from datasets or databases based on user-defined criteria or search parameters.
Q: 2. What are some common algorithms used for efficiently “searching records” in VB.NET?
A: Common algorithms for searching records include linear search, binary search, and hash-based searching. These algorithms offer ways to efficiently locate records within datasets.
Q: 3. How can I optimize the performance of “searching records” in my VB.NET application?
A: Optimizing searching records performance involves employing appropriate indexing, retrieving only necessary fields, and selecting efficient search algorithms aligned with data structure.
Q: 4. What role does LINQ play in “searching records” in VB.NET?
A: Language Integrated Query (LINQ) simplifies querying and manipulating data across various sources, including databases. LINQ enhances code readability and facilitates complex searching records tasks.
Q: 5. What are the best practices for designing interfaces for “searching records” in VB.NET applications?
A: Design interfaces for searching records that offer intuitive search fields, filters, and sorting options. Consider incorporating features like auto-complete to elevate user experience.
Q: 6. Can I implement real-time “searching records” in my VB.NET application?
A: Yes, real-time searching records can be achieved by utilizing event handlers or triggers that respond to user input, updating search results in real-time as users type.
Q: 7. How do I manage large datasets when “searching records” in VB.NET?
A: Managing large datasets involves optimizing queries, implementing pagination to retrieve subsets of data, and considering asynchronous operations to prevent application slowdown.
Q: 8. Why is error handling crucial in implementing “searching records” in VB.NET?
A: Proper error handling is essential for a seamless user experience. Handling exceptions, addressing database connectivity issues, and managing invalid search parameters prevent disruptions.
Q: 9. Is it possible to implement fuzzy search or partial matching while “searching records” in VB.NET?
A: Absolutely, by using techniques like wildcard characters or regular expressions while searching records , you can implement fuzzy search or partial matching to retrieve records that closely match search terms.
Q: 10. Where can I find resources to learn more about “searching records” in VB.NET?
A: Valuable resources for mastering techniques in searching records in VB.NET include online tutorials, Microsoft documentation, programming forums, and repositories like GitHub.
More Links
