I’m relatively new to Visual Basic .NET programming having worked with several 3GL languages over the last 20 years. I accepted employment with a homeless shelter for men, and for women with children (separate divisions) with the goal of developing SQL Server database applications for each division.
I’ve come across an issue within my VB database application and am wondering if anyone might have any ideas on what I might be doing wrong. The issue is that while the great majority (approximately 95% - 98%) of the updates (record creation or update) to the SQL Server database work just fine, the remaining updates do not get recorded in the database. The failure doesn’t appear to be user error, but may be related to how I’m creating and/or updating records in the database.
I’m attaching code to this post showing how the application creates a new or updates an existing record in the database. Basically, the user can find an existing Guest record (our homeless men and women are called Guests) by clicking a Find Guest Record button on the VB form. Once the record is located and selected it is loaded into the the fields on the form. If a change has been made in one of the fields the user clicks the Create / Update button to validate the input, clear the form and update the Guest record. To create a Guest record (if the Guest cannot be found in the database) the user enters data into the cleared form and clicks the Create / Update button to validate the input, clear the form and create the Guest record.
As you can see from the abbreviated source the application is either updating an existing row in the DataSet or creating a new row in the DataSet. The DataSet is then updated in the database. Perhaps I should be using the BEGIN TRANSACTION and COMMIT statements, but I haven’t seen examples of how these are used. If I need to use them could someone please direct me to some example code which does the same sort of thing? Thank you.
Please let me know if anyone has any ideas on this issue. Thank you in advance for your time and consideration.
Ed
I’ve come across an issue within my VB database application and am wondering if anyone might have any ideas on what I might be doing wrong. The issue is that while the great majority (approximately 95% - 98%) of the updates (record creation or update) to the SQL Server database work just fine, the remaining updates do not get recorded in the database. The failure doesn’t appear to be user error, but may be related to how I’m creating and/or updating records in the database.
I’m attaching code to this post showing how the application creates a new or updates an existing record in the database. Basically, the user can find an existing Guest record (our homeless men and women are called Guests) by clicking a Find Guest Record button on the VB form. Once the record is located and selected it is loaded into the the fields on the form. If a change has been made in one of the fields the user clicks the Create / Update button to validate the input, clear the form and update the Guest record. To create a Guest record (if the Guest cannot be found in the database) the user enters data into the cleared form and clicks the Create / Update button to validate the input, clear the form and create the Guest record.
As you can see from the abbreviated source the application is either updating an existing row in the DataSet or creating a new row in the DataSet. The DataSet is then updated in the database. Perhaps I should be using the BEGIN TRANSACTION and COMMIT statements, but I haven’t seen examples of how these are used. If I need to use them could someone please direct me to some example code which does the same sort of thing? Thank you.
Please let me know if anyone has any ideas on this issue. Thank you in advance for your time and consideration.
Ed
Code:
Private Sub btnCreateUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreateUpdate.Click
Dim blnWriteRecord As Boolean ' Write record flag
Dim intArrivedLate As Integer ' Arrived Late
Dim intBirthCountryID As Integer ' Birth Country ID
Dim intEducationID As Integer ' Education ID
Dim intEmergencyCountryID As Integer ' Emergency Country ID
Dim intEnterByStaffID As Integer ' Enter By Staff ID
Dim intEthnicID As Integer ' Ethnic ID
Dim intEyeColorID As Integer ' Eye Color ID
Dim intGuestStatusID As Integer ' Guest status
Dim intHairColorID As Integer ' Hair color
Dim intHealthID As Integer ' Health
Dim intIndex As Integer ' For loop index
Dim intIntegerKey As Integer ' Integer key
Dim intLowerBunk As Integer ' Lower bunk
Dim intMaritalID As Integer ' Marital ID
Dim intMentallyIll As Integer ' Mentally ill
Dim intOccupationID As Integer ' Occupation ID
Dim intReferredByID As Integer ' Referred By ID
Dim intRelationID As Integer ' Relation ID
Dim intReligionID As Integer ' Religion ID
Dim intRideElevator As Integer ' Ride elevator
Dim intUpdateByStaffID As Integer ' Update By Staff ID
Dim intVeteranStatus As Integer ' Veteran status
Dim strBirthStateID As String ' Birth State ID
Dim strEmergencyStateID As String ' Emergency State ID
Dim strFirstInitial As String ' First initial
Dim strFirstName As String ' First name
Dim strLastName As String ' Last name
Dim strStringKey As String ' String key
Dim strUpdate As String ' Update date
If (Validate_Data()) Then
' Check whether a Guest record has been selected. If a Guest record was selected the DataSet will have one record in it.
If (DataSet11.Tables("tblGuest").Rows.Count <= 0) Then
' Get the IDs for the values selected from the drop down list boxes.
' Load the EthnicID that matches the ethnic type.
strStringKey = cmbEthnic.Text
With DataSet11.tblEthnic
For intIndex = 0 To .Rows.Count - 1
If (.Rows(intIndex).Item("Ethnic") = strStringKey) Then
intEthnicID = .Rows(intIndex).Item("EthnicID")
Exit For
End If
Next
End With
' Load the EyeColorID that matches the eye type.
strStringKey = cmbEye.Text
With DataSet11.tblEyeColor
For intIndex = 0 To .Rows.Count - 1
If (.Rows(intIndex).Item("EyeColor") = strStringKey) Then
intEyeColorID = .Rows(intIndex).Item("EyeColorID")
Exit For
End If
Next
End With
' The remaining With / End With loops to get the remaining IDs have been deleted here to shorten the code listing.
Dim rowNewRow As DataRow ' New row in the DataSet for tblGuest.
rowNewRow = DataSet11.tblGuest.NewRow()
rowNewRow("EnterDate") = txtEntered.Text
rowNewRow("EnterByStaffID") = intEnterByStaffID
rowNewRow("LastName") = txtLastName.Text
rowNewRow("FirstName") = txtFirstName.Text
rowNewRow("MiddleInitial") = txtMI.Text
rowNewRow("AliasLastName") = txtALastName.Text
rowNewRow("AliasFirstName") = txtAFirstName.Text
rowNewRow("AliasMiddleInitial") = txtAMI.Text
rowNewRow("SSNumber") = txtSSN.Text
rowNewRow("BirthDate") = txtDOB.Text
rowNewRow("BirthCity") = txtBCity.Text
rowNewRow("BirthStateID") = strBirthStateID
rowNewRow("BirthCountryID") = intBirthCountryID
rowNewRow("EthnicID") = intEthnicID
rowNewRow("MaritalID") = intMaritalID
rowNewRow("Weight") = txtWeight.Text
rowNewRow("Height") = txtHeight.Text
rowNewRow("EyeColorID") = intEyeColorID
rowNewRow("HairColorID") = intHairColorID
rowNewRow("OccupationID") = intOccupationID
rowNewRow("ReferredByID") = intReferredByID
rowNewRow("HealthID") = intHealthID
rowNewRow("MentallyIllID") = intMentallyIll
rowNewRow("EmergencyName") = txtEName.Text
rowNewRow("EmergencyAddress") = txtEAddress.Text
rowNewRow("EmergencyCity") = txtECity.Text
rowNewRow("EmergencyStateID") = strEmergencyStateID
rowNewRow("EmergencyCountryID") = intEmergencyCountryID
rowNewRow("EmergencyZipCode") = txtEZip.Text
rowNewRow("EmergencyPhone") = txtEPhone.Text
rowNewRow("RelationID") = intRelationID
rowNewRow("EducationID") = intEducationID
rowNewRow("ReligionID") = intReligionID
rowNewRow("LivedInMilwaukeeDate") = txtLived.Text
rowNewRow("GuestStatusID") = intGuestStatusID
rowNewRow("VeteranStatusID") = intVeteranStatus
rowNewRow("UpdateDate") = strUpdate
rowNewRow("UpdateByStaffID") = intUpdateByStaffID
rowNewRow("NeedsLowerBunkID") = intLowerBunk
rowNewRow("NeedsToRideElevatorID") = intRideElevator
rowNewRow("ArrivedLateID") = intArrivedLate
DataSet11.tblGuest.Rows.Add(rowNewRow)
Try
' Open the database connection.
If (SqlConnection1.State = ConnectionState.Closed) Then
SqlConnection1.Open()
End If
' Update the database.
adpGuestSqlDataAdapter.Update(DataSet11.tblGuest)
' Close the database connection.
If (SqlConnection1.State = ConnectionState.Open) Then
SqlConnection1.Close()
End If
' Notify the user that the record has been added to the database.
MsgBox("The record has been added to the database.")
' Clear the Guest and associated tables.
Call Clear_Guest_And_Associated_Tables()
' Clear the Guest Registration input fields.
Call Clear_Input_Fields()
Catch ex As Exception
' Close the database connection.
If (SqlConnection1.State = ConnectionState.Open) Then
SqlConnection1.Close()
End If
MsgBox("The record couldn't be added for the following reason: " & ex.Message)
End Try
Else
' Check whether to update the record in the DataSet.
blnWriteRecord = False
' The the input fields against the copy of the input fields.
If (txtEntered.Text <> strCopyEntered) Then
DataSet11.tblGuest.Rows(0).Item("Entered") = txtEntered.Text
blnWriteRecord = True
End If
If (txtLastName.Text <> strCopyLastName) Then
DataSet11.tblGuest.Rows(0).Item("LastName") = txtLastName.Text
blnWriteRecord = True
End If
If (txtFirstName.Text <> strCopyFirstName) Then
DataSet11.tblGuest.Rows(0).Item("FirstName") = txtFirstName.Text
blnWriteRecord = True
End If
If (txtMI.Text <> strCopyMI) Then
DataSet11.tblGuest.Rows(0).Item("MiddleInitial") = txtMI.Text
blnWriteRecord = True
End If
' The other IF statements that check whether the input field values have been changed have been deleted here to shorten the code listing.
If (blnWriteRecord) Then
Try
' Open the database connection.
If (SqlConnection1.State = ConnectionState.Closed) Then
SqlConnection1.Open()
End If
' Update the record.
adpGuestSqlDataAdapter.Update(DataSet11.tblGuest)
' Close the database connection.
If (SqlConnection1.State = ConnectionState.Open) Then
SqlConnection1.Close()
End If
' Notify the user that the record has been updated in the database.
MsgBox("The record has been updated in the database.")
Catch ex As Exception
' Close the database connection.
If (SqlConnection1.State = ConnectionState.Open) Then
SqlConnection1.Close()
End If
MsgBox("The record couldn't be updated for the following reason: " & ex.Message)
End Try
End If
' Clear the Guest and associated tables.
Call Clear_Guest_And_Associated_Tables()
' Clear the Guest Registration input fields.
Call Clear_Input_Fields()
End If
End If
End Sub