VB 2010 to SQL.CE (.sdf) INSERT, etc Problem

[bdbc78]

I'm having incredible difficulty getting my program to modify my SQL database in any manner. Pulling data works fine, but INSERT, UPDATE, and DELETE commands all fail. Currently, I'm concentrating all of my efforts in figuring out the INSERT issue, and would hope that figuring that out would lead to success in other areas. I'm pretty sure this issue has something to do with compatibility between VB 2010 and SQL Compact (.sdf). I read somewhere that .sdf's will not automatically assign a primary key to a new database entry, and it seems to be reflected in my issues with inserting a new row. Here is how my code looks as of now:

Public Shared Function InsertCustomer(ByVal theCustomer As Customer) As Boolean

        If theCustomer Is Nothing Then
            Return False
        End If

        Dim count As Integer

        Dim adapter As New OleDbDataAdapter("SELECT customer_ID FROM Customer_Table", myDB)

        Try
            Dim myCommand As New OleDbCommand("INSERT INTO Customer_Table (customer_ID, first_Name, last_Name, phone_Number, active_Customer, email_Address, address, city, [state], zip_Code) VALUES (@customer_ID, @first_Name, @last_Name, @phone_Number, @active_Customer, @email_Address, @address, @city, [@state], @zip_Code)", myDB)

            With myCommand.Parameters
                .AddWithValue("customer_ID", theCustomer.customer_ID)
                .AddWithValue("@first_Name", theCustomer.first_Name)
                .AddWithValue("@last_Name", theCustomer.last_Name)
                .AddWithValue("@phone_Number", theCustomer.phone_Number)
                .AddWithValue("@active_Customer", True)
                .AddWithValue("@email_Address", theCustomer.email_Address)
                .AddWithValue("@address", theCustomer.address)
                .AddWithValue("@city", theCustomer.city)
                .AddWithValue("@state", theCustomer.state)
                .AddWithValue("@zip_Code", theCustomer.zip_Code)
            End With

            adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
            adapter.InsertCommand = myCommand
            count = myCommand.ExecuteNonQuery()

        Catch ex As Exception
            Throw ex
        End Try

        Return count > 0

    End Function

The app basically closes itself without throwing an exception, once it hits ExecuteNonQuery. If I omit that, and change the Return to True, then the code completes, but the new 'customer' does not show up in a database query. Here's my initial, base attempt:

Public Shared Function InsertCustomer(ByVal theCustomer As Customer) As Boolean

        If theCustomer Is Nothing Then
            Return False
        End If

        Dim count As Integer

        Try
            Dim myCommand As New OleDbCommand("INSERT INTO Customer_Table (first_Name, last_Name, phone_Number, active_Customer, email_Address, address, city, [state], zip_Code) VALUES (@first_Name, @last_Name, @phone_Number, @active_Customer, @email_Address, @address, @city, [@state], @zip_Code)", myDB)

            With myCommand.Parameters
                .AddWithValue("@first_Name", theCustomer.first_Name)
                .AddWithValue("@last_Name", theCustomer.last_Name)
                .AddWithValue("@phone_Number", theCustomer.phone_Number)
                .AddWithValue("@active_Customer", True)
                .AddWithValue("@email_Address", theCustomer.email_Address)
                .AddWithValue("@address", theCustomer.address)
                .AddWithValue("@city", theCustomer.city)
                .AddWithValue("@state", theCustomer.state)
                .AddWithValue("@zip_Code", theCustomer.zip_Code)
            End With

            count = myCommand.ExecuteNonQuery()

        Catch ex As Exception
            Throw ex
        End Try

        Return count > 0

    End Function

One main difference is the omission of any mention of the PK field customer_ID. Unfortunately, when I try to run this, the function tries to insert all of these fields into a new row in this exact order, thereby placing the first_Name contents into the PK customer_ID field, and throwing an error because I'm trying to insert a String into a Long as a result. You can see that I played around a bit with trying to automatically create a PK value using the:

adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
            adapter.InsertCommand = myCommand

However, apparently either I'm not doing it right, or it's somehow not compatible. Any suggestions? Keep in mind that I'm doing VB 2010 to SQL.CE (.sdf), so there's some limitations in code flexibility. I have read through all of what appeared to be relevant links in the VB Forums DB CodeBank, and the Database Development FAQ, however if you feel that I may have overlooked something especially applicable to my problem in there, by all means let me know. Thank you in advance for any assistance.


A little piece of extra info:

    Public Shared myDB As OleDbConnection = New OleDbConnection("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=acme.sdf;")

    Shared Sub New()
        myDB.Open()
    End Sub

    Public Shared Sub CloseConnection()
        myDB.Close()
    End Sub

[bdbc78]

Here's a listing of my tables and columns, along with a snapshot of the customer_ID properties:



Here's the code that works for pulling my customers from the database:

    Public Shared Function GetCustomer(ByVal email As String) As Customer

        Dim theCustomer As Customer = Nothing

        Try
            Dim myCommand As New OleDbCommand("SELECT customer_ID, first_Name, last_Name, phone_Number, active_Customer, email_Address, address, city, state, zip_Code FROM Customer_Table WHERE email_Address = '" & email & "'", myDB)
            Dim dr As OleDbDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
            If dr.Read() Then
                theCustomer = New Customer(dr("customer_ID"), dr("first_Name"), dr("last_Name"), dr("phone_Number"), dr("active_Customer"), dr("email_Address"), dr("address"), dr("city"), dr("state"), dr("zip_Code"))
            End If

        Catch ex As Exception
            Throw ex
        End Try

        Return theCustomer

    End Function

Maybe this info will help in some way.