Solved

Delete from recordset

Posted on 2004-09-26
8
161 Views
Last Modified: 2010-04-23
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
Comment
Question by:Nerdy_Girl88
8 Comments
 
LVL 28

Assisted Solution

by:iboutchkine
iboutchkine earned 100 total points
ID: 12154509
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
 
LVL 2

Assisted Solution

by:fulp02
fulp02 earned 100 total points
ID: 12155101
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
 
LVL 18

Accepted Solution

by:
DotNetLover_Baan earned 50 total points
ID: 12155662
Instead of this
mobjData.DscboAccess1.Access_Type.Rows(inCurrentRecordNumber).Delete()
use,
mobjData.DscboAccess1.Tables("Access_Type").Rows.RemoveAt(inCurrentRecordNumber)

-Baan
0
 

Author Comment

by:Nerdy_Girl88
ID: 12156483
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 2

Assisted Solution

by:ahmadfuwad
ahmadfuwad earned 50 total points
ID: 12157783
'try this
ods.Tables(0).Rows(me.DataGrid1.CurrentRowIndex).Delete  'ods is dataset
0
 
LVL 25

Assisted Solution

by:RonaldBiemans
RonaldBiemans earned 200 total points
ID: 12159752
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
 

Author Comment

by:Nerdy_Girl88
ID: 12211096
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
 

Author Comment

by:Nerdy_Girl88
ID: 12211146
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now