[RESOLVED] Beginner trying to learn SQLite database

[jumper77]

Before I spend too much time with this issue, I'm going to ask a question.

• First I insert a new row in customers database
• Then I call a function to update the record. It changes the last name in the record.
• Before doing the ExecuteNonQurey, the value contains the correct last name to be changed.
• After ExecuteNonQurey, the last name is not changed.

this is because ID always has the value of 0, and I'm not sure why. So here's the create database and calling code:

   'Creates New Database if not found
    Public Sub CreateDatabase()
        Using objConn As New SQLiteConnection(__connectionStringCreateDatabase)
            Using objCommand As SQLiteCommand = objConn.CreateCommand()
                Try
                    With objCommand
                        objConn.Open()

                        .CommandText = "Create TABLE customer('Id' INTEGER PRIMARY KEY,'FirstName' Text Not NULL, 'LastName' Text Not NULL, Address Text Not NULL, 'PhoneNumber' Text Not Null);"
                        .ExecuteNonQuery()

                    End With
                Catch ex As Exception
                    MsgBox(ex.Message)
                End Try
            End Using
        End Using
    End Sub

Module Module1
    Const databaseName As String = "customers.db"
    Private objConn As New SQLiteConnection
    Private objCommand As New SQLiteCommand
    Private dbcustomers As New List(Of Customer)()
    Private updateCustomer As New Customer
    Private db As New CustomerDatabase(databaseName)
    Private conString As New CustomerDatabase(connectionString)

    Sub Main()
        Dim customer As New Customer

        If Not Exists(databaseName) Then
            db.CreateDatabase()
        End If

        TestInsert()
        TestUpdate()
    End Sub

    Sub TestUpdate()
        Dim customer As New Customer

        With Customer
            .FirstName = "Joe"
            .LastName = "bob"
            .address = "20 west main street"
            .phoneNumber = "444.1212"
        End With

        db.Update(customer)
    End Sub

    Public Sub TestInsert()
        Dim customer As New Customer

        With customer
            .FirstName = "Joe"
            .LastName = "Pendalton"
            .address = "20 west main street"
            .phoneNumber = "444.1212"
        End With

        dbcustomers.Add(customer)
        db.Insert(dbcustomers)
    End Sub

Now, the Insert and Update subs:

   Public Sub Insert(ByVal customers As IEnumerable(Of Customer))
        ' Lesson 3: the query is converted to use parameters.
        Dim insertStatement As String = "INSERT INTO customer (firstName, lastname, address, phoneNumber) VALUES (:firstName, :lastName, :address, :phoneNumber)"

        Try
            Using connection As SQLiteConnection = CreateConnection()
                Using command As SQLiteCommand = connection.CreateCommand()
                    command.CommandText = insertStatement
                    command.Parameters.Add(New SQLiteParameter("firstName"))
                    command.Parameters.Add(New SQLiteParameter("lastName"))
                    command.Parameters.Add(New SQLiteParameter("address"))
                    command.Parameters.Add(New SQLiteParameter("phoneNumber"))

                    For Each customer In customers
                        command.Parameters("firstName").Value = customer.FirstName
                        command.Parameters("lastName").Value = customer.LastName
                        command.Parameters("address").Value = customer.address
                        command.Parameters("phoneNumber").Value = customer.phoneNumber

                        command.ExecuteNonQuery()
                        'If command.ExecuteNonQuery() = -1 Then
                        '    Console.WriteLine("INSERT failed!")
                        'End If
                    Next
                End Using
            End Using
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Public Sub Update(ByVal customer As Customer)
        Dim updateQuery As String = "UPDATE customer SET " &
        "firstName = :firstName, lastName = :lastName, address = :address, phoneNumber = :phoneNumber WHERE id = id"

        Try
            Using connection As SQLiteConnection = CreateConnection()
                Using command As SQLiteCommand = connection.CreateCommand()
                    command.CommandText = updateQuery
                    command.Parameters.Add(New SQLiteParameter("firstName", customer.FirstName))
                    command.Parameters.Add(New SQLiteParameter("lastName", customer.LastName))
                    command.Parameters.Add(New SQLiteParameter("address", customer.address))
                    command.Parameters.Add(New SQLiteParameter("phoneNumber", customer.phoneNumber))
                    command.Parameters.Add(New SQLiteParameter("id", customer.Id))

                    command.ExecuteNonQuery()

                    'If command.ExecuteNonQuery() = -1 Then
                    '    Console.WriteLine("An UPDATE failed!")
                    'End If
                End Using
            End Using
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

Anyone have an idea of what be doing on? I can't seem to see it.

edit: The ID field should auto-increment, but it's not.