12 Replies - 1652 Views - Last Post: 03 March 2013 - 08:23 AM Rate Topic: -----

#1 Pooja Mengaji  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 43
  • Joined: 24-February 13

How to delete multiple records 4m DB using checkboxes in datagridview?

Posted 02 March 2013 - 08:45 AM

i hav a table created in oracle supplier(Name, Contact1,Contact2).
i hav used a datagridview for retriveing the supplier table values on form load. what i want is when the user selects multiple checkboxes, then onclick of delete button the records should be deleted from the database & the datagridview should be refreshed with the edited data. also please tell how to write the delete query for the same in the db code.
my code is as follows:

Private CheckboxColumn As New DataGridViewCheckBoxColumn()

Private Sub SupplierContact_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Me.CheckboxColumn.HeaderText = "Tick to Delete"
Me.DataGridView1.Columns.Insert(0, CheckboxColumn)
AddHandler DataGridView1.CurrentCellDirtyStateChang… AddressOf dataGridView1_CurrentCellDirtyStateChang…
AddHandler DataGridView1.CellValueChanged, AddressOf dataGridView1_CellValueChanged

End Sub

Private Sub dataGridView1_CurrentCellDirtyStateChang… sender As Object, ByVal e As EventArgs)
If DataGridView1.IsCurrentCellDirty Then
DataGridView1.CommitEdit(DataGridViewDat…
End If
End Sub

Private Sub dataGridView1_CellValueChanged(ByVal sender As Object, ByVal e As DataGridViewCellEventArgs)
If DataGridView1.Columns(e.ColumnIndex).Nam… = "CheckboxColumn" And e.RowIndex = 0 Then
Dim i As Int32
For i = 1 To DataGridView1.Rows.Count
DataGridView1.Rows(i).Cells("CheckboxCol… = DataGridView1.CurrentCell.Value
Next

End If
End Sub

Private Sub Delete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Delete.Click
Dim RowsToDelete As New List(Of DataGridViewRow)
Try
Dim myOracleConnection As New OracleConnection("User Id=shweja;Password=shweja")
myOracleConnection.Open()
Dim myOracleTransaction As OracleTransaction = myOracleConnection.BeginTransaction()
Dim myOracleCommand As OracleCommand = myOracleConnection.CreateCommand()
myOracleCommand.CommandText = "delete from supplier where Name = ' " & scn.Text & "'"
Dim Delete = MessageBox.Show("Are You Sure to Delete", "Query", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
If (Delete = vbYes) Then
myOracleCommand.ExecuteNonQuery()
MessageBox.Show("Record Deleted successfully!", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information)
End If
myOracleTransaction.Commit()
myOracleConnection.Close()
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
End Sub



also it gives an error that execute requires an obj for transaction.
please help...

Is This A Good Question/Topic? 0
  • +

Replies To: How to delete multiple records 4m DB using checkboxes in datagridview?

#2 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1623
  • View blog
  • Posts: 5,194
  • Joined: 25-September 09

Re: How to delete multiple records 4m DB using checkboxes in datagridview?

Posted 02 March 2013 - 09:26 AM

        For Each itm As DataGridViewRow In DataGridView1.Rows
                If itm.Cells(0).Value = True Then 'assuming 1st column is a checkbox
                'Pass Information to SQL Statement to delete this row.
            End If
            
        Next

This post has been edited by CharlieMay: 02 March 2013 - 09:27 AM

Was This Post Helpful? 0
  • +
  • -

#3 Pooja Mengaji  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 43
  • Joined: 24-February 13

Re: How to delete multiple records 4m DB using checkboxes in datagridview?

Posted 02 March 2013 - 10:06 AM

View PostCharlieMay, on 02 March 2013 - 09:26 AM, said:

        For Each itm As DataGridViewRow In DataGridView1.Rows
                If itm.Cells(0).Value = True Then 'assuming 1st column is a checkbox
                'Pass Information to SQL Statement to delete this row.
            End If
            
        Next


thanks for replying...but i want to delete records for only that row which is ticked....CharlieMay ur sol works for deleting all the records from the table. please if possible then give an eg. for the delete query which i want. please reply
Was This Post Helpful? 0
  • +
  • -

#4 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1623
  • View blog
  • Posts: 5,194
  • Joined: 25-September 09

Re: How to delete multiple records 4m DB using checkboxes in datagridview?

Posted 02 March 2013 - 01:46 PM

No, if the item is true
Build an sqlstatement against that checked row and execute it


For example
Chk Column  CustID   CustomerName
  [ ]         1      Acme Enterprises
  [X]         2      Widgit Inc
  [X]         3      Harpo Industries
  [ ]         4      Darma Initiative



For Each itm As DataGridViewRow In DataGridView1.Rows
    If itm.Cells(0).Value = True Then 'assuming 1st column is a checkbox
        Dim sSQL as string = "DELETE FROM myTable WHERE CustID = @id"
        Dim cmd as New OledbCommand(ssql, con)
        cmd.parameters.addwithvalue("@id", itm.cells(1).Value.Tostring)
        cmd.executenonquery
    End If
Next

In this example, Widgit Inc and Harpo Industries would be deleted from the database
Was This Post Helpful? 0
  • +
  • -

#5 andrewsw  Icon User is offline

  • It's just been revoked!
  • member icon

Reputation: 3806
  • View blog
  • Posts: 13,488
  • Joined: 12-December 12

Re: How to delete multiple records 4m DB using checkboxes in datagridview?

Posted 02 March 2013 - 01:54 PM

This declaration:

Dim sSQL as string = "DELETE FROM myTable WHERE CustID = @id"

could occur before entering the loop I believe. Couldn't the cmd declaration as well?
Was This Post Helpful? 0
  • +
  • -

#6 andrewsw  Icon User is offline

  • It's just been revoked!
  • member icon

Reputation: 3806
  • View blog
  • Posts: 13,488
  • Joined: 12-December 12

Re: How to delete multiple records 4m DB using checkboxes in datagridview?

Posted 02 March 2013 - 02:03 PM

Something like:

Dim sSQL as string = "DELETE FROM myTable WHERE CustID = @id"

Using cmd As OleDbCommand = New OleDbCommand(ssql, con)
    For Each itm As DataGridViewRow In DataGridView1.Rows
        If itm.Cells(0).Value = True Then 'assuming 1st column is a checkbox
            
            cmd.parameters.addwithvalue("@id", itm.cells(1).Value.Tostring)
            cmd.executenonquery
        End If
    Next
End Using

Was This Post Helpful? 0
  • +
  • -

#7 CodingSup3rnatur@l-360  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 992
  • View blog
  • Posts: 972
  • Joined: 30-September 10

Re: How to delete multiple records 4m DB using checkboxes in datagridview?

Posted 02 March 2013 - 02:39 PM

If you reuse the same command object, you just have to remember to clear the Parameters collection by calling Clear() on it on every iteration :)
Was This Post Helpful? 0
  • +
  • -

#8 andrewsw  Icon User is offline

  • It's just been revoked!
  • member icon

Reputation: 3806
  • View blog
  • Posts: 13,488
  • Joined: 12-December 12

Re: How to delete multiple records 4m DB using checkboxes in datagridview?

Posted 02 March 2013 - 02:47 PM

View PostCodingSup3rnatur@l-360, on 02 March 2013 - 02:39 PM, said:

If you reuse the same command object, you just have to remember to clear the Parameters collection by calling Clear() on it on every iteration :)

Thank you, I thought there was something missing :)

But can't we create the parameters outside the loop and just set their values within the loop.. I should dig a little.
Was This Post Helpful? 0
  • +
  • -

#9 Pooja Mengaji  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 43
  • Joined: 24-February 13

Re: How to delete multiple records 4m DB using checkboxes in datagridview?

Posted 03 March 2013 - 04:51 AM

View PostCharlieMay, on 02 March 2013 - 01:46 PM, said:

No, if the item is true
Build an sqlstatement against that checked row and execute it


For example
Chk Column  CustID   CustomerName
  [ ]         1      Acme Enterprises
  [X]         2      Widgit Inc
  [X]         3      Harpo Industries
  [ ]         4      Darma Initiative



For Each itm As DataGridViewRow In DataGridView1.Rows
    If itm.Cells(0).Value = True Then 'assuming 1st column is a checkbox
        Dim sSQL as string = "DELETE FROM myTable WHERE CustID = @id"
        Dim cmd as New OledbCommand(ssql, con)
        cmd.parameters.addwithvalue("@id", itm.cells(1).Value.Tostring)
        cmd.executenonquery
    End If
Next

In this example, Widgit Inc and Harpo Industries would be deleted from the database



i tried this but gives an exception saying the impression missing


 Dim myOracleConnection As New OracleConnection("User Id=shweja;Password=shweja")
            myOracleConnection.Open()
            ' Dim myOracleTransaction As OracleTransaction = myOracleConnection.BeginTransaction()
            'Dim myOracleCommand As OracleCommand = myOracleConnection.CreateCommand()
            For Each itm As DataGridViewRow In DataGridView1.Rows
                If itm.Cells(0).Value = True Then 'assuming 1st column is a checkbox
                    Dim sSQL As String = "delete from supplier WHERE sid = @id"
                    Dim cmd As New OracleCommand(sSQL, myOracleConnection)
                    Dim Delete = MessageBox.Show("Are You Sure to Delete", "Query", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
                    If (Delete = vbYes) Then
                        cmd.ExecuteNonQuery()
                        MessageBox.Show("Record Deleted successfully!", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information)
                    End If
                    cmd.Parameters.AddWithValue("@id", itm.Cells(1).Value.ToString)
                    cmd.executenonquery()
                End If
            Next

Was This Post Helpful? 0
  • +
  • -

#10 andrewsw  Icon User is offline

  • It's just been revoked!
  • member icon

Reputation: 3806
  • View blog
  • Posts: 13,488
  • Joined: 12-December 12

Re: How to delete multiple records 4m DB using checkboxes in datagridview?

Posted 03 March 2013 - 05:03 AM

"impression missing"? You should post the full error message and tell us which line the message refers to in your posted code.

Your code doesn't follow the same order as CharlieMay 's. You are attempting to execute the query before setting the parameter's value.

You are also attempting to delete the record, even if the user confirms they don't want to do this.
Was This Post Helpful? 0
  • +
  • -

#11 Pooja Mengaji  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 43
  • Joined: 24-February 13

Re: How to delete multiple records 4m DB using checkboxes in datagridview?

Posted 03 March 2013 - 07:58 AM

View Postandrewsw, on 03 March 2013 - 05:03 AM, said:

"impression missing"? You should post the full error message and tell us which line the message refers to in your posted code.

Your code doesn't follow the same order as CharlieMay 's. You are attempting to execute the query before setting the parameter's value.

You are also attempting to delete the record, even if the user confirms they don't want to do this.


the error is:
System.data.oracleclient.oracleexception: ORA-01036 illegal variable name/number

i hav also attched the image of the error. please check
Was This Post Helpful? 0
  • +
  • -

#12 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1623
  • View blog
  • Posts: 5,194
  • Joined: 25-September 09

Re: How to delete multiple records 4m DB using checkboxes in datagridview?

Posted 03 March 2013 - 08:09 AM

OK, it appears that @ID is not a valid parameter for Oracle, I think it might be :ID instead. Try changing both occurrences to :ID and see if that works.
Was This Post Helpful? 0
  • +
  • -

#13 Pooja Mengaji  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 43
  • Joined: 24-February 13

Re: How to delete multiple records 4m DB using checkboxes in datagridview?

Posted 03 March 2013 - 08:23 AM

View PostCharlieMay, on 03 March 2013 - 08:09 AM, said:

OK, it appears that @ID is not a valid parameter for Oracle, I think it might be :ID instead. Try changing both occurrences to :ID and see if that works.


thank u soooo much CharlieMay....it really worked...thanks a lot
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1