#Region "Public Declaration"
'Create connection
Dim conStaff As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=XGamesDB.mdb;Persist Security Info=False")
'Create command
Dim cmdStaff As New OleDbCommand("SELECT * FROM Staff Where EmployeeID = " & EmpID, conStaff)
'Create data adapter
Dim daStaff As New OleDbDataAdapter(cmdStaff)
'Create dataset
Dim dsStaff As New DataSet()
#End Region
#Region "Form Code"
Private Sub frmManageStf_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Open connection
conStaff.Open()
'Fill Data Adapter
daStaff.Fill(dsStaff, "Staff")
'Bind data to controls
Call BindData()
'Set 'Insert, Delete and Update' SQL statements
Call SetSQLStatements()
End Sub
Private Sub frmManageStf_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing
'Close connection, if its open
If conStaff.State = ConnectionState.Open Then conStaff.Close()
End Sub
#End Region
#Region "Sub Procedures"
Private Sub BindData()
'Bind data to controls
txtEmpID.DataBindings.Add(New Binding("Text", dsStaff, "Staff.EmployeeID"))
txtSurname.DataBindings.Add(New Binding("Text", dsStaff, "Staff.Surname"))
txtForename.DataBindings.Add(New Binding("Text", dsStaff, "Staff.Forename"))
txtGender.DataBindings.Add(New Binding("Text", dsStaff, "Staff.Gender"))
dtpDOB.DataBindings.Add(New Binding("Text", dsStaff, "Staff.DOB"))
txtAddress.DataBindings.Add(New Binding("Text", dsStaff, "Staff.Address"))
txtPostcode.DataBindings.Add(New Binding("Text", dsStaff, "Staff.Postcode"))
txtTelNum.DataBindings.Add(New Binding("Text", dsStaff, "Staff.Telephone Number"))
txtContract.DataBindings.Add(New Binding("Text", dsStaff, "Staff.Contract"))
txtSalary.DataBindings.Add(New Binding("Text", dsStaff, "Staff.Salary"))
txtPosition.DataBindings.Add(New Binding("Text", dsStaff, "Staff.Position"))
dtpStartDate.DataBindings.Add(New Binding("Text", dsStaff, "Staff.StartDate"))
cboBookingRights.DataBindings.Add(New Binding("Text", dsStaff, "Staff.BookingRights"))
cboLoginRights.DataBindings.Add(New Binding("Text", dsStaff, "Staff.LoginRights"))
cboMemberRights.DataBindings.Add(New Binding("Text", dsStaff, "Staff.MemberRights"))
cboManageRights.DataBindings.Add(New Binding("Text", dsStaff, "Staff.ManageRights"))
End Sub
Private Sub SetSQLStatements()
'Add parameters to use in SQL statements
With cmdStaff.Parameters
.Add("@EmpID", txtEmpID.Text)
.Add("@Surname", txtSurname.Text)
.Add("@Forename", txtForename.Text)
.Add("@Gender", txtGender.Text)
.Add("@DOB", dtpDOB.Text)
.Add("@Address", txtAddress.Text)
.Add("@Postcode", txtPostcode.Text)
.Add("@TelNum", txtTelNum.Text)
.Add("@Contract", txtContract.Text)
.Add("@Salary", txtSalary.Text)
.Add("@Position", txtPosition.Text)
.Add("@StartDate", dtpStartDate.Text)
.Add("@BookRight", cboBookingRights.Text)
.Add("@LoginRight", cboLoginRights.Text)
.Add("@MemberRight", cboMemberRights.Text)
.Add("@ManageRight", cboManageRights.Text)
End With
Dim SQLUpdateStr As String
'SQL Update string
SQLUpdateStr = "UPDATE Staff SET Address = @Address, BookingRights = @BookRight," & _
"Contract = @Contract, DOB = @DOB, Forename = @Forename, Gender = @Gender," & _
"LoginRights = @LoginRight, ManageRights = @ManageRight, MemberRights = @MemberRight," & _
"Position = @Position, Postcode = @Postcode, Salary = @Salary, StartDate = @StartDate," & _
"Surname = @Surname, [Telephone Number] = @TelNum"
'Create instances of OLEDB commands
Dim cmdSQLDel As New OleDbCommand("DELETE * FROM Staff WHERE EmployeeID = @EmpID", conStaff) 'Delete statement
Dim cmdSQLIns As New OleDbCommand("INSERT INTO Staff", conStaff) 'Insert statement
Dim cmdSQLUpd As New OleDbCommand(SQLUpdateStr) 'Update statement
'Set connection for 'cmdSQLUpd'
cmdSQLUpd.Connection = conStaff
'Set SQL statements
With daStaff
.DeleteCommand = cmdSQLDel
.InsertCommand = cmdSQLIns
.UpdateCommand = cmdSQLUpd
End With
End Sub
#End Region
Private Sub btnAddEmp_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddEmp.Click
Try
daStaff.DeleteCommand.ExecuteNonQuery()
'Whatever command i try to execute, i still get a syntax error in my SQL queries
Catch Ex As Exception
MessageBox.Show(Ex.Message)
End Try
End Sub