Insert works once and update not at all

General
1

I have a program I wrote for my girlfriend and for the most part everything works. But for some reason I am having an issue with the following code.

  1. I can only insert 1 record. If I go to insert a second record it won’t do it. I have to close the form and re-open in order to insert another record.

  2. Update doesn’t. I am using the exact same code format in other areas and it works fine. I’m at my wits end trying to figure this one out.

' ----> Variables <----
Var dbRow As New DatabaseRow // For adding...
Var rsData As RowSet // For updating...

' ----> Preliminary Code <----

' ----> Main Code <----
If( btnAddUpd.Caption = "Add Bird" ) Then
  // This adds the new bird...
  dbRow.Column( "bDate" ).StringValue = txtBDate.Text
  dbRow.Column( "breed" ).StringValue = cboBreed.Text
  'dbRow.Column( "dDate" ).StringValue = txtDead.Text
  dbRow.Column( "gender" ).StringValue = txtGender.Text
  dbRow.Column( "health" ).StringValue = txtHealth.Text
  dbRow.Column( "name" ).StringValue = txtName.Text
  dbRow.Column( "notes" ).StringValue = txtNotes.Text
  dbRow.Column( "iDate" ).StringValue = txtSicko.Text
  dbRow.Column( "status" ).StringValue = "Alive"
  dbRow.Column( "legBandLeft" ).StringValue = cboBandLeft.Text
  dbRow.Column( "legBandRight" ).StringValue = cboBandRight.Text
  
  // Now to load the record into the database...
  Try
    dbFinances.AddRow( "tblBirdList", dbRow )
    'MessageBox( "Enter success message here." ) // Uncomment when wanted or needed...
  Catch e As DatabaseException
    MessageBox e.Message
  End Try
Else
  // Update record...
  If (dbFinances.Connect) = True Then
    rsData = dbFinances.SelectSQL( "SELECT * FROM tblBirdList WHERE rNum=?", txtRnum.Text  )
    If( rsData ) = Nil Then
      Beep
      MsgBox "Record Set Error: " + Str( dbFinances.ErrorCode) + EndOfLine + EndOfLine + dbFinances.ErrorMessage _
      + EndOfLine + EndOfLine + "Error generated from: frmBirds.btnAddUpd."
      Exit
    Else
      Try
        rsData.EditRow
        rsData.Column( "bDate" ).StringValue = txtBDate.Text
        rsData.Column( "breed" ).StringValue = cboBreed.Text
        rsData.Column( "dDate" ).StringValue = txtDead.Text
        rsData.Column( "gender" ).StringValue = txtGender.Text
        rsData.Column( "health" ).StringValue = txtHealth.Text
        rsData.Column( "name" ).StringValue = txtName.Text
        rsData.Column( "notes" ).StringValue = txtNotes.Text + EndOfLine.UNIX + "Record Updated " + modSystem.mthDate( ) + " at time : " + modSystem.mthTime( ) + "."
        rsData.Column( "iDate" ).StringValue = txtSicko.Text
        rsData.Column( "status" ).StringValue = txtStatus.Text
        dbRow.Column( "legBandLeft" ).StringValue = cboBandLeft.Text
        dbRow.Column( "legBandRight" ).StringValue = cboBandRight.Text
        rsData.SaveRow
      Catch
        MessageBox( "Oh crap! UPDATE didn't update..." )
      End Try
    End If
  End If
  Me.Caption = "Add Bird"
  rsData.Close
  
  // Left leg band...
  If( cboBandLeft.Text <> "" ) Then
    // Now  I have to update the leg band table and "remove" the leg band from the selection process...
    rsData = dbFinances.SelectSQL( "SELECT * FROM tblLegBands WHERE bandNumber=?", cboBandLeft.Text  )
    If( rsData ) = Nil Then
      Beep
      MsgBox "Record Set Error: " + Str( dbFinances.ErrorCode) + EndOfLine + EndOfLine + dbFinances.ErrorMessage _
      + EndOfLine + EndOfLine + "Error generated from: frmBirds.btnAddUpd."
      Exit
    Else
      Try
        rsData.EditRow
        rsData.Column( "assigned" ).StringValue = "Y"
        rsData.SaveRow
      Catch
        MessageBox( "Oh crap! UPDATE didn't update..." )
      End Try
    End If
    txtBDate.SetFocus
    rsData.Close
  End If
  // Right leg band...
  If( cboBandRight.Text <> "" ) Then
    // Now  I have to update the leg band table and "remove" the leg band from the selection process...
    rsData = dbFinances.SelectSQL( "SELECT * FROM tblLegBands WHERE bandNumber=?", cboBandRight.Text  )
    If( rsData ) = Nil Then
      Beep
      MsgBox "Record Set Error: " + Str( dbFinances.ErrorCode) + EndOfLine + EndOfLine + dbFinances.ErrorMessage _
      + EndOfLine + EndOfLine + "Error generated from: frmBirds.btnAddUpd."
      Exit
    Else
      Try
        rsData.EditRow
        rsData.Column( "assigned" ).StringValue = "Y"
        rsData.SaveRow
      Catch
        MessageBox( "Oh crap! UPDATE didn't update..." )
      End Try
    End If
    txtBDate.SetFocus
    rsData.Close
  End If
  
  If( txtHealthNotes.Visible )Then
    If( txtHealthNotes.Text <> "" ) Then
      dbRow.Column( "iDate" ).StringValue = txtSicko.Text
      dbRow.Column( "entryDate" ).StringValue = modSystem.mthDate( )
      dbRow.Column( "legBand" ).StringValue = cboBandLeft.Text
      dbRow.Column( "notes" ).StringValue = txtHealthNotes.Text
      dbRow.Column( "status" ).StringValue = txtStatus.Text
      dbRow.Column( "health" ).StringValue = txtHealth.Text
      
      // Now to load the record into the database...
      Try
        dbFinances.AddRow( "tblBirdHealth", dbRow )
        'MessageBox( "Enter success message here." ) // Uncomment when wanted or needed...
      Catch e As DatabaseException
        MessageBox e.Message
      End Try
    End If
  End If
End If

frmBirds.mthClear( )
frmBirds.mthLoadBirdsLstBx( )
2

For the 1. Issue, maybe you need to re-initialize your (global?) dbRow?
Try the following:

If( btnAddUpd.Caption = "Add Bird" ) Then
  // This adds the new bird...
  dbRow = New DatabaseRow
  dbRow.Column( "bDate" ).StringValue = txtBDate.Text
  dbRow.Column( "breed" ).StringValue = cboBreed.Text

In your Update section and for issue 2., i see:

        rsData.Column( "status" ).StringValue = txtStatus.Text
        dbRow.Column( "legBandLeft" ).StringValue = cboBandLeft.Text
        dbRow.Column( "legBandRight" ).StringValue = cboBandRight.Text
        rsData.SaveRow

shouldn’t this be more like:

        rsData.Column( "status" ).StringValue = txtStatus.Text
        rsData.Column( "legBandLeft" ).StringValue = cboBandLeft.Text
        rsData.Column( "legBandRight" ).StringValue = cboBandRight.Text
        rsData.SaveRow
3

For update,

rsData = dbFinances.SelectSQL( "SELECT * FROM tblBirdList WHERE rNum=?", txtRnum.Text  )
If( rsData ) = Nil Then

rsData will never be Nil. You will get an exception instead. Wrap the select and update code in a Try/Catch.

What you should check is Not rsData.AfterLastRow That would indicate that there were no errors and your SQL statement was well formed, but no rows matched your query.

4

There are many other things in this code that could be improved differently. However, I thought I’d focus on solving its problems. Unfortunately, I don’t have the time or ability to recreate the code in the editor right now. :slight_smile:

5

I was addressing problem 2, where it doesn’t update an existing record. The current code wil always drop into the Else clause, even if it did not find a record.

1 Like
7

Thanks, I missed that and just finally saw what you were pointing out… man, I need to check my glasses again!

1 Like