More ADO.NET
(Customer Table Maintenance Example)
This sample project demonstrates the use of ADO.NET in the context of a simple "one-table" database application. The sample application works with a table of customer data (name, address, phone, etc.) and allows the user to view, add, update, and delete customer records. Also, this sample project uses the ListView control to display the records that the application will be working with. The use of the ListView control is covered in this topic.
The sample application uses an MS-Access database called Cust.mdb, consisting of one table called Customer. The columns of the table are defined as follows:
|
Column Name |
Data Type |
|
CustID |
Number (Long Integer) |
|
LastName |
Text (50) |
|
FirstName |
Text (50) |
|
Address |
Text (50) |
|
City |
Text (25) |
|
State |
Text (2) |
|
Zip |
Text (5) |
|
PhoneNumber |
Text (10) |
Screen-shots of the sample application are shown below.
Initially, this screen is in "read-only" mode; only the list portion is active. The data entry fields on the bottom half of the screen are grayed out are not enterable. The buttons to add, update, and delete are available; the buttons to save or cancel are not available. A screen-shot of the screen in this state is shown below:
To add a customer, click the "Add" button. This will enable the fields on the bottom half of the screen. The buttons to add, update, and delete will become unavailable; the buttons to save or cancel will be available. A screen-shot of the screen in this state is shown below:
You can then enter the values for the new record:
When you are done entering data into the fields, click the Save button, which will cause the new record to be added to the list and will cause the screen to revert back to its initial "read-only" state. A screen-shot of the screen in this state is shown below:
To modify data for an existing customer record, click the customer record in the list to highlight it and click the "Update" button. This will enable the fields on the bottom half of the screen. The buttons to add, update, and delete will become unavailable; the buttons to save or cancel will be available. When you are done changing the data in the fields, click the Save button, which will cause the record to be updated in the list and will cause the screen to revert back to its initial "read-only" state.
To delete a customer record, click the customer record in the list to highlight it and click the "Delete" button. The following message will be displayed:
Responding "Yes" to the confirmation prompt will cause the application to delete the record and remove it from the list.
To exit the Customer Maintenance screen, click the "Close" button when the screen is in its "read only" state. The Close button will not be available when you have an "add" or "update" pending. If you attempt to close the screen with the Windows "X" button while you have an "add" or "update" pending, the following message will be displayed:
Highlights of the code, as it relates to ADO.NET, are presented below.
· In a module named "modGeneral", a variable to hold the connection string for the database has been declared:
Public DB_CONN_STRING As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
& My.Application.Info.DirectoryPath & "\cust.mdb"
In the connection string defined above, two parameters are required to connect to the Access database: first, the Provider parameter (which specifies Microsoft.Jet.OLEDB.4.0; and second, the Data Source parameter, which specifies the full path of the Access mdb file. In the case of this sample application, it is expected that the mdb file reside in the same folder as the executable (.exe) file. Note that the syntax of the ConnectionString requires each parameter name to be followed by an equal sign (=) followed by the value. Each parameter name / value pair is separated by a semicolon (;). Embedded spaces in the parameter names and values are evaluated properly. For example, if your application was located in the folder "C:\My VB Apps\Database Apps", the content of the ConnectionString would be:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\My VB Apps\Database Apps\Cust.mdb"
Note: If you connect to other databases such as Oracle or SQL Server, different parameters (such as Server, UID, and PWD) will be required; however the syntax rules described above still apply.
· In the "LoadCustomerListView" Sub, the contents of the Customer table is loaded into the listview.
Initially, the three necessary ADO.NET objects are declared (objConn for the OleDbConnection, objCmd for the OleDbCommand, and objReader for the OleDbDataReader). Further on, a SQL SELECT statement is built in the String variable strSQL. Then, the necessary statements to create a resultset based on the SELECT statement that can be processed by the program are coded: the connection object is instantiated with the connection string established in the DB_CONN_STRING variable, the command object is instantiated by giving it the SQL statement to execute and the connection to use, the connection is opened through the command object, then the reader object is set to the results of the ExecuteReader method, giving us the records we need to work with.
The code then loops through the reader, populating each row of the listview. The reader and the connection objects are then closed, disconnecting from the database. All manipulations of the data from the point on are handled through the listview, until it is time to update or insert a record back into the database.
Private Sub LoadCustomerListView()
Dim objConn As OleDbConnection
Dim objCmd As OleDbCommand
Dim objReader As OleDbDataReader
Dim objLI As ListViewItem
Dim strSQL As String
Dim strPhone As String
lvwCustomer.Items.Clear()
strSQL = "SELECT FirstName" _
& " , LastName" _
& " , Address" _
& " , City" _
& " , State" _
& " , Zip" _
& " , PhoneNumber" _
& " , CustID" _
& " FROM Customer " _
& " ORDER BY LastName" _
& " , FirstName"
objConn = New OleDbConnection(DB_CONN_STRING)
objCmd = New OleDbCommand(strSQL, objConn)
objCmd.CommandType = CommandType.Text
objCmd.Connection.Open()
objReader = objCmd.ExecuteReader
Do While objReader.Read
strPhone = ReplaceDBNull(objReader("PhoneNumber"))
If Len(strPhone) > 0 Then
strPhone = "(" & Strings.Left(strPhone, 3) & ") " _
& Mid(strPhone, 4, 3) & "-" _
& Strings.Right(strPhone, 4)
End If
objLI = New ListViewItem(ReplaceDBNull(objReader("FirstName")), "Custs")
objLI.SubItems.Add(ReplaceDBNull(objReader("LastName")))
objLI.SubItems.Add(ReplaceDBNull(objReader("Address")))
objLI.SubItems.Add(ReplaceDBNull(objReader("City")))
objLI.SubItems.Add(ReplaceDBNull(objReader("State")))
objLI.SubItems.Add(ReplaceDBNull(objReader("Zip")))
objLI.SubItems.Add(strPhone)
objLI.SubItems.Add(ReplaceDBNull(objReader("CustID")))
lvwCustomer.Items.Add(objLI)
Loop
objReader.Close()
objConn.Close()
If lvwCustomer.Items.Count > 0 Then
lvwCustomer.Items(0).Selected = True
End If
End Sub
· In the "btnSave_Click" event procedure, depending on whether an add or update has been initiated, a SQL INSERT or UPDATE statement is built with the String variable strSQL. The connection and command objects are set up as described previously, however, to execute the SQL, the ExecuteNonQuery method of the command object is used.
Note that the record ID plays an important role. For an add, the new record ID is obtained by calling the programmer-defined function GetNextRecordID, which is described a little later below. For an update, the record ID of the currently selected item in the listview is used in the UPDATE statement to update the record. In either case, the contents of the listview is updated appropriately to reflect the insert or update.
Private Sub btnSave_Click(sender As System.Object, e As System.EventArgs) Handles btnSave.Click
Dim strPhone As String
Dim objNewListViewItem As ListViewItem
Dim intIDField As Integer
Dim strSQL As String
If Not ValidateFormFields Then Exit Sub
strPhone = txtArea.Text & txtPrfx.Text & txtLine.Text
If mstrMaintMode = "ADD" Then
intIDField = GetNextRecordID("Customer", "CustID")
strSQL = "INSERT INTO Customer( CustID"
strSQL &= " , FirstName"
strSQL &= " , LastName"
strSQL &= " , Address"
strSQL &= " , City"
strSQL &= " , State"
strSQL &= " , Zip"
strSQL &= " , PhoneNumber"
strSQL &= ") VALUES ("
strSQL &= intIDField
strSQL &= ", '" & Replace(txtFirst.Text, "'", "''") & "'"
strSQL &= ", '" & Replace(txtLast.Text, "'", "''") & "'"
strSQL &= ", '" & Replace(txtAddr.Text, "'", "''") & "'"
strSQL &= ", '" & Replace(txtCity.Text, "'", "''") & "'"
strSQL &= ", '" & txtState.Text & "'"
strSQL &= ", '" & txtZip.Text & "'"
strSQL &= ", '" & strPhone & "'"
strSQL &= ")"
objNewListViewItem = lvwCustomer.Items.Add(txtFirst.Text, "Custs")
For intX As Integer = 1 To 7
objNewListViewItem.SubItems.Add("")
Next
PopulateListViewItem(objNewListViewItem)
With objNewListViewItem
.SubItems(mintCUST_ID_IDX).Text = CStr(intIDField)
.EnsureVisible()
End With
objNewListViewItem.Selected = True
Else
intIDField = CInt(lvwCustomer.SelectedItems(0).SubItems(mintCUST_ID_IDX).Text)
strSQL = "UPDATE Customer SET "
strSQL &= " FirstName = '" & Replace(txtFirst.Text, "'", "''") & "'"
strSQL &= ", LastName = '" & Replace(txtLast.Text, "'", "''") & "'"
strSQL &= ", Address = '" & Replace(txtAddr.Text, "'", "''") & "'"
strSQL &= ", City = '" & Replace(txtCity.Text, "'", "''") & "'"
strSQL &= ", State = '" & txtState.Text & "'"
strSQL &= ", Zip = '" & txtZip.Text & "'"
strSQL &= ", PhoneNumber = '" & strPhone & "'"
strSQL &= " WHERE CustID = " & intIDField
lvwCustomer.SelectedItems(0).Text = txtFirst.Text
PopulateListViewItem(lvwCustomer.SelectedItems(0))
End If
Dim objConn As New OleDbConnection(DB_CONN_STRING)
Dim objCommand As New OleDbCommand(strSQL, objConn)
objCommand.CommandType = CommandType.Text
objCommand.Connection.Open()
objCommand.ExecuteNonQuery()
objConn.Close()
SetFormState(True)
mblnUpdateInProgress = False
End Sub
· When we need to add a new record, a new, unique record ID must generated. In this particular sample application, the record ID is defined as a Long Integer in the Access database (a Long Integer in an Access 2000 format database equates to the "Integer" type in VB). The GetNextRecordID function shown below uses the SQL MAX function to find the highest existing value for the CustID field, adds one to it, and returns that value to the caller. It should be noted that if the CustID field was defined as an Access AutoNumber field, the logic of this application would have to be modified accordingly.
Public Function GetNextRecordID(ByVal pstrTableName As String, _
ByVal pstrFieldName As String) _
As Integer
Dim objConn As OleDbConnection
Dim objCmd As OleDbCommand
Dim objReader As OleDbDataReader
Dim strSQL As String
Dim intNextRecordID As Integer
strSQL = "SELECT MAX(" & pstrFieldName & ") AS MaxID " _
& " FROM " & pstrTableName
objConn = New OleDbConnection(DB_CONN_STRING)
objCmd = New OleDbCommand(strSQL, objConn)
objCmd.CommandType = CommandType.Text
objCmd.Connection.Open()
objReader = objCmd.ExecuteReader
objReader.Read()
If IsDBNull(objReader("MaxID")) Then
intNextRecordID = 1
Else
intNextRecordID = CInt(objReader("MaxID").ToString) + 1
End If
objReader.Close()
objConn.Close()
Return intNextRecordID
End Function
· For a delete action, after we have confirmed that the user truly wants to delete the record, the connection and command objects are set up as described previously, and to execute the SQL, the ExecuteNonQuery method of the command object is used.
Logic is then executed to update the listview to remove the deleted record.
Private Sub btnDelete_Click(sender As System.Object, e As System.EventArgs) Handles btnDelete.Click
Dim strFirstName As String
Dim strLastName As String
Dim intCustID As Integer
Dim intNewSelIndex As Integer
If lvwCustomer.SelectedItems.Count = 0 Then
MsgBox("No Customer selected to delete.", _
vbExclamation, _
"Delete")
Exit Sub
End If
With lvwCustomer.SelectedItems(0)
strFirstName = .Text
strLastName = .SubItems(mintCUST_LAST_IDX).Text
intCustID = CInt(.SubItems(mintCUST_ID_IDX).Text)
End With
If MsgBox("Are you sure that you want to delete Customer '" _
& strFirstName & " " & strLastName & "'?", _
vbYesNo + vbQuestion, _
"Confirm Delete") = vbNo Then
Exit Sub
End If
Dim strSQL As String = "DELETE FROM Customer WHERE CustID = " & intCustID
Dim objConn As New OleDbConnection(DB_CONN_STRING)
Dim objCommand As New OleDbCommand(strSQL, objConn)
objCommand.CommandType = CommandType.Text
objCommand.Connection.Open()
objCommand.ExecuteNonQuery()
objConn.Close()
With lvwCustomer
If .Items(.Items.Count - 1).Selected Then
intNewSelIndex = .Items.Count - 2
Else
intNewSelIndex = .SelectedItems(0).Index
End If
.SelectedItems(0).Remove()
If .Items.Count > 0 Then
.Items(intNewSelIndex).Selected = True
Else
ClearCurrRecControls()
End If
End With
End Sub
Using the ListView Control
In this section of the article, code related to the use of the ListView will be explained. The ListView control can be found in the ToolBox under "Common Controls".
In this project, a ListView control named lvwCustomer and an ImageList control named imlLVIcons were added to the form. (If you want to use icons with your ListView, you must store those icons in an ImageList control that will be tied to the ListView control.) An icon representing "customers" was added to the imlLVIcons ImageList; this image was given a Key property of "Custs".
· The following properties of the ListView were set at design time:
|
Property |
Value |
Notes |
|
View |
Details |
The ListView is used prominently in the MS-Windows interface. It is used to display the lists of files and folders in the Windows Explorer and My Computer applications. In those applications, you can go to the View menu and specify Large Icons, Small Icons, List, or Details, and the appearance of the file list is modified accordingly. The corresponding settings for the VB ListView control can be specified via the View property. Setting the ListView View property to "Details" enables the display of data in a columnar, grid-like fashion. |
|
MultiSelect |
False |
We only want to allow one item at a time to be selected. |
|
FullRowSelect |
True |
This is a Boolean property that is applicable only when View is set to Details. If False (the default), only the first column will be highlighted when a row is selected. If True, the entire row will be highlighted when selected. |
|
GridLines |
True |
This is a Boolean property that is applicable only when View is set to Details. If True, grid lines will border the cells of the ListView. The default is False. |
|
SmallImageList |
imlLVIcons |
Specifies the ImageList that was added to the form. |
· In the Form_Load event, a call is made to the programmer-defined Sub "SetupCustLVCols". Here, we set up the columns for the ListView by using the Add method of the ListView's Columns collection. The arguments for the Add method has a number of overloads; in the code below, only the text and the width arguments are specified. The text argument specifies the text that will appears in the column header. A common technique to specify the width is to use a percentage of the ListView as a whole. In the code below, the First Name will take up 15% of the ListView's width; the Last Name will take up 12%, and so on. In order to prevent a horizontal scroll bar from appearing at the bottom of the ListView, keep the total width under 100% (in the code below, the percentages add up to 95%). (If you want to display many columns, or certain columns must be particularly wide, the total percentage can certainly exceed 100%, in which case a horizontal scroll bar will automatically appear at the bottom of the ListView, and the user will have to scroll to see one or more columns.) Note that you may specify 0 for the column width, in which case you create a "hidden" column, useful for storing data that the user need not see (in this case, the "ID" column). The alignment property is not specified for any of the columns in the code below, so by default, all of the columns will be left-aligned. The other alignment options are centered or right-aligned. The alignment argument for left, center, and right are specified with the enumerations HorizontalAlignment.Left, HorizontalAlignment.Center, and HorizontalAlignment.Right, respectively. The code for this Sub is as follows:
Private Sub SetupCustLVCols()
With lvwCustomer
.Items.Clear()
With .Columns
.Add("First Name", CInt(lvwCustomer.Width * 0.15))
.Add("Last Name", CInt(lvwCustomer.Width * 0.12))
.Add("Address", CInt(lvwCustomer.Width * 0.2))
.Add("City", CInt(lvwCustomer.Width * 0.15))
.Add("St", CInt(lvwCustomer.Width * 0.06))
.Add("Zip", CInt(lvwCustomer.Width * 0.09))
.Add("Phone #", CInt(lvwCustomer.Width * 0.18))
.Add("ID", 0)
End With
End With
End Sub
· Also in the Form_Load event, following the call to "SetupCustLVCols" is the call to the "LoadCustomerListView" Sub. We looked at this Sub earlier to examine the ADO.NET/database aspects of the code; now we will look at it to examine the ListView aspects of the code.
A local ListViewItem object, objLI, is declared. ListViewItem objects represent the entries of the ListView; they are the components of the ListView's Items collection.
After the resultset has been created, we first use the Clear method to remove all existing ListViewItem objects from the ListView (this step is technically not needed in the case of this particular application, but it is good practice to do this – there could be situations where you need to reload a ListView, and failure to clear the existing items will result in extraneous or duplicate items in the list).
As we loop through the Resultset, for each record we create a new ListViewItem object (objLI) using the New method. The New method for the ListViewItem has several overloads; here, we are using the text and imageKey arguments. The text argument sets the Text property of the ListView; it is the text that will appear in the first column of the ListView (in this case, it is the FirstName field from the resultset). The imageKey argument refers to the image that will appear to the left of each ListViewItem; here we specify "Custs", which is the Key of the desired image in the imlLVIcons ImageList control (which we tied to this ListView control). The remaining columns of the ListView are created by using the SubItems.Add method of the ListVeiwItem; each of the remaining fields of the resultset are assigned to the corresponding SubItems entry. Whenever you add more than one ColumnHeader object (as was done in the "SetupCustLVCols" Sub), you need to create corresponding SubItem entries; otherwise, the ListView will look strange at run-time. Most importantly, after the SubItems have been added to the objLI object, the objLI object is added to the ListView using the Items.Add method.
Note: To reference a SubItem, use an index from 1 to the number of columns - 1. For example, lvwCustomer.Items(0).SubItems(1).Text would refer to the last name populated in the first row of the ListView. To reference the data in the first column of the ListView, you can use either SubItems(0) or the Text property of the ListViewItem. For example, both lvwCustomer.Items(0).SubItems(0).Text and lvwCustomer.Items(0).Text both refer to the first name populated in the first row of the ListView. Note that constants are used for the SubItem indexes (you could alternatively just use hard-coded numbers to refer to the indexes). These constants were defined at the form-level:
' Customer LV SubItem Indexes ...
Private Const mintCUST_LAST_IDX As Integer = 1
Private Const mintCUST_ADDR_IDX As Integer = 2
Private Const mintCUST_CITY_IDX As Integer = 3
Private Const mintCUST_ST_IDX As Integer = 4
Private Const mintCUST_ZIP_IDX As Integer = 5
Private Const mintCUST_PHONE_IDX As Integer = 6
Private Const mintCUST_ID_IDX As Integer = 7
After the loop has completed, we then check the Count property of the Items collection. As long as at least one record was loaded, we set the Selected property of the first ListViewItem to True. Normally, the Selected property of a ListViewItem is set when the user clicks on it. Setting the first ListViewItem to True should cause theListView's SelectedIndexChanged event to fire, which will cause the detail area of the form to be populated with the data for the selected customer.
The code for "LoadCustomerListView" is shown below, with the ListView-related code in bold:
Private Sub LoadCustomerListView()
Dim objConn As OleDbConnection
Dim objCmd As OleDbCommand
Dim objReader As OleDbDataReader
Dim objLI As ListViewItem
Dim strSQL As String
Dim strPhone As String
lvwCustomer.Items.Clear()
strSQL = "SELECT FirstName" _
& " , LastName" _
& " , Address" _
& " , City" _
& " , State" _
& " , Zip" _
& " , PhoneNumber" _
& " , CustID" _
& " FROM Customer " _
& " ORDER BY LastName" _
& " , FirstName"
objConn = New OleDbConnection(DB_CONN_STRING)
objCmd = New OleDbCommand(strSQL, objConn)
objCmd.CommandType = CommandType.Text
objCmd.Connection.Open()
objReader = objCmd.ExecuteReader
Do While objReader.Read
strPhone = ReplaceDBNull(objReader("PhoneNumber"))
If Len(strPhone) > 0 Then
strPhone = "(" & Strings.Left(strPhone, 3) & ") " _
& Mid(strPhone, 4, 3) & "-" _
& Strings.Right(strPhone, 4)
End If
objLI = New ListViewItem(ReplaceDBNull(objReader("FirstName")), "Custs")
objLI.SubItems.Add(ReplaceDBNull(objReader("LastName")))
objLI.SubItems.Add(ReplaceDBNull(objReader("Address")))
objLI.SubItems.Add(ReplaceDBNull(objReader("City")))
objLI.SubItems.Add(ReplaceDBNull(objReader("State")))
objLI.SubItems.Add(ReplaceDBNull(objReader("Zip")))
objLI.SubItems.Add(strPhone)
objLI.SubItems.Add(ReplaceDBNull(objReader("CustID")))
lvwCustomer.Items.Add(objLI)
Loop
objReader.Close()
objConn.Close()
If lvwCustomer.Items.Count > 0 Then
lvwCustomer.Items(0).Selected = True
End If
End Sub
· The ListView's SelectedIndexChanged event is fired when the user clicks on a ListViewItem in the ListView. You'll see that a test is made to see if the count of SelectedItems is zero, in which case we want to exit this Sub. The reason for the test is that the SelectedIndexChange event will fire twice, once when the previous item is being deselected, then once when the new item is being selected. When the previous item is being deselected, the SelectedItems count will be zero, and we are not interested in that occurrence. When we get the "good" occurrence, a Sub called "PopulateCustomerDetail" is called, where we populate the textboxes in the "Current Record" area of the form with the data from the currently selected ListViewItem.
Private Sub lvwCustomers_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) _
Handles lvwCustomer.SelectedIndexChanged
If lvwCustomer.SelectedItems.Count = 0 Then Exit Sub
PopulateCustomerDetail()
End Sub
Private Sub PopulateCustomerDetail()
With lvwCustomer.SelectedItems(0)
txtFirst.Text = .Text
txtLast.Text = .SubItems(mintCUST_LAST_IDX).Text
txtAddr.Text = .SubItems(mintCUST_ADDR_IDX).Text
txtCity.Text = .SubItems(mintCUST_CITY_IDX).Text
txtState.Text = .SubItems(mintCUST_ST_IDX).Text
txtZip.Text = .SubItems(mintCUST_ZIP_IDX).Text
If .SubItems(mintCUST_PHONE_IDX).Text = "" Then
txtArea.Text = ""
txtPrfx.Text = ""
txtLine.Text = ""
Else
txtArea.Text = Mid(.SubItems(mintCUST_PHONE_IDX).Text, 2, 3)
txtPrfx.Text = Mid(.SubItems(mintCUST_PHONE_IDX).Text, 7, 3)
txtLine.Text = Strings.Right(.SubItems(mintCUST_PHONE_IDX).Text, 4)
End If
End With
End Sub
· The ListView's ColumnClick event is fired when the user clicks on one of the ListView's column headers. What we want to do here is to sort the ListView by the column that was clicked (and if you click the same column twice in a row, the sorting of that column will toggle from ascending to descending). Sorting a ListView on a particular column is more complicated in VB.NET / VB.20XX compared to classic VB (VB6 and earlier). The sorting code is part of this project and you are welcome to peruse it; however, it will not be examined in this article.
Private Sub lvwCustomer_ColumnClick(ByVal sender As Object, ByVal e As System.Windows.Forms.ColumnClickEventArgs) _
Handles lvwCustomer.ColumnClick
SortListView(lvwCustomer, e.Column)
End Sub
· Next, we will look at the ListView's role in cmdSave_Click event. We looked at this Sub earlier to examine the ADO.NET/database aspects of the code; now we will look at it to examine the ListView aspects of the code.
A ListViewItem object variable, objNewListViewItem, is declared at the local level. This will be used on the "ADD" side (i.e., if we are saving a newly added record). On the "ADD" side, after we have set up the SQL to do an INSERT, we use the Add method of the ListViewItems collection to add a new ListViewItem. The reference to the new ListViewItem is assigned to the objNewListViewItem variable. Note that the first name that has been entered into the txtFirst textbox is used for the Text property of the newly added item. Also, we add seven "placeholder" SubItems. The next statement calls the programmer-defined Sub PopulateListViewItem, passing it the newly added ListViewItem as an argument (the code for PopulateListViewItem will be shown a little later below, but all it does is assign the values of the textboxes used in the add or update to the corresponding SubItems of the ListViewItem object passed in to the Sub). Following this, the new Customer ID (that was established when GetNextRecordID was called) is assigned to the corresponding SubItem of the new ListViewItem. The EnsureVisible method is invoked to ensure that the user can see the newly added item. The Selected property of the newly added item is then set to True.
On the "UPDATE" side, our job is to modify the SubItem values of the currently selected item to reflect the user's changes. Here, we get the value for the intIDField variable from the "hidden" Cust ID column of the currently selected ListViewItem of the ListView. This variable is then used in the building of the UPDATE SQL statement. We then set the Text property of the currently selected item to the first name entered in the txtFirst textbox, and then we call the PopulateListViewItem Sub, passing the currently selected ListViewItem.
The code for the "cmdSave_Click" event procedure is shown below, with the ListView-related code in bold:
Private Sub btnSave_Click(sender As System.Object, e As System.EventArgs) Handles btnSave.Click
Dim strPhone As String
Dim objNewListViewItem As ListViewItem
Dim intIDField As Integer
Dim strSQL As String
If Not ValidateFormFields Then Exit Sub
strPhone = txtArea.Text & txtPrfx.Text & txtLine.Text
If mstrMaintMode = "ADD" Then
intIDField = GetNextRecordID("Customer", "CustID")
strSQL = "INSERT INTO Customer( CustID"
strSQL &= " , FirstName"
strSQL &= " , LastName"
strSQL &= " , Address"
strSQL &= " , City"
strSQL &= " , State"
strSQL &= " , Zip"
strSQL &= " , PhoneNumber"
strSQL &= ") VALUES ("
strSQL &= intIDField
strSQL &= ", '" & Replace(txtFirst.Text, "'", "''") & "'"
strSQL &= ", '" & Replace(txtLast.Text, "'", "''") & "'"
strSQL &= ", '" & Replace(txtAddr.Text, "'", "''") & "'"
strSQL &= ", '" & Replace(txtCity.Text, "'", "''") & "'"
strSQL &= ", '" & txtState.Text & "'"
strSQL &= ", '" & txtZip.Text & "'"
strSQL &= ", '" & strPhone & "'"
strSQL &= ")"
objNewListViewItem = lvwCustomer.Items.Add(txtFirst.Text, "Custs")
For intX As Integer = 1 To 7
objNewListViewItem.SubItems.Add("")
Next
PopulateListViewItem(objNewListViewItem)
With objNewListViewItem
.SubItems(mintCUST_ID_IDX).Text = CStr(intIDField)
.EnsureVisible()
End With
objNewListViewItem.Selected = True
Else
intIDField = CInt(lvwCustomer.SelectedItems(0).SubItems(mintCUST_ID_IDX).Text)
strSQL = "UPDATE Customer SET "
strSQL &= " FirstName = '" & Replace(txtFirst.Text, "'", "''") & "'"
strSQL &= ", LastName = '" & Replace(txtLast.Text, "'", "''") & "'"
strSQL &= ", Address = '" & Replace(txtAddr.Text, "'", "''") & "'"
strSQL &= ", City = '" & Replace(txtCity.Text, "'", "''") & "'"
strSQL &= ", State = '" & txtState.Text & "'"
strSQL &= ", Zip = '" & txtZip.Text & "'"
strSQL &= ", PhoneNumber = '" & strPhone & "'"
strSQL &= " WHERE CustID = " & intIDField
lvwCustomer.SelectedItems(0).Text = txtFirst.Text
PopulateListViewItem(lvwCustomer.SelectedItems(0))
End If
Dim objConn As New OleDbConnection(DB_CONN_STRING)
Dim objCommand As New OleDbCommand(strSQL, objConn)
objCommand.CommandType = CommandType.Text
objCommand.Connection.Open()
objCommand.ExecuteNonQuery()
objConn.Close()
SetFormState(True)
mblnUpdateInProgress = False
End Sub
· The code for the programmer-defined Sub "PopulateListViewItem" (discussed above) is shown below:
Private Sub PopulateListViewItem(ByVal pobjListViewItem As ListViewItem)
With pobjListViewItem
.SubItems(mintCUST_LAST_IDX).Text = txtLast.Text
.SubItems(mintCUST_ADDR_IDX).Text = txtAddr.Text
.SubItems(mintCUST_CITY_IDX).Text = txtCity.Text
.SubItems(mintCUST_ST_IDX).Text = txtState.Text
.SubItems(mintCUST_ZIP_IDX).Text = txtZip.Text
.SubItems(mintCUST_PHONE_IDX).Text _
= IIf(txtArea.Text = "", _
"", _
"(" & txtArea.Text & ") " & txtPrfx.Text & "-" & txtLine.Text)
End With
End Sub
· The last bit of ListView-related code we will look at is in the cmdDelete_Click event procedure. In the first section of highlighted code, we use the SelectedItem to get the first and last name of the customer to be deleted so that we can present the message "Are you sure that you want to delete Customer so-and-so?"; we also get the Cust ID for use in building the SQL string. Provided that the user goes ahead with the delete, we must refresh the ListView accordingly: we are going to remove the currently selected item, and we also want to establish a "new" selected item (we will store the index of the new selected item in the variable intNewSelIndex). We first test to see if the item to be deleted is the last item of the ListView – if it is, then the "new" selected item's index will be 2 less than the current count of listview items – otherwise the "new" selected item's index will be the same as the currently selected item's index (for example, if you have five items and you delete the third one, the item that was previously fourth will now be third). The selected item is then removed with the Remove method. Provided that we did not delete the very last record of the table, we use the value of the intNewSelIndex variable to set the "new" selected item. Setting the Selected property of the new item to True should cause the SelectedIndexChanged event to fire, which in turn will cause the textboxes to be populated with the data from the newly selected item.
Note: You will notice that the syntax SelectedItems(0) is used to refer to the selected item. This is because the ListView allows multiple items to be selected, and the selected items are part of the SelectedItems collection. However, we are not allowing multiple items to be selected (we set the MultiSelect property to False in design mode) - therefore, in our case, "SelectedItems(0)" will always refer to the one-and-only selected item.
Private Sub btnDelete_Click(sender As System.Object, e As System.EventArgs) Handles btnDelete.Click
Dim strFirstName As String
Dim strLastName As String
Dim intCustID As Integer
Dim intNewSelIndex As Integer
If lvwCustomer.SelectedItems.Count = 0 Then
MsgBox("No Customer selected to delete.", _
vbExclamation, _
"Delete")
Exit Sub
End If
With lvwCustomer.SelectedItems(0)
strFirstName = .Text
strLastName = .SubItems(mintCUST_LAST_IDX).Text
intCustID = CInt(.SubItems(mintCUST_ID_IDX).Text)
End With
If MsgBox("Are you sure that you want to delete Customer '" _
& strFirstName & " " & strLastName & "'?", _
vbYesNo + vbQuestion, _
"Confirm Delete") = vbNo Then
Exit Sub
End If
Dim strSQL As String = "DELETE FROM Customer WHERE CustID = " & intCustID
Dim objConn As New OleDbConnection(DB_CONN_STRING)
Dim objCommand As New OleDbCommand(strSQL, objConn)
objCommand.CommandType = CommandType.Text
objCommand.Connection.Open()
objCommand.ExecuteNonQuery()
objConn.Close()
With lvwCustomer
If .Items(.Items.Count - 1).Selected Then
intNewSelIndex = .Items.Count - 2
Else
intNewSelIndex = .SelectedItems(0).Index
End If
.SelectedItems(0).Remove()
If .Items.Count > 0 Then
.Items(intNewSelIndex).Selected = True
Else
ClearCurrRecControls()
End If
End With
End Sub
Download the project files for this sample application here.