CODEWITHSUNDEEP
vb.netsqlite
Vector
Vector

Reputation: 3235

Second Record in SQLite DB not updating

I have what is just an odd issue with VB.Net and SQLite and an Update procedure
Little background first I wanted to move some data from a Derby DB to the SQLite DB in a VB.Net project
So I had two records int the Netbeans Derby DB project dated Dec-03-2019 and Dec-30-2019
To use the date setting in the VB.Net project I set my computer date and time back to Dec 3 2019
With no records in the VB.Net SQLite DB project I only copy and pasted the TextBox multi line in the Netbeans project into the VB.Net project and saved the data the save worked as it should
Closed all open projects and move on to setting the date and time to Dec 30 2019 and repeted the above process great I now have two legacy Records
With all projects closed I set the computer back to today's date and time

Here is where it gets beyond my comprehension
 If I try to update the first record for Dec 3 2019 the update code works
If I try to update the second record for Dec 30 2019 the update FAILS
To be clear if I enter two records for September 2020 and try to update either one the Update code works even with different dates

Why is the second LEGACY record not updating?
You will notice a Global Variable gv_childInt the little MesBox confirms the correct value is being used
UPDATE CODE BELOW with commented out changes I have tried I even did WHERE CID = " & gv_childInt

    Private Sub btnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click

    If Len(tbTitle.Text.Trim) = 0 Then
        tbMessage.ForeColor = Color.Blue
        tbMessage.Text = "Enter the Title"
        tbTitle.Select()
        Return
    End If
    'If rtbEnter.Text.Trim().Length = 0 Then
    If tb.Text.Trim().Length = 0 Then
        tbMessage.ForeColor = Color.Red
        tbMessage.Text = "Enter Data to Update"
        'rtbEnter.Select()
        'Rewrite code for tb WPF below
        tb.Focus()
        Return

    End If

    MsgBox("Value CID " & gv_childInt)
    Using conn As New SQLiteConnection($"Data Source = '{gv_dbName}';Version=3;")
        conn.Open()
        Using cmd As New SQLiteCommand
            cmd.Connection = conn
            Try
                cmd.CommandText = $"UPDATE ChildTable SET cDispDate = '{tbDispDate.Text.Trim}', cMonth = '{tbMonth.Text.Trim}',cYear = '{tbYear.Text.Trim}',cTitle = '{tbTitle.Text.Trim}', cEntry = '{tb.Text.Trim}' WHERE CID = '{gv_childInt}'"
                'Using cmd As SQLiteCommand = New SQLiteCommand($"UPDATE ChildTable Set cDispDate = '{tbDispDate.Text.Trim}', cMonth = '{tbMonth.Text.Trim}',cYear = '{tbYear.Text.Trim}',cTitle = '{tbTitle.Text.Trim}',cEntry = '{tb.Text.Trim}' WHERE CID = '{gv_childInt}'", conn)
                ' NOTE added tb.Text to line of code above it was rtbEnter.Text.Trim
                '====================================================================

                cmd.Parameters.Add("@cDispDate", CType(SqlDbType.VarChar, DbType)).Value = tbDispDate.Text.Trim
                cmd.Parameters.Add("@cMonth", CType(SqlDbType.VarChar, DbType)).Value = tbMonth.Text.Trim
                cmd.Parameters.Add("@cYear", CType(SqlDbType.VarChar, DbType)).Value = tbYear.Text.Trim
                cmd.Parameters.Add("@cTitle", CType(SqlDbType.VarChar, DbType)).Value = tbTitle.Text.Trim
                'cmd.Parameters.Add("@cEntry", CType(SqlDbType.VarChar, DbType)).Value = rtbEnter.Text.Trim
                'Rewrite code for tb WPF below
                cmd.Parameters.Add("@cEntry", CType(SqlDbType.VarChar, DbType)).Value = tb.Text.Trim
                cmd.ExecuteNonQuery()
                conn.Close()


                'gv_doEdit = False
                'frmStart.Show()
                'Close()
            Catch ex As Exception
                tbMessage.Text = "Child Table Failed"
            End Try
        End Using
    End Using
    gv_doEdit = False
    frmStart.Show()
    Close()
End Sub

So this code has Issues as well 

    Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click

    If Len(tbTitle.Text.Trim) = 0 Then
        tbMessage.ForeColor = Color.Blue
        tbMessage.Text = "Enter the Title"
        tbTitle.Select()
        Return
    End If
    'If rtbEnter.Text.Trim().Length = 0 Then
    If tb.Text.Trim().Length = 0 Then
        tbMessage.ForeColor = Color.Red
        tbMessage.Text = "Enter Data to SAVE"
        'rtbEnter.Select()
        'Rewrite code for tb WPF below
        tb.Focus()
        Return
    End If

    Using conn As New SQLiteConnection($"Data Source = '{gv_dbName}';Version=3;")
        conn.Open()

        Using cmd As New SQLiteCommand
            cmd.Connection = conn
            Try
                cmd.CommandText = "INSERT INTO ChildTable (cDispDate,cMonth,cYear,cTitle,cEntry) VALUES (@cDispDate,@cMonth,@cYear,@cTitle,@cEntry)"

                cmd.Parameters.Add("@cDispDate", CType(SqlDbType.VarChar, DbType)).Value = tbDispDate.Text.Trim
                cmd.Parameters.Add("@cMonth", CType(SqlDbType.VarChar, DbType)).Value = tbMonth.Text.Trim
                cmd.Parameters.Add("@cYear", CType(SqlDbType.VarChar, DbType)).Value = tbYear.Text.Trim
                cmd.Parameters.Add("@cTitle", CType(SqlDbType.VarChar, DbType)).Value = tbTitle.Text.Trim
                'cmd.Parameters.Add("@cEntry", CType(SqlDbType.VarChar, DbType)).Value = rtbEnter.Text.Trim
                'Rewrite code for tb WPF below
                cmd.Parameters.Add("@cEntry", CType(SqlDbType.VarChar, DbType)).Value = tb.Text.Trim

                cmd.ExecuteNonQuery()
                conn.Close()

                If colMonth = tbMonth.Text Then '<== Need To TEST in September <=== READ
                    frmStart.tbMessage.ForeColor = Color.Blue
                    frmStart.tbMessage.Text = "Record Added"
                    frmStart.Show()
                    Close()
                Else
                    InsertInToParent()
                End If
                'Notice Code Above it only saves to parent if entry is for a new Month
                '======================================================================
            Catch ex As Exception
                tbMessage.Text = "Child Table Failed"
            End Try
        End Using
    End Using

End Sub

Upvotes: 0

Views: 101

Answers (1)

Mary
Mary

Reputation: 15091

First, there is way too much going on in a UI event. Try to separate your code into logical methods instead of one large monolith.

You can save indents by combining more than one database object in a single Using block. Just separate with a comma.
I passed the command text and the connection directly to the constructor of the command. You have sort of missed the point of the parameters. The name of the parameter is used directly in the sql update string. I added a parameter for the child ID.
For Sqlite use DbType for the datatype parameter in the .Add method. Intellisense provides the choices available.

Private Sub btnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click
    If Not ValidateUpdateInput() Then
        Return
    End If
    MsgBox("Value CID " & gv_childInt)
    Try
        Dim RowsUpdated = UpdateDatabase()
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
    If RowsUpdated = 1 Then
        gv_doEdit = False
        frmStart.Show()
        Close()
    Else
        MessageBox.Show("Update failed")
    End If
End Sub

Private Function ValidateUpdateInput() As Boolean
    If Len(tbTitle.Text.Trim) = 0 Then
        tbMessage.ForeColor = Color.Blue
        tbMessage.Text = "Enter the Title"
        tbTitle.Select()
        Return False
    End If
    'If rtbEnter.Text.Trim().Length = 0 Then
    If tb.Text.Trim().Length = 0 Then
        tbMessage.ForeColor = Color.Red
        tbMessage.Text = "Enter Data to Update"
        'rtbEnter.Select()
        'Rewrite code for tb WPF below
        tb.Focus()
        Return False

    End If
    Return True
End Function

Public Function UpdateDatabase() As Integer
    Dim RetVal As Integer
    Using conn As New SQLiteConnection($"Data Source = '{gv_dbName}';Version=3;"),
            cmd As New SQLiteCommand("UPDATE ChildTable SET cDispDate = @cDispDate, cMonth = @cMonth,cYear = @cYear,cTitle = @cTitle, cEntry = @cEntry WHERE CID = @ID;", conn)

        'Using cmd As SQLiteCommand = New SQLiteCommand($"UPDATE ChildTable Set cDispDate = '{tbDispDate.Text.Trim}', cMonth = '{tbMonth.Text.Trim}',cYear = '{tbYear.Text.Trim}',cTitle = '{tbTitle.Text.Trim}',cEntry = '{tb.Text.Trim}' WHERE CID = '{gv_childInt}'", conn)
        ' NOTE added tb.Text to line of code above it was rtbEnter.Text.Trim
        '====================================================================

        cmd.Parameters.Add("@cDispDate", DbType.String).Value = tbDispDate.Text.Trim
        cmd.Parameters.Add("@cMonth", DbType.String).Value = tbMonth.Text.Trim
        cmd.Parameters.Add("@cYear", DbType.String).Value = tbYear.Text.Trim
        cmd.Parameters.Add("@cTitle", DbType.String).Value = tbTitle.Text.Trim
        'cmd.Parameters.Add("@cEntry", CType(SqlDbType.VarChar, DbType)).Value = rtbEnter.Text.Trim
        'Rewrite code for tb WPF below
        cmd.Parameters.Add("@cEntry", DbType.String).Value = tb.Text.Trim
        cmd.Parameters.Add("@ID", DbType.String).Value = gv_childInt

        conn.Open()
        RetVal = cmd.ExecuteNonQuery()
    End Using
    Return RetVal
End Function

Upvotes: 1

Related Questions