Question New Record/Edit/Delete

kieran82

Member
Joined
Feb 21, 2010
Messages
19
Programming Experience
Beginner
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
 
First up, this:
VB.NET:
btnLast_Click(sender, e)
should be this:
VB.NET:
btnLast.PerformClick()
As for the question, could you narrow down what:
can't get it to work
means. What exactly do you do and what exactly happens that you don't expect or what exactly doesn't happen that you do expect?
 
Back
Top