I am doing an assignment in college and I have got most of it working. It shows the database and i can navigate through it but can't create a new record or update a previous record or delete a record. can you help me?
VB.NET:
Imports System.Data
Public Class frmMain
Inherits System.Windows.Forms.Form
Dim con As New OleDb.OleDbConnection()
Dim sql As String
Dim ds As New DataSet()
Dim da As OleDb.OleDbDataAdapter
Dim inc As Integer
Dim MaxRows As Integer
Dim Records, current As Integer
Private Sub NavigateRecords()
txtTeams.Text = ds.Tables("Premier").Rows(inc).Item(1)
txtP.Text = ds.Tables("Premier").Rows(inc).Item(2)
txtHomeW.Text = ds.Tables("Premier").Rows(inc).Item(3)
txtHomeD.Text = ds.Tables("Premier").Rows(inc).Item(4)
txtHomeL.Text = ds.Tables("Premier").Rows(inc).Item(5)
txtHomeF.Text = ds.Tables("Premier").Rows(inc).Item(6)
txtHomeA.Text = ds.Tables("Premier").Rows(inc).Item(7)
txtAwayW.Text = ds.Tables("Premier").Rows(inc).Item(8)
txtAwayD.Text = ds.Tables("Premier").Rows(inc).Item(9)
txtAwayL.Text = ds.Tables("Premier").Rows(inc).Item(10)
txtAwayF.Text = ds.Tables("Premier").Rows(inc).Item(11)
txtAwayA.Text = ds.Tables("Premier").Rows(inc).Item(12)
txtAwayGD.Text = ds.Tables("Premier").Rows(inc).Item(13)
txtAwayPTS.Text = ds.Tables("Premier").Rows(inc).Item(14)
End Sub
Private Sub MenuItem4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem4.Click
Me.Close()
End Sub
Private Sub btnQuit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnQuit.Click
Me.Close()
End Sub
Private Sub btnLoad_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoad.Click
con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = E:\Software Development Project 2010\dbPremier.mdb"
con.Open()
'sql = "SELECT tblContacts.FirstName, tblContacts.Surname FROM tblCon"
sql = "SELECT * FROM tblPremier"
da = New OleDb.OleDbDataAdapter(sql, con)
da.Fill(ds, "Premier")
con.Close()
NavigateRecords()
MaxRows = ds.Tables("Premier").Rows.Count
inc = -1
End Sub
Private Sub btnDisplay_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDisplay.Click
dtgDisplay.DataSource = ds
dtgDisplay.DataMember = "Premier"
End Sub
Private Sub btnPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrevious.Click
If inc > 0 Then
inc = inc - 1
NavigateRecords()
Else
MsgBox("First Record")
End If
End Sub
Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
If inc <> MaxRows - 1 Then
inc = inc + 1
NavigateRecords()
Else
MsgBox("No More Rows")
End If
End Sub
Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click
If inc <> MaxRows - 1 Then
inc = MaxRows - 1
NavigateRecords()
End If
End Sub
Private Sub btnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirst.Click
If inc <> 0 Then
inc = 0
NavigateRecords()
End If
End Sub
Private Sub btnNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNew.Click
btnConfirm.Enabled = True
btnNew.Enabled = False
btnUpdate.Enabled = False
btnDelete.Enabled = False
txtTeams.Clear()
txtP.Clear()
txtHomeW.Clear()
txtHomeD.Clear()
txtHomeL.Clear()
txtHomeF.Clear()
txtHomeA.Clear()
txtAwayW.Clear()
txtAwayD.Clear()
txtAwayL.Clear()
txtAwayF.Clear()
txtAwayA.Clear()
txtAwayGD.Clear()
txtAwayPTS.Clear()
End Sub
Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
Dim cb As New OleDb.OleDbCommandBuilder(da)
If MessageBox.Show("Do you really want to Delete this Record?", _
"Delete", MessageBoxButtons.YesNo, _
MessageBoxIcon.Warning) = DialogResult.No Then
MsgBox("Operation Cancelled")
Exit Sub
End If
ds.Tables("Premier").Rows(inc).Delete()
MaxRows = MaxRows - 1
inc = 0
NavigateRecords()
da.Update(ds, "Premier")
End Sub
Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
Dim cb As New OleDb.OleDbCommandBuilder(da)
ds.Tables("Premier").Rows(inc).Item(1) = txtTeams.Text
ds.Tables("Premier").Rows(inc).Item(2) = txtP.Text
ds.Tables("Premier").Rows(inc).Item(3) = txtHomeW.Text
ds.Tables("Premier").Rows(inc).Item(4) = txtHomeD.Text
ds.Tables("Premier").Rows(inc).Item(5) = txtHomeL.Text
ds.Tables("Premier").Rows(inc).Item(6) = txtHomeF.Text
ds.Tables("Premier").Rows(inc).Item(7) = txtHomeA.Text
ds.Tables("Premier").Rows(inc).Item(8) = txtAwayW.Text
ds.Tables("Premier").Rows(inc).Item(9) = txtAwayD.Text
ds.Tables("Premier").Rows(inc).Item(10) = txtAwayL.Text
ds.Tables("Premier").Rows(inc).Item(11) = txtAwayF.Text
ds.Tables("Premier").Rows(inc).Item(12) = txtAwayA.Text
ds.Tables("Premier").Rows(inc).Item(13) = txtAwayGD.Text
ds.Tables("Premier").Rows(inc).Item(14) = txtAwayPTS.Text
da.Update(ds, "Premier")
MsgBox("Data updated")
End Sub
Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click
btnNew.Enabled = True
btnUpdate.Enabled = True
btnDelete.Enabled = True
inc = 0
NavigateRecords()
End Sub
Private Sub btnConfirm_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnConfirm.Click
If inc <> -1 Then
Dim cb As New OleDb.OleDbCommandBuilder(da)
Dim dsNewRow As DataRow
dsNewRow = ds.Tables("Premier").NewRow()
dsNewRow.Item("Team") = txtTeams.Text
dsNewRow.Item("P") = txtP.Text
dsNewRow.Item("Home W") = txtHomeW.Text
dsNewRow.Item("Home D") = txtHomeD.Text
dsNewRow.Item("Home L") = txtHomeL.Text
dsNewRow.Item("Home F") = txtHomeF.Text
dsNewRow.Item("Home A") = txtHomeA.Text
dsNewRow.Item("Away W") = txtAwayW.Text
dsNewRow.Item("Away D") = txtAwayD.Text
dsNewRow.Item("Away L") = txtAwayL.Text
dsNewRow.Item("Away F") = txtAwayF.Text
dsNewRow.Item("Away A") = txtAwayA.Text
dsNewRow.Item("Away GD") = txtAwayGD.Text
dsNewRow.Item("Away PTS") = txtAwayPTS.Text
ds.Tables("Premier").Rows.Add(dsNewRow)
da.Update(ds, "Premier")
MsgBox("New Record added to the Database")
End If
End Sub
End Class
