SQL statement won't insert into DB.sdf

Question

Hi guys, I am using Visual Studio 2008 and have connected a database correctly as I have done a login that works fine, although when I try to insert information submitted in the text boxes a different table, it doesn't enter after I end program to check it still has no data in. Any ideas?

    Dim con As SqlCeConnection = New SqlCeConnection("Data Source=NESdb.sdf")        
    Dim myDA As SqlCeDataAdapter
    Dim myDataSet As DataSet
    Dim dt As New DataTable()        

    'Connect to database'
    con.Open()
    'Attempt to retrieve data'
    Try ' Select username and password that match'
        Dim cmd As SqlCeDataAdapter = New SqlCeDataAdapter("INSERT INTO ScrapVehicles(Fname, Lname, Add1, Add2, Town, PostCode, Telephone, Mob, Email, VehicleType, RegNo, Year, Make, Model, V5, Collected, CollectionDate)" + "VALUES('" & txtFname.Text & "', '" & txtLname.Text & "', '" & txtAdd1.Text & "', '" & txtAdd2.Text & "', '" & txtTown.Text & "', '" & txtPostCode.Text & "', '" & txtTelephone.Text & "', '" & txtMob.Text & "', '" & txtEmail.Text & "', '" & comboVehicleType.Text & "', '" & txtReg.Text & "', '" & comboYear.Text & "', '" & comboMake.Text & "', '" & txtModel.Text & "', '" & chkV5.Text & "', '" & chkCollected.Text & "', '" & dtpWhen.Text & "')", con)
        'Catch errors'
    Catch ex As Exception
    End Try
    'Close connection to database'
    If con.State <> ConnectionState.Closed Then
        con.Close()
    End If

Answer 1

You are missing a bracket at the end

& dtpWhen.Text & "'", con)

should be

& dtpWhen.Text & "')", con)

Answer 2

Instead of using a SqlCeDataAdapter, use a SqlCeCommand object. And after creating it, actually use it (call ExecuteNonQuery on it). And remove the Try, Catch Ex as Exception and End Try lines so that, if an error occurs, you'll actually see it.

That's what I can see from 30 seconds of looking.

Edit

You should also look at using parameters rather than concatenating the INSERT statement together.

Answer 3

You're building up the cmd object, but you don't execute it.

Suggest ditch the Adapter when inserting. Try this instead:

'snipped values for brevity.
Dim insertSql As String = "INSERT INTO ScrapVehicles(Fname, Lname, Add1, Add2, Town) VALUES(@FName, @LName, @Add1, @Add2, @Town)" 

conn.Open()
Dim cmd As New SqlCeCommand(insertSql, conn)
cmd.Parameters.Add(New SqlCeParameter("@FName", txtFirstName.Text.Trim()))
cmd.Parameters.Add(New SqlCeParameter("@LName", txtLastName.Text.Trim()))
cmd.Parameters.Add(New SqlCeParameter("@Add1", txtAdd1.Text.Trim()))
cmd.Parameters.Add(New SqlCeParameter("@Add2", txtAdd2.Text.Trim()))

cmd.ExecuteNonQuery()
conn.Close()