[naveed_786]

I am trying to delete selected row from datagrid and commit changes permanently in my DB here is my code but i am unable to delete record

 Private Sub btndelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndelete.Click
        Try
            DataGridView1.Rows.Remove(DataGridView1.SelectedRows(0))
            da.Update(dsOle)
            MessageBox.Show("success!")

        Catch ex As Exception

            MessageBox.Show("fail")

        End Try
    End Sub

Thanks for help in advance.

[smohd]

What is happening? it should work if dsOle is a dataset you use to set value to data grid view. Also you may try this:

 da.Update(dsOle,"Table name") 

to see if it will help

[noorahmad]

You should updating your DataSet after deleting row from datagridview.


A Quick Example:

    Dim con As New SqlConnection
    Dim ds As New DataSet
    Dim da As New SqlDataAdapter
    Dim cmd As New SqlCommandBuilder

    Sub LoadData()
        con.Open()
        da = New SqlDataAdapter("SELECT * FROm Employees", con)
        cmd = New SqlCommandBuilder(da)
        da.DeleteCommand = cmd.GetDeleteCommand()
        da.UpdateCommand = cmd.GetUpdateCommand()
        da.InsertCommand = cmd.GetInsertCommand()
        da.Fill(ds, "Employees")
        DataGridView1.DataSource = ds.Tables("Employees")
    End Sub

    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        Try
            Dim i As Integer = DataGridView1.SelectedRows(0).Index
            DataGridView1.Rows.Remove(DataGridView1.SelectedRows(0))
            ds.Tables(0).Rows(i).Delete()
            da.Update(ds, "Employees")
            MessageBox.Show("Success!")
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

[smohd]

Yap, I forget about removing the row from ds. Good catch NoorAhmad

[naveed_786]

Thanks a lot its working but i when i delete the record it didn't clear the data grid i.e when i select a record and press delete button it should also remove the record from data grid i mean i need to load again the data grid to see that record exists or removed.

[noorahmad]

Create a function do load your data from database to datagridview and call after deleteing row from DGV.

    Sub LoadData()
        da = New SqlDataAdapter("SELECT * FROm Employees", con)
        cmd = New SqlCommandBuilder(da)
        da.DeleteCommand = cmd.GetDeleteCommand()
        da.UpdateCommand = cmd.GetUpdateCommand()
        da.InsertCommand = cmd.GetInsertCommand()
        da.Fill(ds, "Employees")
        DataGridView1.DataSource = ds.Tables("Employees")
    End Sub

' btnDelete....
da.Update(ds, "Employees")
LoadData()
'...

[naveed_786]

As per your instruction i have changed peace of code but when i search the record and click on delete button it gives me following error
"update required a valid delete command when pass datarow with deleted rows collection error message."

What should i do???

[fixo]

I had gathered from nooarhmad's example and from my
own code, almost not tested
try to delete record in the middle of grid
See if this helps, not sure about though :)

Partial Public Class Form1

    '   *** http://www.vb-net.ru/dotnet/AdoNet.htm  ***
    Private SqlConn As SqlConnection
    Private SqlDa As SqlDataAdapter
    Private SqlDt As DataTable
    Private SqlDs As New DataSet
    Private SqlCmd As New SqlCommandBuilder
    Private SqlBind As New BindingSource
    Public Sub New()

        Me.InitializeComponent()

    End Sub
''------------------------------------------------
    Sub LoadData()
        Dim SqlSb As New SqlConnectionStringBuilder()
        SqlSb.DataSource = ".\SQLEXPRESS"
        SqlSb.InitialCatalog = "Geraint"
        SqlSb.IntegratedSecurity = True
        SqlConn = New SqlConnection(SqlSb.ConnectionString)
        SqlConn.Open()
        SqlDa = New SqlDataAdapter("select COUNTRY_ID, COUNTRY_CODE, COUNTRY_NAME from COUNTRY", SqlConn)
        SqlCmd = New SqlCommandBuilder(SqlDa)
        SqlDa.DeleteCommand = SqlCmd.GetDeleteCommand()
        SqlDa.UpdateCommand = SqlCmd.GetUpdateCommand()
        SqlDa.InsertCommand = SqlCmd.GetInsertCommand()
        If SqlDs.Tables("COUNTRY") IsNot Nothing Then
            SqlDs.Tables("COUNTRY").Clear()
        End If
        SqlDa.Fill(SqlDs, "COUNTRY")
        SqlBind.DataSource = SqlDs.Tables("COUNTRY")
        Me.dgv.DataSource = SqlBind
    End Sub
    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        Try
            Dim i As Integer = Me.dgv.CurrentRow.Index
            Dim id As Object = Me.dgv.CurrentRow.Cells(0).Value           
            Dim SqlSb As New SqlConnectionStringBuilder()
            SqlSb.DataSource = ".\SQLEXPRESS"
            SqlSb.InitialCatalog = "Geraint"
            SqlSb.IntegratedSecurity = True
            SqlConn = New SqlConnection(SqlSb.ConnectionString)
            SqlConn.Open()
            SqlDa = New SqlDataAdapter()
            Dim strSQL As String = "delete from COUNTRY where (COUNTRY_ID=@COUNTRY_ID);"
            SqlDa.DeleteCommand = New SqlCommand(strSQL, SqlConn)
            SqlDa.DeleteCommand.Parameters.Add("@COUNTRY_ID", SqlDbType.Int, 10, "COUNTRY_ID")
            SqlDa.DeleteCommand.Parameters("@COUNTRY_ID").SourceVersion = DataRowVersion.Current
            SqlDa.DeleteCommand.Parameters("@COUNTRY_ID").Value = id
            SqlDa.DeleteCommand.ExecuteNonQuery()
            SqlConn.Close()
            Me.dgv.Rows.Remove(Me.dgv.CurrentRow)
            LoadData()
            MessageBox.Show("Success!")
        Catch ex As Exception
            MessageBox.Show(ex.Message & vbLf & ex.StackTrace)
        End Try
    End Sub

'add event on removing the row
    Private Sub dgv_RowsRemoved(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewRowsRemovedEventArgs) Handles dgv.RowsRemoved
        Try
            If Me.dgv.SelectedRows.Count = 1 Then
                Dim i As Integer = dgv.CurrentRow.Index + 1
                SqlDs.Tables("COUNTRY").Rows(i).Delete()
                SqlDa.Update(SqlDs, "COUNTRY")
                LoadData()
                MessageBox.Show("Success!")
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message & vbLf & ex.StackTrace)
        End Try
    End Sub

[naveed_786]

I have resolved the issue by making changes in this way

Try
            Dim a As Integer
            a = MessageBox.Show("Are you sure you want to exit????", "ITPCHR", MessageBoxButtons.YesNo, MessageBoxIcon.Stop)
            If a = vbYes Then
                Dim i As Integer = DataGridView1.SelectedRows(0).Index
                DataGridView1.Rows.Remove(DataGridView1.SelectedRows(0))
                dsOle.Tables(0).Rows(i).Delete()
                LoadData()
                da.Update(dsOle, "EQUIPMENT_INV")
                DataGridView1.DataSource = Nothing
                DataGridView1.Rows.Clear()
                MessageBox.Show("Record has been Successfully Deleted", "ITPCHR", MessageBoxButtons.OK, MessageBoxIcon.Information)
                txtcriteria.Text = ""
            Else
                Exit Sub
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

But now i am facing one more problem when i select record to delete i am able to delete and the data grid clears up,after this i enter the criteria in text box click on search button it returns me this error

"Not allowed to change the 'ConnectionString' property. The connection's current state is open."

on this line

con.ConnectionString = ConnectionString

Private Sub btnsearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsearch.Click
        con.ConnectionString = ConnectionString
        Try
            dsOle.Clear()
            dtOle.Clear()
            cmdOle = con.CreateCommand
            cmdOle.CommandText = "SELECT * FROM EQUIPMENT_INV WHERE LOWER(ITEM_TYPE) LIKE '%" & Trim(txtcriteria.Text) & "%'"
            da.SelectCommand = cmdOle
            da.Fill(dsOle, "EQUIPMENT_INV")
            DataGridView1.DataSource = dsOle
            DataGridView1.DataMember = "EQUIPMENT_INV"
            If dsOle.Tables("EQUIPMENT_INV").Rows.Count = 0 Then
                MessageBox.Show("No such record exists!", "ITPCHR", MessageBoxButtons.OK, MessageBoxIcon.Stop)
                txtcriteria.Text = ""
            End If
        Catch ex As Exception
            If ConnectionState.Open Then
                con.Close()
            End If
            MsgBox("Error: " & ex.Source & ": " & ex.Message, MsgBoxStyle.OkOnly, "NO DATA FOUND !!")
        End Try
    End Sub

What should i do to resolve this problem.

[noorahmad]

Before updating table you are loading data to dataset and it will not update your table

'....
dsOle.Tables(0).Rows(i).Delete()
LoadData() // Here..
da.Update(dsOle, "EQUIPMENT_INV")
'...

It should be

dsOle.Tables(0).Rows(i).Delete()
da.Update(dsOle, "EQUIPMENT_INV")
LoadData() // Load data after updating the tabel

You can not change or assign new connection string to connection object when is open try to check for connection status before opening

        If con.State = ConnectionState.Closed Then
            con.Open()
        End If

[fixo]

Try again

Public Class Form1
    Private dtOle As DataTable
    Private dsOle As DataSet
    Private da As OleDbDataAdapter
    Private con As OleDbConnection
    Private cmdOle As OleDbCommand
    Private conStr As String


    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load ''OK
        LoadData()
    End Sub


    Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click

        Dim criteria As String = txtcriteria.Text.ToLower
        Dim OleDbSb As New OleDbConnectionStringBuilder()
        OleDbSb.Provider = "Microsoft.ACE.OLEDB.12.0"
        OleDbSb.DataSource = "C:\Users\Test\Desktop\data.mdb"
        OleDbSb.PersistSecurityInfo = True
        conStr = OleDbSb.ConnectionString
        Using con = New OleDbConnection(conStr)
            Try
                dsOle.Clear()
                dtOle.Clear()
                cmdOle = con.CreateCommand
                '---> it's not working on my machine
                'cmdOle.CommandText = "SELECT * FROM EQUIPMENT_INV WHERE (LOWER(ITEM_TYPE) LIKE '%" & criteria & "%')  ORDER BY ITEM_ID"
                '---> it's working good for me
                cmdOle.CommandText = "SELECT * FROM EQUIPMENT_INV WHERE LCASE(ITEM_TYPE) LIKE '%" & criteria & "%'  ORDER BY ITEM_ID"
                da.SelectCommand = cmdOle
                da.Fill(dsOle, "EQUIPMENT_INV")
                DataGridView1.DataSource = dsOle
                DataGridView1.DataMember = "EQUIPMENT_INV"
                If dsOle.Tables("EQUIPMENT_INV").Rows.Count = 0 Then
                    MessageBox.Show("No such record exists!", "ITPCHR", MessageBoxButtons.OK, MessageBoxIcon.Stop)
                    txtcriteria.Text = ""
                End If
                DataGridView1.DataSource = bindingSource1
            Catch ex As Exception
                MsgBox("Error: " & ex.Message & ": " & ex.StackTrace, MsgBoxStyle.OkOnly, "NO DATA FOUND !!")
            Finally
                If ConnectionState.Open Then
                    con.Close()
                End If
            End Try
        End Using
    End Sub

    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        Dim i As Integer = Me.DataGridView1.CurrentRow.Index
        Dim OleDbSb As New OleDbConnectionStringBuilder()
        OleDbSb.Provider = "Microsoft.ACE.OLEDB.12.0"
        OleDbSb.DataSource = "C:\Users\Test\Desktop\data.mdb"
        OleDbSb.PersistSecurityInfo = True
        Using con = New OleDbConnection(OleDbSb.ConnectionString)

            con.Open()
            Dim strSQL As String = ""
            da = New OleDbDataAdapter()

            strSQL = "SELECT ITEM_ID,ITEM_TYPE FROM EQUIPMENT_INV"
            da.SelectCommand = New OleDbCommand(strSQL, con)

            strSQL = "delete from EQUIPMENT_INV where ITEM_ID=" & Me.DataGridView1.CurrentRow.Cells(0).Value & ""
            da.DeleteCommand = New OleDbCommand(strSQL, con)

            da.DeleteCommand.ExecuteNonQuery()

            dsOle.Tables("EQUIPMENT_INV").Rows(i).Delete()
            dsOle.Tables("EQUIPMENT_INV").AcceptChanges()
            da.Update(dsOle, "EQUIPMENT_INV")

            con.Close()

        End Using
        '' Load data after updating the table
        LoadData()

    End Sub


    Sub LoadData()
      Dim OleDbSb As New OleDbConnectionStringBuilder()
        OleDbSb.Provider = "Microsoft.ACE.OLEDB.12.0"
        OleDbSb.DataSource = "C:\Users\Test\Desktop\data.mdb"
        OleDbSb.PersistSecurityInfo = True
        conStr = OleDbSb.ConnectionString
        Using con = New OleDbConnection(conStr)
            con.Open()
            da = New OleDbDataAdapter("SELECT * FROM EQUIPMENT_INV", con)
            dsOle = New DataSet
            dtOle = New DataTable
            If dsOle.Tables("EQUIPMENT_INV") IsNot Nothing Then
                dsOle.Tables("EQUIPMENT_INV").Clear()
            End If
            da.Fill(dsOle, "EQUIPMENT_INV")
            dtOle = dsOle.Tables("EQUIPMENT_INV")
            Me.bindingSource1.DataSource = dtOle
            Me.DataGridView1.DataSource = bindingSource1
            con.Close()
        End Using
    End Sub
End Class

[naveed_786]

Thanks Noorahmad and Fixo i have resolved the problem your code give me idea