Question Problem Registering Employees onto MS Access (XP)

[Saiph0902]

This is the code I'm using to be able to register new users onto our Sales and Inventory System, and I get an error:

and it's pointing on the line Dim dr As OleDbDataReader = cmd.ExecuteReader

Imports System.Data.OleDb
Public Class Register
    Dim ds As New DataSet
    Dim da As New OleDbDataAdapter
    Dim cb As New OleDb.OleDbCommandBuilder(da)
    Dim cn As New OleDbConnection
    Dim cmd As OleDbCommand
    Dim sql As String
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Me.Close()
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim ds As New DataSet
        Dim da As New OleDbDataAdapter
        Dim cb As New OleDb.OleDbCommandBuilder(da)
        Dim cn As New OleDbConnection
        Dim cmd As OleDbCommand
        Dim sql = "INSERT INTO Employees(GivenName, LastName, Gender, JobPosition, Month, Day, Year" & _
                "Address, EmailAddress, Username, Passcode, SecretQ1, SecretQ2, SecretA1," & _
                "SecretA2) VALUES('" & txtGivenName.Text & "','" & txtLastName.Text & "','" & _
                "'" & cmbGender.Text & "','" & "Employee" & "','" & cmbMonth.Text & "','" & _
                "'" & cmbDay.Text & "','" & cmbYear.Text & "','" & "'" & txtUsername.Text & "','" & _
                "'" & txtPassword.Text & "','" & cmbSecretQ1.Text & "','" & _
                "'" & cmbSecretQ2.Text & "','" & txtSecretA1.Text & "','" & txtSecretA2.Text & "')"

        If txtConfirmPW.Text <> txtPassword.Text Then
            MessageBox.Show("The password does not match!")
        End If

        cn.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0; data source=C:\Documents and Settings\James\My Documents\MSNPharmacy 07-30-2011\MSNPharmacy\MSNPharmacy\msn_pharmacy.mdb"
        cn.Open()
        da = New OleDb.OleDbDataAdapter(Sql, cn)
        cmd = New OleDbCommand(Sql, cn)
        Dim dr As OleDbDataReader = cmd.ExecuteReader
        SuccessRegister.ShowDialog()
        Profile.Show()
        Me.Hide()
    End Sub
End Class

 

[jmcilhinney]

There's a lot of wrong stuff going on there. First up, you are creating a data adapter and command builder that you never use. You only use a data adapter when you want to populate or save a whole DataTable and you only use a command builder if you want the system to generate the action commands (DELETE, INSERT, UPDATE) automatically from the query you provide. You're not doing any of that so get rid of both the data adapter and the command builder.

Secondly, you are using a data reader when you aren't reading anything. A data reader is for reading the result set of a query row by row. You aren't executing a query so get rid of the data reader. You should be calling ExecuteNonQuery.

Most importantly though, don't use string concatenation to insert variables into SQL code. Always use parameters. To learn why and how, follow the Blog link in my signature and check out my post on ADO.NET Parameters. If you do that then your syntax error should probably go away. If it doesn't then that would suggest that one of your column names is a reserved word, so you'd need to wrap at least that identifier in brackets.

 

[Saiph0902]

Imports System.Data.OleDb
Public Class Register
    Dim ds As New DataSet
    Dim cn As New OleDbConnection
    Dim cmd As OleDbCommand
    Dim sql As String
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Me.Close()
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim ds As New DataSet
        Dim cn As New OleDbConnection
        cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source= C:\Documents and Settings\James\My Documents\MSN Pharmacy 08-08-2011\MSNPharmacy 2\MSNPharmacy\msn_pharmacy.mdb"
        cn.Open()
        Dim sql = "INSERT INTO Employees (LastName, GivenName, Gender, JobPosition, Month, Day" & _
                    "Year, Username, [Password], SecretQuestion1, SecretQuestion2, SecretAnswer1" & _
                    "SecretAnswer2) VALUES(?LastName, ?GivenName, ?Gender ,?Month, ?Day, ?Year" & _
                    "?Username, ?[Password], ?SecretQuestion1, ?SecretQuestion2, ?SecretAnswer1" & _
                    "?SecretAnswer2)"
        Dim cmd As New OleDbCommand(sql)

        With cmd.Parameters

            .AddWithValue("?LastName", Me.txtLastName.Text)
            .AddWithValue("?GivenName", Me.txtGivenName.Text)
            .AddWithValue("?Gender", Me.cmbGender.Text)
            .AddWithValue("?JobPosition", "Employee")
            .AddWithValue("?Month", Me.cmbMonth.Text)
            .AddWithValue("?Day", Me.cmbDay.Text)
            .AddWithValue("?Year", Me.cmbYear.Text)
            .AddWithValue("?Username", Me.txtUsername.Text)
            .AddWithValue("?[Password]", Me.txtPassword.Text)
            .AddWithValue("?SecretQuestion1", Me.cmbSecretQ1.Text)
            .AddWithValue("?SecretAnswer1", Me.txtSecretA1.Text)
            .AddWithValue("?SecretQuestion2", Me.cmbSecretQ2.Text)
            .AddWithValue("?SecretAnswer2", Me.txtSecretA2.Text)
        End With


        If txtConfirmPW.Text <> txtPassword.Text Then
            MessageBox.Show("The password does not match!")
        End If

        SuccessRegister.ShowDialog()
        Profile.Show()
        Me.Hide()
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click

    End Sub
End Class

I somewhat followed what your blog said when it comes to utilizing SQL and parameters. Okay, the registrant was successful, and now taken to the company's main menu, but the problem is now that the registered account does not reflect to the database.

 

[jmcilhinney]

You missed the bit that actually saves the data.

You should be calling ExecuteNonQuery.