Pen Truth Contributor
This is the fifth installment of working with databases in VB.Net using Visual Studio 2008. In previous edition we filled a data grid with a table from our database. Also, we learned how to write data to the table and how to clear the table. In this edition we will write code that will allow us to search the table for data matching an entry in a search box and to delete that data from the table. We will also optimize our program by eliminating some unneeded steps.
Before we begin, add several name entries into your data table. Some of your entries should have the same last name. Space the identical last names a few names apart. Twenty different names will be enough for what we will need in this exercise. Once you have completed entering all the names we will begin with the next steps.
Our first order of business is to do some house cleaning. In the Main Form Loading Event, frnMain_Load add the following call:
fillDataTable() ' Call And Show Results
This will fill the grid view when our program first loads. We will also add this call to the button event btnAddData_Click. Place the code at the bottom so that it is the last item the event acts on.
firstName = txbFirstName.Text ' Text that gets entered into the text box will be entered into the table lastName = txbLastName.Text' Text that gets entered into the text box will be entered into the table MsgBox(addData()) ' Call And Show Results fillDataTable() ' Call And Show Results
Now that we have a little more automation in our program lets start coding for the table search. First we need to add five items from the Tool Box. We will need two buttons, a label, a text box for entering the search name and a list box to store our search results. For speed you can copy what you already have on the main form. Click on the label that has the text “Last Name”, then hold the shift key and select the text box with the text “Doe”. Let off the Shift Key and press CTRL C, or hover your mouse over the selections, right click and select Copy.
Paste the items to a convenient location and rename the text box to txbSearchName. Change the label text to “Enter A Last Name”. Place two buttons under the the label and text box. The first button will be named btnSearch with “Search” as the text. The second button will be named btnDelete with “Delete” as the text. Below the buttons ad a list box and rename it to lbxFoundNames.
We don’t need to have any values passed to us by a function as we will se the results displayed, so we will stick to subs. Error messages will ba passed by the Try Catch. We should have everything that we need in the way of string variables, so in the region set aside for functions add the following code:
Sub searchDataBaseTable()
Dim dbData As New DataSet ' Create our data set object
Dim dbConnect As New OleDbConnection(dbProvider & dbNameLoc)' Create our database connection object
Dim dbCommand As New OleDbCommand(dbSelectFrom, dbConnect) ' Set the command
try
dbConnect.Open() ' Open the database connection
Dim dbAdapter As New OleDbDataAdapter(dbCommand) ' Create the adapter for the open database table
dbAdapter.Fill(dbData, dbTableName) ' retrieve the data from the table
Dim dbCount As New OleDb.OleDbCommand("Select COUNT(*) FROM " & dbTableName, dbConnect) ' Create the command for counting rows
If dbCount.ExecuteScalar() < 1 Then ' Test to see if there is any data in the table
MsgBox("No Records Were Found")
dbConnect.Close() ' Make sure that we close the connection
Exit Sub ' Exit since there is no reason to go further
End If ' Done testing if there is any data in the table
dbConnect.Close() ' Close the connection
Dim dbTable As DataTable = dbData.Tables(dbTableName) ' Row data can't be read directly from the data set.
Dim dbRow As DataRow ' Create a data row object
lbxFoundNames.Items.Clear() ' Clear any existing items from the list box readying it for new data
For Each dbRow In dbTable.Rows ' As long as there are rows to read data from, increments and gets next row of data
If dbRow(1) = txbSearchName.Text Then ' Test if the last name from table matches the one we are searching for
firstName = dbRow(0) ' Could have added the data directly into our list box
lastName = dbRow(1) ' but we can use these strings in our delete operation
lbxFoundNames.Items.Add(dbRow(0) & " " & dbRow(1)) ' Add items to list box
End If ' Done testing if the last name from table matches the one we are searching for
Next ' Go to the next record
Catch ex As Exception
MsgBox(ex.Message)
Finally
dbConnect.Close()
End Try
End Sub
End Sub
The only really new data base function in this sub is dbCount.ExecuteScalar(). dbCount gets the number of rows that exist in the table. If the data table is empty the code doesn’t need to go any further so a message is displayed telling us that no records were found and exits the sub routine. We connect to the database the same way as we that have been throughout these exercises.
The command setup hasn’t changed and the adapter is being used again as when we added data to the table. We can’t extract rows from the data set in dbData or fill dbTable from the adapter and we also need an object that allows us to move through the rows in the database. The For Each statement will pull data from the rows in our table as long as there are rows to read. The If statement tests the data against out text entry box and a true result will add data from the rows with matching last names. The If test can be expanded to test against both the first and last names using a second text box and the AND command.
In the event for btnSearch_Click, add a call to our search sub:
searchDataBaseTable()
Once you are done, save your project and Start Debug. Try some of the different names in your table. Try names with a single entry and try names with duplicate last names.
The delete function isn’t much different than the search function. Where the real difference is when we test each row for text than matches our search string a message box pops up with a Yes and No button asking if the displayed name is to be deleted. Enter the following code in the Functions region:
Sub deleteTableEntry()
Dim dbData As New DataSet
Dim dbConnect As New OleDbConnection(dbProvider & dbNameLoc)
Dim dbCommand As New OleDbCommand(dbSelectFrom, dbConnect)
Try
dbConnect.Open()
Dim dbAdapter As New OleDbDataAdapter(dbCommand)
dbAdapter.Fill(dbData, dbTableName)
dbConnect.Close()
Dim dbTable As DataTable = dbData.Tables(dbTableName)
Dim dbRow As DataRow
lbxFoundNames.Items.Clear()
For Each dbRow In dbTable.Rows
If dbRow(1) = txbSearchName.Text Then ' Test if the last name matches the one we are searching for
firstName = dbRow(0)
lastName = dbRow(1)
Dim result = MsgBox("Delete " & firstName & " " & lastName & "?", MessageBoxButtons.YesNo)
If result = MsgBoxResult.Yes Then ' Message box asking to delete yes/no
dbDelete = "DELETE * FROM " & dbTableName & " WHERE First_Name = '" & firstName & "' AND Last_Name = '" & lastName & "'"
dbConnect.Open()
dbCommand = New OleDbCommand(dbDelete, dbConnect)
dbCommand.ExecuteNonQuery()
End If
End If
Next
Catch ex As Exception
MsgBox(ex.Message)
Finally
dbConnect.Close()
End Try
End Sub
In button event btnDeleteName input the following code:
deleteTableEntry() ' Search and delete selected data
fillDataTable() ' Call And Show Results
Save your project and Start Debug. Try deleting some of the entries. Did it work? Try adding and deleting different names. This pretty much is all there is to it. We could have used Visual Studio’s Data Sources Wizard to select a database and the the data and let the wizard bind it to a grid view for us. However, there are instances where you need to have more control over data, or your data needs special handling. For debugging, here is a quick tip; If you have a section of code that is good that gets called often, you can add this code above a sub or function: <System.Diagnostics.DebuggerStepThrough()> _
<System.Diagnostics.DebuggerStepThrough()> _ Private Sub mySubOrFunction()
For people working with microcontroller project boards who want to do data logging, they can use serial connections to a PC via the serial com or using a USB to Serial dongle and write their data logs right to a database directly without binding to grid views, which is how I got to doing this project. So enjoy and check out the follow up installment to see suggestions for cleaning up the code a little more.
In the sixth and final installment of working with databases in VB.Net using Visual Studio 2008 we will be cleaning up some more of our code now that you have a good idea how all of this works. I will also post my completed code so you can see how I tackled condensing the program and used a module to contain all the functions.
Return to “How To Insert Data Into A Table Within A Database”
Go to “Database Tutorial Source Code”
In my completed code I pass values to the functions so that the code is much more portable. After the last installment I will post my complete code for the frmMain.
Disclaimer: The code in this tutorial is not intended as a final product or by no means the only way this project can be coded. The presented code is for educational purposes only and no warranty is granted or implied. I/we are not responsible for any damages as a result of using this code. Use at your own risk.