• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 183
  • Last Modified:

Delete from recordset

hi, this is my first experience with vb.net, so i aplogis if my questions seem obvious,

I am trying to delete a record from a dataset,  the code was running fine, untill i made some changes,  but i don't know what changes are causing the delete to fail because the code executes fine, it just doesn't actually delete the record,  Can you see what I'm doing wrong.
-----------------------------------------------------
 Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        'this method should delete the current row in the dataset, which is displayed on the form
        Try
            If MessageBox.Show("Are you sure you want to delete this record. No recovery Possible", "lkjl", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = DialogResult.Yes Then
                Dim inCurrentRecordNumber As Integer = Me.BindingContext(mobjData.DscboAccess1.Tables("Access_Type")).Position
                mobjData.DscboAccess1.Access_Type.Rows(inCurrentRecordNumber).Delete()
                mobjData.DscboAccess1.AcceptChanges()
                UpdateDatabase()
                'update the combobox
                LoadCombobox()
            End If
        Catch ex As Exception
            MessageBox.Show("No Record Selected, Please selete another Operation", "Delete Failure", MessageBoxButtons.OK)
        End Try
    End Sub

Private Sub UpdateDatabase()
        mobjData.DacboAccess.Update(mobjData.DscboAccess1, "Access_Type")
    End Sub

----------------------------------------

the form has, a combobox and two textboxes, they are both bound to the same dataset.  when i pick a record from the combobox, the textboxes are populated with the related data. I can add new records, edt current records, i just can't delete.

thanks

0
Nerdy_Girl88
Asked:
Nerdy_Girl88
5 Solutions
 
iboutchkineCommented:
Here is the working example

'by default all the rows are processed in their primary key order. That is why if for example you
want to delete record with primary key 10 and then insert the record with a primary key 10
then if insert will happen first  - it will raise an error.
--------------------------------------------------------------------
That is why it is always better to to delete, then update and  then insert
--------------------------------------------------------------------


   Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click

        Dim sql As String = "SELECT * FROM Publishers"
        Dim cn As New OleDbConnection(BiblioConnString)
        Dim da As New OleDbDataAdapter(sql, cn)
        Dim ds as New DataSet()

        cn.Open()
        da.Fill(ds, "Publishers")


        Dim cmdBuilder As New OleDbCommandBuilder(da)


        ' generate the three default commands
        da.DeleteCommand = cmdBuilder.GetDeleteCommand
        da.InsertCommand = cmdBuilder.GetInsertCommand
        da.UpdateCommand = cmdBuilder.GetUpdateCommand


        With ds.Tables("Publishers")
            ' Modify the first record (just append 3 asterisks to Name field)
            .Rows(0)("Name") = .Rows(0)("Name").ToString & " ***"

            ' Add a new record
            Dim dr As DataRow = .NewRow
            dr("pub_id") = "9988"
            dr("Name") = "Iouri"
            dr("city") = "New York"
            dr("country") = "USA"
            .Rows.Add(dr)
        End With

        ' Send changes them to the database.
        da.Update(ds, "Publishers")
        cn.Close()
    End Sub

'ensure that changes were successful
  If Not ds.HasChanges Then Exit Sub

'else there was no success give a message
0
 
fulp02Commented:
Try
            If ((MessageBox.Show("Are you sure you want to delete this record. No recovery Possible", "lkjl", MessageBoxButtons.YesNo, MessageBoxIcon.Question))) = DialogResult.Yes Then

If that does not work comment the if statment out and if it work after that then you know where yout problem is.
0
 
DotNetLover_BaanCommented:
Instead of this
mobjData.DscboAccess1.Access_Type.Rows(inCurrentRecordNumber).Delete()
use,
mobjData.DscboAccess1.Tables("Access_Type").Rows.RemoveAt(inCurrentRecordNumber)

-Baan
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Nerdy_Girl88Author Commented:
sorry all i am going away for a week,  not sure if where im going has the internet, I will respond whe get abck, tanks for you help.
0
 
ahmadfuwadCommented:
'try this
ods.Tables(0).Rows(me.DataGrid1.CurrentRowIndex).Delete  'ods is dataset
0
 
RonaldBiemansCommented:
you are calling

mobjData.DscboAccess1.AcceptChanges()

before you update, this statement should come after you update the data, otherwise the all rowstates will be reset to unchanged, so the dataadapter will think there is nothing to update.

like

UpdateDatabase()
mobjData.DscboAccess1.AcceptChanges()


0
 
Nerdy_Girl88Author Commented:
thanks RonaldBiemans, i have tried you sujjestion and still no luck,  I tnk my problem is that i am not getting the row number.  I have been dugging and the inCurrentRecordNumber = 0,  Here is the code, can you se why it does this?

Try
                'turn off the read only property
                unLockTextboxes()

                Dim inCurrentRecordNumber As Integer = Me.BindingContext(mobjData.DscboAccess1.Tables("Access_Type")).Position
                mobjData.DscboAccess1.Access_Type.Rows(inCurrentRecordNumber).Delete()

                UpdateDatabase()
                mobjData.DscboAccess1.AcceptChanges()

                'update the combobox
                LoadCombobox()
            Catch ex As Exception
                MessageBox.Show("No Record Selected, Please selete another Operation", "Delete Failure", MessageBoxButtons.OK)
            End Try

i am using a combobox to get the the text fields on the form to populate with the appropreiate data.

thanks.
0
 
Nerdy_Girl88Author Commented:
Thanks everyone for you input, I managed to get it working using this code, I am using the SelectedIndex on the combobox, since the combox and  the text fields are bound to the sae datasource.

Thanks for all your help i will split the points.

                        Dim inCurrentRecordNumber As Integer = Me.cboAccessSearch.SelectedIndex
                        mobjData.DscboAccess1.Access_Type.Rows(inCurrentRecordNumber).Delete()
                        UpdateDatabase()
                        mobjData.DscboAccess1.AcceptChanges()

nerdy girl
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now