Question How to insert data into table "users"?

[Lie1964]

I would like to know what the VB.NET code should be on how to insert data via VB.net express 2010 form fields into an access 2007 database.
I get a syntax error (near to the da.update) when I use the following code.
Can anyone help me and give me the correct code?

Public Class Registreren

    Dim da As OleDb.OleDbDataAdapter
    Dim ds As New DataSet

    Dim con As New OleDb.OleDbConnection
    Dim sql As String

    Dim dbProvider As String
    Dim dbSource As String

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RegisterButton.Click
        'On Error GoTo Fout
        Dim cb As New OleDb.OleDbCommandBuilder(da)
        Dim dsNewRow As DataRow

        If GebruikersnaamTextBox.Text = "" Or WachtwoordTextBox.Text = "" Or EmailTextBox.Text = "" Then
            MsgBox("Alle velden zijn verplicht!")
        Else
            If EmailTextBox.Text Like "*@*.*" Then

                dbProvider = "Provider=Microsoft.ACE.OLEDB.12.0;"
                dbSource = "Data Source = C:\Users\Compaq\Documents\Koen\VBproject\LoginTest.accdb"

                con.ConnectionString = dbProvider & dbSource

                con.Open()

                dsNewRow = ds.Tables("Gebruikers").NewRow()

                dsNewRow.Item("Gebruikersnaam") = GebruikersnaamTextBox.Text
                dsNewRow.Item("Wachtwoord") = WachtwoordTextBox.Text
                dsNewRow.Item("E-mail") = EmailTextBox.Text
                dsNewRow.Item("Beheerder") = False

                ds.Tables("Gebruikers").Rows.Add(dsNewRow)
                da.Update(ds, "Gebruikers")

                Me.Close()
            Else
                MsgBox("Ongeldig e-mail adres!")
            End If
        End If
        Exit Sub

Fout:
        MsgBox("Fout " & Err.Number & vbCrLf & Err.Description)
    End Sub

    Private Sub Form4_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        dbProvider = "Provider=Microsoft.ACE.OLEDB.12.0;"
        dbSource = "Data Source = C:\Users\Compaq\Documents\Koen\VBproject\LoginTest.accdb"

        con.ConnectionString = dbProvider & dbSource

        con.Open()

        sql = "SELECT * FROM Gebruikers"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "Gebruikers")

        con.Close()

    End Sub
End Class

I cannot find the correct documentation for programming VB.net projects in VB expres 2010 using database inserts, updates, deletes etc..
Does anyone know a good course book or tutorial which mainly deals with the VB programming and databases (in this case access databases)?

Thanks in advance!

 

[jmcilhinney]

The issue is the fact that you have a dash in your "E-mail" column name. You should always avoid everything other than letters and numbers in your column names and other identifiers if at all possible because they just invite issues. At the moment, when the command builder builds your commands, that dash is being interpreted as a subtraction operator and is thus causing a syntax error.

To fix the issue, your first option should be to change that column name to "Email". That's really more correct these days anyway as noone really uses a dash in such a common term any more. If you can't do that then your second option is to set the QuotePrefix and QuoteSuffix properties of the command builder so that it escapes all column names in the SQL code. For Access, use "[" and "]" respectively, so your column name ends up appearing as [E-mail] and the dash doesn't cause any issues.

 

[Lie1964]

It works! Many thanks!!
Do you also know a good book, tutorial or course which only handles VB.net and databases coding?

 

[Lie1964]

Does anyone know a good explanation of how to code database interactions such as select database data via fields on a form, update data from form fields into the database, insert data from fields into the database and delete data from fields from the database?

 

[jmcilhinney]

Follow the Data Walkthroughs link in my signature.