I have written a program in vb.net 2010 and using sql server express 2008.
I am going nuts trying to figure out why my database tables are not updating. Here is what’s happening.
When I add a record to my RIDERS table. It will let me modify the record several times, and then it stops updating. I get no error messages from SQL server or from vb.net.
So this is what I have done.
A. Add a new record only creating the record with primary Id. This gives me a new record with only one field filled (Primary ID)
B. Then I started modifying the new record one field at a time. Total fields 26. Then it just stops letting me update the record as stated above no errors. Sometime It will stop updating before I get all fields filled.
C. Now I have 9 records and it will not let me update any records. Now if I drop and create my riders table, and of course I now have no records. I can now enter new records and modify them until it just stops letting me update.
D. I can go into the sql database and manually update the records with no problem.
E. I have submitted this problem last week along with the code. AngleIII helped me
on this.
F. It’s almost like SQL has a lock on the modifying records after so many edits.
G. PLEASE HELP I have been working on this for 2 weeks now.
Public Shared Function UpdateRiders(ByVal oldRider As Riders,
ByVal newRider As Riders) As Boolean
Dim connection As SqlConnection = dctsConnect.GetConnection
Dim updateStatement As String =
"UPDATE RIDERS SET " &
"L_NAME = @NewL_NAME, " &
"F_NAME = @NewF_NAME, " &
"M_NAME = @NewM_NAME, " &
"UP_DATE = @NewUP_DATE, " &
"ADDRESS = @NewADDRESS, " &
"CITY = @NewCITY, " &
"ST = @NewST, " &
"ZIP = @NewZIP, " &
"PHONE = @NewPHONE, " &
"COUNTY = @NewCOUNTY, " &
"ALT_PHONE = @NewALT_PHONE, " &
"DOB = @NewDOB, " &
"AGE = @NewAGE, " &
"CAP = @NewCAP, " &
"CD = @NewCD, " &
"DCN = @NewDCN, " &
"LIFT = @NewLIFT, " &
"SSAN = @NewSSAN, " &
"LOCATION = @NewLOCATION, " &
"AREA = @NewAREA, " &
"HSE_NO = @NewHSE_NO, " &
"SEX = @NewSEX, " &
"RACE = @NewRACE, " &
"EMER_CONT = @NewEMER_CONT, " &
"EMER_PHONE = @NewEMER_PHONE, " &
"COMMENTS = @NewCOMMENTS " &
"WHERE RIDER_NO = @OldRIDER_NO " &
"AND ((L_NAME = @OldL_NAME) OR (L_NAME IS NULL AND @OldL_NAME IS NULL)) " &
"AND ((F_NAME = @OldF_NAME) OR (F_NAME IS NULL AND @OldF_NAME IS NULL)) " &
"AND ((M_NAME = @OldM_NAME) OR (M_NAME IS NULL AND @OldM_NAME IS NULL)) " &
"AND ((UP_DATE = @OldUP_DATE) OR (UP_DATE IS NULL AND @OldUP_DATE IS NULL)) " &
"AND ((ADDRESS = @OldADDRESS) OR (ADDRESS IS NULL AND @OldADDRESS IS NULL)) " &
"AND ((CITY = @OldCITY) OR (CITY IS NULL AND @OldCITY IS NULL)) " &
"AND ((ST = @OldST) OR (ST IS NULL AND @OldST IS NULL)) " &
"AND ((ZIP = @OldZIP) OR (ZIP IS NULL AND @OldZIP IS NULL)) " &
"AND ((COUNTY = @OldCOUNTY) OR (COUNTY IS NULL AND @OldCOUNTY IS NULL)) " &
"AND ((PHONE = @OldPHONE) OR (PHONE IS NULL AND @OldPHONE IS NULL)) " &
"AND ((ALT_PHONE = @OldALT_PHONE) OR (ALT_PHONE IS NULL AND @OldALT_PHONE IS NULL)) " &
"AND ((DOB = @OldDOB) OR (DOB IS NULL AND @OldDOB IS NULL)) " &
"AND ((AGE = @OldAGE) OR (AGE IS NULL AND @OldAGE IS NULL)) " &
"AND ((CAP = @OldCAP) OR (CAP IS NULL AND @OldCAP IS NULL)) " &
"AND ((CD = @OldCD) OR (CD IS NULL AND @OldCD IS NULL)) " &
"AND ((DCN = @OldDCN) OR (DCN IS NULL AND @OldDCN IS NULL))" &
"AND ((LIFT = @OldLIFT) OR (LIFT IS NULL AND @OldLIFT IS NULL)) " &
"AND ((LOCATION = @OldLOCATION) OR (LOCATION IS NULL AND @OldLOCATION IS NULL)) " &
"AND ((AREA = @OldAREA) OR (AREA IS NULL AND @OldAREA IS NULL)) " &
"AND ((HSE_NO = @OldHSE_NO) OR (HSE_NO IS NULL AND @OldHSE_NO IS NULL)) " &
"AND ((SEX = @OldSEX) OR (SEX IS NULL AND @OldSEX IS NULL)) " &
"AND ((ACE = @OldRACE) OR (RACE IS NULL AND @OldRACE IS NULL)) " &
"AND ((EMER_CONT = @OldEMER_CONT) OR (EMER_CONT IS NULL AND @OldEMER_CONT IS NULL)) " &
"AND ((EMER_PHONE = @OldEMER_PHONE) OR (EMER_PHONE IS NULL AND @OldEMER_PHONE IS NULL)) " &
"AND ((COMMENTS = @OldCOMMENTS) OR (COMMENTS IS NULL AND @OldCOMMENTS IS NULL))"
Dim updateCommand As New SqlCommand(updateStatement, connection)
If newRider.L_NAME = "" Then
updateCommand.Parameters.AddWithValue("@NewL_NAME", DBNull.Value)
Else
updateCommand.Parameters.AddWithValue("@NewL_NAME", newRider.L_NAME)
End If
If newRider.F_NAME = "" Then
updateCommand.Parameters.AddWithValue("@NewF_NAME", DBNull.Value)
Else
updateCommand.Parameters.AddWithValue("@NewF_NAME", newRider.F_NAME)
End If
If newRider.M_NAME = "" Then
updateCommand.Parameters.AddWithValue("@NewM_NAME", DBNull.Value)
Else
updateCommand.Parameters.AddWithValue("@NewM_NAME", newRider.M_NAME)
End If
If IsDate(newRider.UP_DATE) Then
updateCommand.Parameters.AddWithValue("@NewUP_DATE", newRider.UP_DATE)
Else
updateCommand.Parameters.AddWithValue("@NewUP_DATE", DBNull.Value)
End If
If newRider.ADDRESS = "" Then
updateCommand.Parameters.AddWithValue("@NewADDRESS", DBNull.Value)
Else
updateCommand.Parameters.AddWithValue("@NewADDRESS", newRider.ADDRESS)
End If
If newRider.CITY = "" Then
updateCommand.Parameters.AddWithValue("@NewCITY", DBNull.Value)
Else
updateCommand.Parameters.AddWithValue("@NewCITY", newRider.CITY)
End If
If newRider.ST = "" Then
updateCommand.Parameters.AddWithValue("@NewST", DBNull.Value)
Else
updateCommand.Parameters.AddWithValue("@NewST", newRider.ST)
End If
If newRider.ZIP = "" Then
updateCommand.Parameters.AddWithValue("@NewZIP", DBNull.Value)
Else
updateCommand.Parameters.AddWithValue("@NewZIP", newRider.ZIP)
End If
If newRider.COUNTY = "" Then
updateCommand.Parameters.AddWithValue("@NewCOUNTY", DBNull.Value)
Else
updateCommand.Parameters.AddWithValue("@NewCOUNTY", newRider.COUNTY)
End If
If newRider.PHONE = "" Then
updateCommand.Parameters.AddWithValue("@NewPHONE", DBNull.Value)
Else
updateCommand.Parameters.AddWithValue("@NewPHONE", newRider.PHONE)
End If
If newRider.ALT_PHONE = "" Then
updateCommand.Parameters.AddWithValue("@NewALT_PHONE", DBNull.Value)
Else
updateCommand.Parameters.AddWithValue("@NewALT_PHONE", newRider.ALT_PHONE)
End If
If IsDate(newRider.DOB) Then
updateCommand.Parameters.AddWithValue("@NewDOB", newRider.DOB)
Else
updateCommand.Parameters.AddWithValue("@NewDOB", DBNull.Value)
End If
If newRider.AGE = 0 Then
updateCommand.Parameters.AddWithValue("@NewAGE", DBNull.Value)
Else
updateCommand.Parameters.AddWithValue("@NewAGE", newRider.AGE)
End If
If newRider.CAP = "" Then
updateCommand.Parameters.AddWithValue("@NewCAP", DBNull.Value)
Else
updateCommand.Parameters.AddWithValue("@NewCAP", newRider.CAP)
End If
If newRider.CD = "" Then
updateCommand.Parameters.AddWithValue("@NewCD", DBNull.Value)
Else
updateCommand.Parameters.AddWithValue("@NewCD", newRider.CD)
End If
If newRider.LIFT = "" Then
updateCommand.Parameters.AddWithValue("@NewLIFT", DBNull.Value)
Else
updateCommand.Parameters.AddWithValue("@NewLIFT", newRider.LIFT)
End If
If newRider.DCN = "" Then
updateCommand.Parameters.AddWithValue("@NewDCN", DBNull.Value)
Else
updateCommand.Parameters.AddWithValue("@NewDCN", newRider.DCN)
End If
If newRider.SSAN = "" Then
updateCommand.Parameters.AddWithValue("@NewSSAN", DBNull.Value)
Else
updateCommand.Parameters.AddWithValue("@NewSSAN", newRider.SSAN)
End If
If newRider.AREA = "" Then
updateCommand.Parameters.AddWithValue("@NewAREA", DBNull.Value)
Else
updateCommand.Parameters.AddWithValue("@NewAREA", newRider.AREA)
End If
If newRider.LOCATION = "" Then
updateCommand.Parameters.AddWithValue("@NewLOCATION", DBNull.Value)
Else
updateCommand.Parameters.AddWithValue("@NewLOCATION", newRider.LOCATION)
End If
If newRider.HSE_NO = 0 Then
updateCommand.Parameters.AddWithValue("@NewHSE_NO", DBNull.Value)
Else
updateCommand.Parameters.AddWithValue("@NewHSE_NO", newRider.HSE_NO)
End If
If newRider.SEX = "" Then
updateCommand.Parameters.AddWithValue("@NewSEX", DBNull.Value)
Else
updateCommand.Parameters.AddWithValue("@NewSEX", newRider.SEX)
End If
If newRider.RACE = "" Then
updateCommand.Parameters.AddWithValue("@NewRACE", DBNull.Value)
Else
updateCommand.Parameters.AddWithValue("@NewRACE", newRider.RACE)
End If
If newRider.EMER_CONT = "" Then
updateCommand.Parameters.AddWithValue("@NewEMER_CONT", DBNull.Value)
Else
updateCommand.Parameters.AddWithValue("@NewEMER_CONT", newRider.EMER_CONT)
End If
If newRider.EMER_PHONE = "" Then
updateCommand.Parameters.AddWithValue("@NewEMER_PHONE", DBNull.Value)
Else
updateCommand.Parameters.AddWithValue("@NewEMER_PHONE", newRider.EMER_PHONE)
End If
If newRider.COMMENTS = "" Then
updateCommand.Parameters.AddWithValue("@NewCOMMENTS", DBNull.Value)
Else
updateCommand.Parameters.AddWithValue("@NewCOMMENTS", newRider.COMMENTS)
End If
''''' OldRider
updateCommand.Parameters.AddWithValue("@OldRIDER_NO", oldRider.RIDER_NO)
If oldRider.L_NAME = "" Then
updateCommand.Parameters.AddWithValue("@OldL_NAME", DBNull.Value)
Else
updateCommand.Parameters.AddWithValue("@OldL_NAME", oldRider.L_NAME)
End If
If oldRider.F_NAME = "" Then
updateCommand.Parameters.AddWithValue("@OldF_NAME", DBNull.Value)
Else
updateCommand.Parameters.AddWithValue("@OldF_NAME", oldRider.F_NAME)
End If
If oldRider.M_NAME = "" Then
updateCommand.Parameters.AddWithValue("@OldM_NAME", DBNull.Value)
Else
updateCommand.Parameters.AddWithValue("@OldM_NAME", oldRider.M_NAME)
End If
If IsDate(oldRider.UP_DATE) Then
'If oldRider.UP_DATE = "" Then
updateCommand.Parameters.AddWithValue("@OldUP_DATE", oldRider.UP_DATE)
Else
updateCommand.Parameters.AddWithValue("@OldUP_DATE", DBNull.Value)
End If
If oldRider.ADDRESS = "" Then
updateCommand.Parameters.AddWithValue("@OldADDRESS", DBNull.Value)
Else
updateCommand.Parameters.AddWithValue("@OldADDRESS", oldRider.ADDRESS)
End If
If oldRider.CITY = "" Then
updateCommand.Parameters.AddWithValue("@OldCITY", DBNull.Value)
Else
updateCommand.Parameters.AddWithValue("@OldCITY", oldRider.CITY)
End If
If oldRider.ST = "" Then
updateCommand.Parameters.AddWithValue("@OldST", DBNull.Value)
Else
updateCommand.Parameters.AddWithValue("@OldST", oldRider.ST)
End If
If oldRider.ZIP = "" Then
updateCommand.Parameters.AddWithValue("@OldZIP", DBNull.Value)
Else
updateCommand.Parameters.AddWithValue("@OldZIP", oldRider.ZIP)
End If
If oldRider.COUNTY = "" Then
updateCommand.Parameters.AddWithValue("@OldCOUNTY", DBNull.Value)
Else
updateCommand.Parameters.AddWithValue("@OldCOUNTY", oldRider.COUNTY)
End If
If oldRider.PHONE = "" Then
updateCommand.Parameters.AddWithValue("@OldPHONE", DBNull.Value)
Else
updateCommand.Parameters.AddWithValue("@OldPHONE", oldRider.PHONE)
End If
If oldRider.ALT_PHONE = "" Then
updateCommand.Parameters.AddWithValue("@OldALT_PHONE", DBNull.Value)
Else
updateCommand.Parameters.AddWithValue("@OldALT_PHONE", oldRider.ALT_PHONE)
End If
' If IsDate(oldRider.DOB) Then
If oldRider.DOB = "" Then
updateCommand.Parameters.AddWithValue("@OldDOB", DBNull.Value)
Else
updateCommand.Parameters.AddWithValue("@OldDOB", oldRider.DOB)
End If
If oldRider.AGE = 0 Then
updateCommand.Parameters.AddWithValue("@OldAGE", DBNull.Value)
Else
updateCommand.Parameters.AddWithValue("@OldAGE", oldRider.AGE)
End If
If oldRider.CAP = "" Then
updateCommand.Parameters.AddWithValue("@OldCAP", DBNull.Value)
Else
updateCommand.Parameters.AddWithValue("@OldCAP", oldRider.CAP)
End If
If oldRider.CD = "" Then
updateCommand.Parameters.AddWithValue("@OldCD", DBNull.Value)
Else
updateCommand.Parameters.AddWithValue("@OldCD", oldRider.CD)
End If
If oldRider.LIFT = "" Then
updateCommand.Parameters.AddWithValue("@OldLIFT", DBNull.Value)
Else
updateCommand.Parameters.AddWithValue("@OldLIFT", oldRider.LIFT)
End If
If oldRider.DCN = "" Then
updateCommand.Parameters.AddWithValue("@OldDCN", DBNull.Value)
Else
updateCommand.Parameters.AddWithValue("@OldDCN", oldRider.DCN)
End If
If oldRider.SSAN = "" Then
updateCommand.Parameters.AddWithValue("@OldSSAN", DBNull.Value)
Else
updateCommand.Parameters.AddWithValue("@OldSSAN", oldRider.SSAN)
End If
If oldRider.LOCATION = "" Then
updateCommand.Parameters.AddWithValue("@OldLOCATION", DBNull.Value)
Else
updateCommand.Parameters.AddWithValue("@OldLOCATION", oldRider.LOCATION)
End If
If oldRider.AREA = "" Then
updateCommand.Parameters.AddWithValue("@OldAREA", DBNull.Value)
Else
updateCommand.Parameters.AddWithValue("@OldAREA", oldRider.AREA)
End If
If oldRider.HSE_NO = 0 Then
updateCommand.Parameters.AddWithValue("@OldHSE_NO", DBNull.Value)
Else
updateCommand.Parameters.AddWithValue("@OldHSE_NO", oldRider.HSE_NO)
End If
If oldRider.SEX = "" Then
updateCommand.Parameters.AddWithValue("@OldSEX", DBNull.Value)
Else
updateCommand.Parameters.AddWithValue("@OldSEX", oldRider.SEX)
End If
If oldRider.RACE = "" Then
updateCommand.Parameters.AddWithValue("@OldRACE", DBNull.Value)
Else
updateCommand.Parameters.AddWithValue("@OldRACE", oldRider.RACE)
End If
If oldRider.EMER_CONT = "" Then
updateCommand.Parameters.AddWithValue("@OldEMER_CONT", DBNull.Value)
Else
updateCommand.Parameters.AddWithValue("@OldEMER_CONT", oldRider.EMER_CONT)
End If
If oldRider.EMER_PHONE = "" Then
updateCommand.Parameters.AddWithValue("@OldEMER_PHONE", DBNull.Value)
Else
updateCommand.Parameters.AddWithValue("@OldEMER_PHONE", oldRider.EMER_PHONE)
End If
If oldRider.COMMENTS = "" Then
updateCommand.Parameters.AddWithValue("@OldCOMMENTS", DBNull.Value)
Else
updateCommand.Parameters.AddWithValue("@OldCOMMENTS", oldRider.COMMENTS)
End If
Try
connection.Open()
Dim count As Integer = updateCommand.ExecuteNonQuery
If count > 0 Then
Return True
Else
Return False
End If
Catch ex As SqlException
Throw ex
Finally
connection.Close()
End Try
End Function
End Class
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Most Valuable Expert award recognizes technology experts who passionately share their knowledge with the community, demonstrate the core values of this platform, and go the extra mile in all aspects of their contributions. This award is based off of nominations by EE users and experts. Multiple MVEs may be awarded each year.
The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.