troubleshooting Question

UPDATING SQL EXPRESS DATABASE STOPS WORKING, PROGRAM WRITTEN IN VB.NET 2010

Avatar of Poppygb
PoppygbFlag for United States of America asked on
.NET ProgrammingVisual Basic.NET
3 Comments1 Solution403 ViewsLast Modified:
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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 3 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros