Trouble deleting a record from an Access database using VB.net 2005

Trouble deleting a record from an Access database using VB.net 2005

Post by joshturner196 » Sat, 18 Oct 2008 04:38:51



I am having trouble deleting a record from an Access database using
VB.net

The code compiles and the record that I want is selected but when I
delete the record it never goes away what am I doing wrong - obviously
missing a step  I do not get any errors I have 21 records to begin
with and when I check it after the delete I still have 21 - any ideas?

here is my event code
Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient

Public Class UserMaint

Private Sub BtnDelete_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles BtnDelete.Click

Dim con As New OleDb.OleDbConnection
    Dim ds As DataSet = New DataSet
    Dim reccount As Integer

        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\TestTheory\Maintapp\Maintapp\mymainttemp.mdb;Persist
Security Info=False"

        Try

            con.Open()

        Catch OleDbExceptionErr As OleDb.OleDbException

            MessageBox.Show(OleDbExceptionErr.Message)

        End Try

        'If row is selected continue '
        If Me.DataGridView1.SelectedRows.Count = 1 Then

            Dim dr As DataGridViewRow =
Me.DataGridView1.SelectedRows.Item(0)
            MsgBox(dr.Cells(0).Value)

            ' Create the SelectCommand.
            Dim sql As String = "SELECT * FROM USR"

            ' Create the Delete Statment based upon the user id from
the selected row.
            Dim deleteSql = New OleDbCommand("DELETE FROM USR WHERE
USER_ID = dr.Cells(0).Value")

            Try
                Dim da As OleDbDataAdapter = New OleDbDataAdapter(sql,
con)
                da.Fill(ds, "USR")

                Dim dt As DataTable = ds.Tables("USR")
                da.DeleteCommand = deleteSql

                da.Update(ds, "USR")
                ds.AcceptChanges()

                Dim scalarCommand As New OleDbCommand("SELECT COUNT(*)
FROM USR", con)

                USRBindingSource.DataSource = ds.DefaultViewManager
                MsgBox("After Delete, Number of Employee = " &
scalarCommand.ExecuteScalar())

                DataGridView1.Refresh()

            Catch ex As SqlException

                MsgBox("Error: " & ex.ToString())

                ' MsgBox("Error during delete of USR Table for " &
dr.Cells(0).Value)

            End Try

        Else
            MsgBox("Row not selected")

        End If

    End Sub
End Class

Thanks Josh

 
 
 

Trouble deleting a record from an Access database using VB.net 2005

Post by Glen » Sat, 18 Oct 2008 20:01:40


I'm not familiar with the inner workings of Access, but with many other
database systems the record is not actually deleted.  Rather it is just
marked for deletion so that it can be recovered if needed.  Check the Access
documentation on record deletion and see what it says.

...Glenn


Quote:

> I am having trouble deleting a record from an Access database using
> VB.net

> The code compiles and the record that I want is selected but when I
> delete the record it never goes away what am I doing wrong - obviously
> missing a step  I do not get any errors I have 21 records to begin
> with and when I check it after the delete I still have 21 - any ideas?

> here is my event code
> Imports System.Data
> Imports System.Data.OleDb
> Imports System.Data.SqlClient

> Public Class UserMaint

> Private Sub BtnDelete_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles BtnDelete.Click

> Dim con As New OleDb.OleDbConnection
>    Dim ds As DataSet = New DataSet
>    Dim reccount As Integer

>        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
> Source=C:\TestTheory\Maintapp\Maintapp\mymainttemp.mdb;Persist
> Security Info=False"

>        Try

>            con.Open()

>        Catch OleDbExceptionErr As OleDb.OleDbException

>            MessageBox.Show(OleDbExceptionErr.Message)

>        End Try

>        'If row is selected continue '
>        If Me.DataGridView1.SelectedRows.Count = 1 Then

>            Dim dr As DataGridViewRow =
> Me.DataGridView1.SelectedRows.Item(0)
>            MsgBox(dr.Cells(0).Value)

>            ' Create the SelectCommand.
>            Dim sql As String = "SELECT * FROM USR"

>            ' Create the Delete Statment based upon the user id from
> the selected row.
>            Dim deleteSql = New OleDbCommand("DELETE FROM USR WHERE
> USER_ID = dr.Cells(0).Value")

>            Try
>                Dim da As OleDbDataAdapter = New OleDbDataAdapter(sql,
> con)
>                da.Fill(ds, "USR")

>                Dim dt As DataTable = ds.Tables("USR")
>                da.DeleteCommand = deleteSql

>                da.Update(ds, "USR")
>                ds.AcceptChanges()

>                Dim scalarCommand As New OleDbCommand("SELECT COUNT(*)
> FROM USR", con)

>                USRBindingSource.DataSource = ds.DefaultViewManager
>                MsgBox("After Delete, Number of Employee = " &
> scalarCommand.ExecuteScalar())

>                DataGridView1.Refresh()

>            Catch ex As SqlException

>                MsgBox("Error: " & ex.ToString())

>                ' MsgBox("Error during delete of USR Table for " &
> dr.Cells(0).Value)

>            End Try

>        Else
>            MsgBox("Row not selected")

>        End If

>    End Sub
> End Class

> Thanks Josh


 
 
 

Trouble deleting a record from an Access database using VB.net 2005

Post by sallusha » Sun, 19 Oct 2008 00:45:49


   Dim deleteSql = New OleDbCommand("DELETE FROM USR WHERE
USER_ID = dr.Cells(0).Value")

you have put "dr.cells(0).value" in the string instead it should be
concatenated. Change this to,

Dim deleteSql = New OleDbCommand("DELETE FROM USR WHERE
USER_ID = " & dr.Cells(0).Value)

If User_ID is string then use the following statement,

Dim deleteSql = New OleDbCommand("DELETE FROM USR WHERE
USER_ID = '" & dr.Cells(0).Value.ToString().Replace("'", "''") & "'")

BTW try using Parameters, since that is a safer method.


Quote:> I am having trouble deleting a record from an Access database using
> VB.net

> The code compiles and the record that I want is selected but when I
> delete the record it never goes away what am I doing wrong - obviously
> missing a step ?I do not get any errors I have 21 records to begin
> with and when I check it after the delete I still have 21 - any ideas?

> here is my event code
> Imports System.Data
> Imports System.Data.OleDb
> Imports System.Data.SqlClient

> Public Class UserMaint

> Private Sub BtnDelete_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles BtnDelete.Click

> Dim con As New OleDb.OleDbConnection
> ? ? Dim ds As DataSet = New DataSet
> ? ? Dim reccount As Integer

> ? ? ? ? con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
> Source=C:\TestTheory\Maintapp\Maintapp\mymainttemp.mdb;Persist
> Security Info=False"

> ? ? ? ? Try

> ? ? ? ? ? ? con.Open()

> ? ? ? ? Catch OleDbExceptionErr As OleDb.OleDbException

> ? ? ? ? ? ? MessageBox.Show(OleDbExceptionErr.Message)

> ? ? ? ? End Try

> ? ? ? ? 'If row is selected continue '
> ? ? ? ? If Me.DataGridView1.SelectedRows.Count = 1 Then

> ? ? ? ? ? ? Dim dr As DataGridViewRow =
> Me.DataGridView1.SelectedRows.Item(0)
> ? ? ? ? ? ? MsgBox(dr.Cells(0).Value)

> ? ? ? ? ? ? ' Create the SelectCommand.
> ? ? ? ? ? ? Dim sql As String = "SELECT * FROM USR"

> ? ? ? ? ? ? ' Create the Delete Statment based upon the user id from
> the selected row.
> ? ? ? ? ? ? Dim deleteSql = New OleDbCommand("DELETE FROM USR WHERE
> USER_ID = dr.Cells(0).Value")

> ? ? ? ? ? ? Try
> ? ? ? ? ? ? ? ? Dim da As OleDbDataAdapter = New OleDbDataAdapter(sql,
> con)
> ? ? ? ? ? ? ? ? da.Fill(ds, "USR")

> ? ? ? ? ? ? ? ? Dim dt As DataTable = ds.Tables("USR")
> ? ? ? ? ? ? ? ? da.DeleteCommand = deleteSql

> ? ? ? ? ? ? ? ? da.Update(ds, "USR")
> ? ? ? ? ? ? ? ? ds.AcceptChanges()

> ? ? ? ? ? ? ? ? Dim scalarCommand As New OleDbCommand("SELECT COUNT(*)
> FROM USR", con)

> ? ? ? ? ? ? ? ? USRBindingSource.DataSource = ds.DefaultViewManager
> ? ? ? ? ? ? ? ? MsgBox("After Delete, Number of Employee = " &
> scalarCommand.ExecuteScalar())

> ? ? ? ? ? ? ? ? DataGridView1.Refresh()

> ? ? ? ? ? ? Catch ex As SqlException

> ? ? ? ? ? ? ? ? MsgBox("Error: " & ex.ToString())

> ? ? ? ? ? ? ? ? ' MsgBox("Error during delete of USR Table for " &
> dr.Cells(0).Value)

> ? ? ? ? ? ? End Try

> ? ? ? ? Else
> ? ? ? ? ? ? MsgBox("Row not selected")

> ? ? ? ? End If

> ? ? End Sub
> End Class

> Thanks Josh

 
 
 

Trouble deleting a record from an Access database using VB.net 2005

Post by sallusha » Sun, 19 Oct 2008 00:57:40


Also your coding seems to be confusing. Try creating Typed DataSet and
TableAdapters using Data Wizards and use Proper Data Binding.

The confusing parts,

1) Why are you filling the Table again if it is already filled as you
are selecting the row from DataGridView control
2) You are sending the Table in "Update" method of DataAdapter,
without changing your Table. Note that DataAdapter only updates the
DataRow(s) which RowStatus() is changed i.e. (Deleted, Added,
Updated).
3) Proper Column Mapping is NOT done therefore Row(s) may NOT Delete
anyway


Quote:> I am having trouble deleting a record from an Access database using
> VB.net

> The code compiles and the record that I want is selected but when I
> delete the record it never goes away what am I doing wrong - obviously
> missing a step ?I do not get any errors I have 21 records to begin
> with and when I check it after the delete I still have 21 - any ideas?

> here is my event code
> Imports System.Data
> Imports System.Data.OleDb
> Imports System.Data.SqlClient

> Public Class UserMaint

> Private Sub BtnDelete_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles BtnDelete.Click

> Dim con As New OleDb.OleDbConnection
> ? ? Dim ds As DataSet = New DataSet
> ? ? Dim reccount As Integer

> ? ? ? ? con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
> Source=C:\TestTheory\Maintapp\Maintapp\mymainttemp.mdb;Persist
> Security Info=False"

> ? ? ? ? Try

> ? ? ? ? ? ? con.Open()

> ? ? ? ? Catch OleDbExceptionErr As OleDb.OleDbException

> ? ? ? ? ? ? MessageBox.Show(OleDbExceptionErr.Message)

> ? ? ? ? End Try

> ? ? ? ? 'If row is selected continue '
> ? ? ? ? If Me.DataGridView1.SelectedRows.Count = 1 Then

> ? ? ? ? ? ? Dim dr As DataGridViewRow =
> Me.DataGridView1.SelectedRows.Item(0)
> ? ? ? ? ? ? MsgBox(dr.Cells(0).Value)

> ? ? ? ? ? ? ' Create the SelectCommand.
> ? ? ? ? ? ? Dim sql As String = "SELECT * FROM USR"

> ? ? ? ? ? ? ' Create the Delete Statment based upon the user id from
> the selected row.
> ? ? ? ? ? ? Dim deleteSql = New OleDbCommand("DELETE FROM USR WHERE
> USER_ID = dr.Cells(0).Value")

> ? ? ? ? ? ? Try
> ? ? ? ? ? ? ? ? Dim da As OleDbDataAdapter = New OleDbDataAdapter(sql,
> con)
> ? ? ? ? ? ? ? ? da.Fill(ds, "USR")

> ? ? ? ? ? ? ? ? Dim dt As DataTable = ds.Tables("USR")
> ? ? ? ? ? ? ? ? da.DeleteCommand = deleteSql

> ? ? ? ? ? ? ? ? da.Update(ds, "USR")
> ? ? ? ? ? ? ? ? ds.AcceptChanges()

> ? ? ? ? ? ? ? ? Dim scalarCommand As New OleDbCommand("SELECT COUNT(*)
> FROM USR", con)

> ? ? ? ? ? ? ? ? USRBindingSource.DataSource = ds.DefaultViewManager
> ? ? ? ? ? ? ? ? MsgBox("After Delete, Number of Employee = " &
> scalarCommand.ExecuteScalar())

> ? ? ? ? ? ? ? ? DataGridView1.Refresh()

> ? ? ? ? ? ? Catch ex As SqlException

> ? ? ? ? ? ? ? ? MsgBox("Error: " & ex.ToString())

> ? ? ? ? ? ? ? ? ' MsgBox("Error during delete of USR Table for " &
> dr.Cells(0).Value)

> ? ? ? ? ? ? End Try

> ? ? ? ? Else
> ? ? ? ? ? ? MsgBox("Row not selected")

> ? ? ? ? End If

> ? ? End Sub
> End Class

> Thanks Josh