[RESOLVED] Dataset Problem - Deleting Row

[mouse88]

I am having problems deleting a row from my database. Here is my code:

Public Sub DeleteList(ByVal Text As String, ByVal Form As Form)

        If Text = "D" Then

            'Clear Dataset'

            Database.DataSet.Clear()


            'Store sql query in string variable for use with database connection sub'

            Dim sql As String = "SELECT * FROM " & DeleteListForm.WindscreenCompany


            'Call database connection sub and pass along above sql query'

            Call Database.DatabaseConnection(sql)


            'Call rowcount sub from database module to get tables rowcount'

            Dim RowCount As Integer = Database.RowCount


            'Loop through dataset table to match selected list on delete list form and when found delete record from dataset'

            Dim dbCommandBuilder As New OleDb.OleDbCommandBuilder(Database.DataAdapter)


            If DeleteListForm.IsAutowindscreens = True Then

                For i As Integer = 0 To RowCount - 1

                    If Database.DataSet.Tables("Windscreens").Rows(i).Item(0) = DeleteListForm.ListDate And Database.DataSet.Tables("Windscreens").Rows(i).Item(1) = DeleteListForm.Broker Then

                        Database.DataSet.Tables("Windscreens").Rows(i).Delete()


                        'Update database'

                        Database.DataAdapter.Update(Database.DataSet, "Windscreens")

                    End If

                Next i

            ElseIf DeleteListForm.IsAutowindscreens = False Then

                For i As Integer = 0 To RowCount - 1

                    If Database.DataSet.Tables("Windscreens").Rows(i).Item(0) = DeleteListForm.ListDate Then

                        Database.DataSet.Tables("Windscreens").Rows(i).Delete()


                        'Update database'

                        Database.DataAdapter.Update(Database.DataSet, "Windscreens")

                    End If

                Next i

            End If


            'Display messagebox confirming that list has been deleted'

            MessageBox.Show("List has been deleted", "Delete List", MessageBoxButtons.OK, MessageBoxIcon.Information)

Here is the database module that i am calling in the code:

Module Database

    'Declare varibales for database connection, connection string builder, dataset, database adapter and sql command'

    Public dbConnection As New OleDb.OleDbConnection
    Public dbConnectionStringBuilder As New OleDb.OleDbConnectionStringBuilder
    Public DataSet As New DataSet
    Public DataAdapter As OleDb.OleDbDataAdapter
    Public sql As String

    Public Sub DatabaseConnection(ByVal sql As String)

        'Provide data source to database connection builders connection string'

        dbConnectionStringBuilder.ConnectionString = "Data Source = " & LoginForm.Filename


        'Add database password and provider to connectionbuilder and then load into dbconnectionstring'

        dbConnectionStringBuilder.Add("Provider", "Microsoft.Jet.Oledb.4.0")
        dbConnectionStringBuilder.Add("Jet OLEDB:Database Password", "A5B7C5D8E4")
        dbConnection.ConnectionString = dbConnectionStringBuilder.ConnectionString


        'Open database connection, use sql query to obtain required data and fill dataset'

        dbConnection.Open()

        DataAdapter = New OleDb.OleDbDataAdapter(sql, dbConnection)

        dbConnection.Close()

        DataAdapter.Fill(DataSet, "Windscreens")

    End Sub

    Public Function RowCount() As Integer

        'Get rowcount of data set and pass to RowCount functions returned value'

        RowCount = DataSet.Tables("Windscreens").Rows.Count

    End Function

End Module

The problem is i am getting my message box displayed confirming that the record has been deleted but when i check the database it is still there so for some reason it is not updating properly. Any idea why this may be because this usually works all the time for me?

[techgnome]

Put a breakpoint on your delete statements... make sure you are actually executing them....

-tg

[mouse88]

Ye i have tried this, for some reason they are not being executed but ive checked my variables and they do match the data in the database so it should be executing. Think im gna have to recode it and try again, im really stumped on this one.

[techgnome]

Then something isn't rightwith the logic.

I'd start by putting a breakpoint on this line:
If Text = "D" Then

And stepping through each line, chekcing values and variables, and making sure that you arte getting what you expect, when you expect it.

-tg

[mouse88]

Ok problem now solved it wasn't executing correctly because my variables were in the wrong palce and were blank when my module was being called. Then it started throwing an exception which was because i didnt have a primary key on my table for some reason. Is it always a good idea to have a primary key in your table?

[techgnome]

Generally speaking, yes... that's what tells the database how to uniquely identify each row in the table.

-tg