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

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

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
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

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
> 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

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

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,
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