How to Delete Selected Row in DatagridView and Ms Access

[Jommaria03]

This is the Code:
When I select the row in datagridview, and click on delete button it doesn't delete.
Am I wrong in the code ?

Private Sub Save_Image()
Dim conn As New OleDbConnection
Dim sConnString As String
Dim cmd As New OleDbCommand
Dim sSQL As String = String.Empty
Dim arrImage() As Byte
Dim myMs As New IO.MemoryStream
Dim bSaveImage As Boolean = False
Dim strImg As String = String.Empty


Try
If Not IsNothing(Me.myimage.Image) Then
Me.myimage.Image.Save(myMs, Me.myimage.Image.RawFormat)
arrImage = myMs.GetBuffer
bSaveImage = True
Else
arrImage = Nothing
bSaveImage = False
End If
If bSaveImage = True Then
If Microsoft.VisualBasic.Right(Application.StartupPat h, 1) = "\" Then
sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "SampleDB.accdb;Persist Security Info=False;"
Else
sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\SampleDB.accdb;Persist Security Info=False;"
End If
conn = New OleDbConnection(sConnString)
conn.Open()
cmd.Connection = conn
cmd.CommandType = CommandType.Text
If Me.txtDescription.Tag = 0 Then
cmd.CommandText = "INSERT INTO tbl_image (image_description, [image] ) VALUES(@img_desc, @img)"
Else
cmd.CommandText = "UPDATE tbl_image set image_description = @img_desc, [image] = @img where id=@id"
End If
cmd.Parameters.Add("@img_desc", OleDbType.VarChar).Value = Me.txtDescription.Text
cmd.Parameters.Add("@img", OleDb.OleDbType.Binary).Value = arrImage
cmd.Parameters.Add("@id", OleDbType.BigInt).Value = Me.txtDescription.Tag
cmd.ExecuteNonQuery()
MsgBox("Image has been save.")
Else
MsgBox("Please add an image")
End If
Catch ex As Exception
MsgBox(ErrorToString)
Finally
conn.Close()
End Try
End Sub




Private Sub Load_Image(ByVal iIMageID As Long)
Dim conn As New OleDbConnection
Dim sConnString As String
Dim cmd As New OleDbCommand
Dim dr As OleDbDataReader
Dim arrImage() As Byte
Dim myMS As New IO.MemoryStream


Try
If Microsoft.VisualBasic.Right(Application.StartupPat h, 1) = "\" Then
sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "SampleDB.accdb;Persist Security Info=False;"
Else
sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\SampleDB.accdb;Persist Security Info=False;"
End If
conn = New OleDbConnection(sConnString)
conn.Open()
cmd.Connection = conn
cmd.CommandType = CommandType.Text
cmd.CommandText = "select id, image_description as description, [image] from tbl_image where id= " & iIMageID


dr = cmd.ExecuteReader
If dr.HasRows Then
While dr.Read
Me.txtDescription.Tag = dr("id")
Me.txtDescription.Text = dr("description")
arrImage = dr("image")
For Each ar As Byte In arrImage
myMS.WriteByte(ar)
Next
'
Me.myimage.Image = System.Drawing.Image.FromStream(myMS)
End While
End If


Catch ex As Exception
MsgBox(ErrorToString)
Finally
conn.Close()
End Try
End Sub

'PROBLEM EXISTS HERE
Private Sub Delete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
Dim SqlQuery As String = "DELETE FROM tbl_image WHERE ID = " & Me.DataGridView1.SelectedRows(0).Cells(0).Value.To String & ";"
Dim SqlCommand As New OleDbCommand
Dim conn As New OleDbConnection




With SqlCommand
.CommandText = SqlQuery
.Connection = conn
.ExecuteNonQuery()


End With


MsgBox("Your record is successfully deleted!")
End Sub



Private Sub Load_GridView()
Dim conn As New OleDbConnection
Dim sConnString As String
Dim cmd As New OleDbCommand
Dim da As New OleDbDataAdapter
Dim dt As New DataTable


Try
If Microsoft.VisualBasic.Right(Application.StartupPat h, 1) = "\" Then
sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "SampleDB.accdb;Persist Security Info=False;"
Else
sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\SampleDB.accdb;Persist Security Info=False;"
End If
conn = New OleDbConnection(sConnString)
conn.Open()
cmd.Connection = conn
cmd.CommandType = CommandType.Text
cmd.CommandText = "select id, image_description as description from tbl_image"
da.SelectCommand = cmd
da.Fill(dt)
Me.DataGridView1.DataSource = dt
Catch ex As Exception
MsgBox(ErrorToString)
Finally
conn.Close()
End Try
End Sub




Private Sub DataGridView1_DoubleClick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DataGridView1.DoubleClick
Load_Image(Me.DataGridView1.Item(0, Me.DataGridView1.CurrentRow.Index).Value)
End Sub

[June7]

VBA doesn't allow variables to be declared and set in the same statement. Did you get this code from a VB.Net example?

Dim SqlQuery As String
SqlQuery = "DELETE FROM tbl_image WHERE ID = " & Me.DataGridView1.SelectedRows(0).Cells(0).Value.To String & ";"

Is that code behind a command button? Try:

Private Sub Delete_Click()
CurrentDb.Execute "DELETE FROM tbl_image WHERE ID=" & Me.DataGridView1.SelectedRows(0).Cells(0).Value.To String
MsgBox "Deleted"
End Sub

I've never used a DataGridView, so not sure about the syntax of .Value.To String

[Jommaria03]

I have a new code:
but it still doesn't work, check out the redcode below
it says 'Column named ID cannot be found.
'Parameter name: columnName
If MsgBox("There is no undo once record(s) is deleted..." & vbNewLine & "Are you sure you want to delete?", MsgBoxStyle.Question + MsgBoxStyle.YesNo + MsgBoxStyle.DefaultButton2, "Delete confirmation...") = MsgBoxResult.No Then
MsgBox("Operation canceled...", MsgBoxStyle.Information, "Canceled...")
Exit Sub
End If
If Me.DataGridView1.Rows.Count > 0 Then


End If
If Me.DataGridView1.SelectedRows.Count > 0 Then
Dim intID As Integer = Me.DataGridView1.SelectedRows(0).Cells("ID").Value
'open connection
If Not conn.State = ConnectionState.Open Then
conn.Open()
End If


'delete data
Dim cmd As New OleDb.OleDbCommand
cmd.Connection = conn
cmd.CommandText = "DELETE FROM tbl_image WHERE ID=" & intID
cmd.ExecuteNonQuery()
'refresh data




'close connection
conn.Close()
End If

[Jommaria03]

How to delete the selected rows in datagridview and also be deleted in Ms access. The code here only deletes the record one by one starting from the first row not when selecting a row.
I don't know what code to add. please help...



Private Sub DeleteData()
Dim conn As New OleDbConnection
Dim sConnString As String
Dim cmd As New OleDbCommand
Dim da As New OleDbDataAdapter
Dim dt As New DataTable


Try
If Microsoft.VisualBasic.Right(Application.StartupPat h, 1) = "\" Then
sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "SampleDB.accdb;Persist Security Info=False;"
Else
sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\SampleDB.accdb;Persist Security Info=False;"
End If
conn = New OleDbConnection(sConnString)
conn.Open()
cmd.Connection = conn
cmd.CommandType = CommandType.Text
cmd.CommandText = "select id, image_description as description from tbl_image"
da.SelectCommand = cmd


da.Fill(dt)


Me.DataGridView1.DataSource = dt


dt.Rows(0).BeginEdit()
dt.Rows(0).Delete()
dt.Rows(0).EndEdit()






Dim cb As New OleDbCommandBuilder(da)


da.Update(dt)
Me.DataGridView1.DataSource = dt.DefaultView


Catch ex As Exception
MsgBox(ErrorToString)
Finally
conn.Close()
End Try
End Sub

[June7]

Sorry, I've never used a GridView.

Do you actually have a field named ID used in the GridView setup?

[June7]

I have never seen Try End Try.

Do you want to provide db for analysis? follow instructions at bottom of my post.

[Jommaria03]

How to delete the selected rows in datagridview and also be deleted in Ms access. The code here only deletes the record one by one starting from the first row not when selecting a row.
I don't know what code to add. please help...



Private Sub DeleteData()
Dim conn As New OleDbConnection
Dim sConnString As String
Dim cmd As New OleDbCommand
Dim da As New OleDbDataAdapter
Dim dt As New DataTable


Try
If Microsoft.VisualBasic.Right(Application.StartupPat h, 1) = "\" Then
sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "SampleDB.accdb;Persist Security Info=False;"
Else
sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\SampleDB.accdb;Persist Security Info=False;"
End If
conn = New OleDbConnection(sConnString)
conn.Open()
cmd.Connection = conn
cmd.CommandType = CommandType.Text
cmd.CommandText = "select id, image_description as description from tbl_image"
da.SelectCommand = cmd


da.Fill(dt)


Me.DataGridView1.DataSource = dt


dt.Rows(0).BeginEdit()
dt.Rows(0).Delete()
dt.Rows(0).EndEdit()






Dim cb As New OleDbCommandBuilder(da)


da.Update(dt)
Me.DataGridView1.DataSource = dt.DefaultView


Catch ex As Exception
MsgBox(ErrorToString)
Finally
conn.Close()
End Try
End Sub

This is my current code. I'm connecting vb.net to ms access. I want to delete a selected row in datagridview and totally delete them from datagrid and ms access database. My current code automatically deletes the row(0), but I want to be the one to choose what to delete.

[June7]

So this is not even VBA behind Access, it is VB.net project? Perhaps you would have more success in a VB.net forum.

[Jommaria03]

yes it's a vb.net project