?
Solved

Delete from recordset

Posted on 2004-09-26
8
Medium Priority
?
178 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 28

Assisted Solution

by:iboutchkine
iboutchkine earned 400 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 400 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 200 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
Independent Software Vendors: 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!

 

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
 
LVL 2

Assisted Solution

by:ahmadfuwad
ahmadfuwad earned 200 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 800 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

801 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