I am doing a project and i am linking to a access database table. I am writing my own code to connect to data adapter. i have a list of records in a database table and want ot edit one record and update it in the database or create a new record or delete a record but can't get it to work. can you help me? the code i have done so far is here
VB.NET:
Dim aTeams(14) As String
Dim aP(14) As Integer
Dim aHomeW(14) As Integer
Dim aHomeD(14) As Integer
Dim aHomeL(14) As Integer
Dim aHomeF(14) As Integer
Dim aHomeA(14) As Integer
Dim aAwayW(14) As Integer
Dim aAwayD(14) As Integer
Dim aAwayL(14) As Integer
Dim aAwayF(14) As Integer
Dim aAwayA(14) As Integer
Dim aGD(14) As Integer
Dim aPTS(14) As Integer
Dim No_of_Teams As Integer, a, c As Integer
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
'Load Button, To load the database into the dataset
Private Sub btnLoad_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoad.Click
panEdit.Visible = True
con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = F:\College Work\Course Work\VB .NET\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
'Navigation method, to navigate through the records
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
'Display Button, Display the dataset in the DataGrid
Private Sub btnDisplay_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDisplay.Click
panDisplay.Visible = True
dtgDisplay.DataSource = ds
dtgDisplay.DataMember = "Premier"
End Sub
'Navigate to the First record
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
'Navigate to the Last record
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
'Navigate to the Next record
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
'Navigate to the Previous record
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
'new Record Button, to create a new record
Private Sub btnNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNew.Click
panAdd.Visible = True
panEdit.Visible = False
btnUpdate.Enabled = False
btnDelete.Enabled = False
txtTeam1.Clear()
txtP1.Clear()
txtHomeW1.Clear()
txtHomeD1.Clear()
txtHomeL1.Clear()
txtHomeF1.Clear()
txtHomeA1.Clear()
txtAwayW1.Clear()
txtAwayD1.Clear()
txtAwayL1.Clear()
txtAwayF1.Clear()
txtAwayA1.Clear()
txtAwayGD1.Clear()
txtAwayPTS1.Clear()
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)
For c = 1 To No_of_Teams
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")
Next
MsgBox("Data updated")
End Sub
'Clear Button, to clear textboxes
Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click
panAdd.Visible = False
panEdit.Visible = True
btnUpdate.Enabled = True
btnDelete.Enabled = True
inc = 0
NavigateRecords()
End Sub
'Delete Record Button, to delete a record
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
'Confirm Button, to confirm on creating a new record
Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
If inc <> -1 Then
Dim cb As New OleDb.OleDbCommandBuilder(da)
Dim dsNewRow As DataRow
dsNewRow = ds.Tables("Premier").NewRow()
panAdd.Visible = False
inc += 1
aTeams(inc) = txtTeam1.Text
aP(inc) = txtP1.Text
aHomeW(inc) = txtHomeW1.Text
aHomeD(inc) = txtHomeD1.Text
aHomeL(inc) = txtHomeL1.Text
aHomeF(inc) = txtHomeF1.Text
aHomeA(inc) = txtHomeA1.Text
aAwayW(inc) = txtAwayW1.Text
aAwayD(inc) = txtAwayD1.Text
aAwayL(inc) = txtAwayL1.Text
aAwayF(inc) = txtAwayF1.Text
aAwayA(inc) = txtAwayA1.Text
aGD(inc) = txtAwayGD1.Text
aPTS(inc) = txtAwayPTS1.Text
dsNewRow.Item("Team") = txtTeam1.Text
dsNewRow.Item("P") = txtP1.Text
dsNewRow.Item("Home W") = txtHomeW1.Text
dsNewRow.Item("Home D") = txtHomeD1.Text
dsNewRow.Item("Home L") = txtHomeL1.Text
dsNewRow.Item("Home F") = txtHomeF1.Text
dsNewRow.Item("Home A") = txtHomeA1.Text
dsNewRow.Item("Away W") = txtAwayW1.Text
dsNewRow.Item("Away D") = txtAwayD1.Text
dsNewRow.Item("Away L") = txtAwayL1.Text
dsNewRow.Item("Away F") = txtAwayF1.Text
dsNewRow.Item("Away A") = txtAwayA1.Text
dsNewRow.Item("GD") = txtAwayGD1.Text
dsNewRow.Item("PTS") = txtAwayPTS1.Text
ds.Tables("Premier").Rows.Add(dsNewRow)
da.Update(ds, "Premier")
MsgBox("New Record added to the Database")
End If
End Sub
'Quit Button, to Quit Program
Private Sub btnQuit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnQuit.Click
Me.Close()
End Sub
'Load Menu Item
Private Sub munLoad_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles munLoad.Click
btnLoad_Click(sender, e)
End Sub
'Display Menu Item
Private Sub mnuDisplay_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuDisplay.Click
btnDisplay_Click(sender, e)
End Sub
'Quit Menu Item
Private Sub mnuQuit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuQuit.Click
Me.Close()
End Sub
'New Record Menu Item
Private Sub mnuNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuNew.Click
btnNew_Click(sender, e)
End Sub
'Delete Record Menu Item
Private Sub mnuDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuDelete.Click
btnDelete_Click(sender, e)
End Sub
'Update Record Menu Item
Private Sub mnuUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuUpdate.Click
btnUpdate_Click(sender, e)
End Sub
'Clear Menu Item
Private Sub mnuClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuClear.Click
btnClear_Click(sender, e)
End Sub
'Confirm Menu Item
Private Sub mnuConfirm_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuConfirm.Click
btnAdd_Click(sender, e)
End Sub
'First Menu Item
Private Sub munFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles munFirst.Click
btnFirst_Click(sender, e)
End Sub
'Last Menu Item
Private Sub munLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles munLast.Click
btnLast_Click(sender, e)
End Sub
'Next Menu Item
Private Sub munNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles munNext.Click
btnNext_Click(sender, e)
End Sub
'Previous Menu Item
Private Sub mnuPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuPrevious.Click
btnPrevious_Click(sender, e)
End Sub
End Class
