Registering Employees onto my Access DB (VB 2008 + MS Access XP)

[ClimaxBeetle]

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 As String

        'sql = "INSERT INTO Employees(GivenName, LastName, Gender, JobPosition, Month, Day,  " & _
        '"Address, EmailAddress, )"
        sql = "INSERT INTO Employees(GivenName) where GivenName='" & txtGivenName.Text
        sql = "INSERT INTO Employees(LastName) where LastName='" & txtLastName.Text
        sql = "INSERT INTO Employees(Gender) where Gender='" & cmbGender.Text
        sql = "INSERT INTO Employees(JobPosition) where JobPosition='" & cmbJobPos.Text
        sql = "INSERT INTO Employees(Month) where Month='" & cmbMonth.Text
        sql = "INSERT INTO Employees(Day) where Day='" & cmbDay.Text
        sql = "INSERT INTO Employees(Year) where Year='" & cmbYear.Text
        sql = "INSERT INTO Employees(Address) where Address='" & txtAddress.Text
        sql = "INSERT INTO Employees(EmailAddress) where EmailAddress='" & txtEmailAddress.Text
        sql = "INSERT INTO Employees(Username) where Username='" & txtUsername.Text
        sql = "INSERT INTO Employees([Password]) where [Password]='" & txtPassword.Text
        sql = "INSERT INTO Employees(SecretQ1) where SecretQ1='" & cmbSecretQ1.Text
        sql = "INSERT INTO Employees(SecretQ2) where SecretQ2='" & cmbSecretQ2.Text
        sql = "INSERT INTO Employees(SecretA1) values('" & txtSecretA1.Text '")
        sql = "INSERT INTO Employees(SecretA2) values(‘" & 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\MSN Pharmacy\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

My SQL skills are a bit rusty, and I so far remember about SELECT, not yet in INSERT, INSERT INTO, and UPDATE. I dunno if am I doing the code right, especially in adding data based on what's typed in the text boxes.

[debasisdas]

Your SQL syntax is wrong.

[ClimaxBeetle]

SQLstr = "INSERT INTO TableTestEmployes VALUES('" & TextBox1.Text & "','" & TextBox2.Text & "','" & _ 
           TextBox3.Text & "','" & ComboBox1.Text & "')"

^ Is this the right one? or is there a more organized way? I have like 16 items asked to the new employee on that form.

[debasisdas]

That should work.

Before insert check if the same record already exists (to avoid duplicate).

[adam_k]

SQLstr = "INSERT INTO TableTestEmployes VALUES('" & TextBox1.Text & "','" & TextBox2.Text & "','" & _ 
           TextBox3.Text & "','" & ComboBox1.Text & "')"

^ Is this the right one? or is there a more organized way? I have like 16 items asked to the new employee on that form.

This syntax will work as long as you are assigning values to all fields, with the order they are declared in the table. If you change the order of the fields or don't assign value to one or more fields then you have to use the full syntax:

INSERT INTO Table
(FieldName1,FieldName2,FieldName3) 
VALUES
('Value1','Value2','Value3')

[ClimaxBeetle]

Imports System.Data.OleDb
Public Class Register
    'Public ds As New DataSet
    Public da As New OleDbDataAdapter
    Public cb As New OleDb.OleDbCommandBuilder(da)
    Public cn As New OleDbConnection
    Public cmd As OleDbCommand
    Public dr As OleDb.OleDbDataReader
    Public 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 As String

        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 & "','" & cmbJobPos.Text & "','" & cmbMonth.Text & "','" & _
                "'" & cmbDay.Text & "','" & cmbYear.Text & "','" & txtAddress.Text & "','" & _
                "'" & txtEmailAddress.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\MSN Pharmacy\msn_pharmacy.mdb"
        cn.Open()
        da = New OleDb.OleDbDataAdapter(sql, cn)
        cmd = New OleDbCommand(sql, cn)
        Dim dr As OleDbDataReader = cmd.ExecuteNonQuery
        SuccessRegister.ShowDialog()
        Profile.Show()
        Me.Hide()
    End Sub

    Private Sub Register_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        'TODO: This line of code loads data into the 'Msn_pharmacyDataSet1.Employees' table. You can move, or remove it, as needed.
        Me.EmployeesTableAdapter.Fill(Me.Msn_pharmacyDataSet1.Employees)
    End Sub
End Class

Updated code, but I get a "Syntax Error in INSERT INTO Statement" when I'm about to register a new user.

[debasisdas]

1. try to print the sql before executing
2. try to execute the sql at backend and check if you are getting any error.

[adam_k]

You are missing a comma (,) after Year and before Address. You are also using 1 quote too many when assigning values with cmbGender.Text, cmbDay.Text, txtEmailAddress.Text, txtPassword.Text, cmbSecretQ2.Text.
You are adding the quote with the

& "','" & _

and then adding another quote with the

"'" & cmbGender.Text