473,327 Members | 2,118 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Why Can't I Delete a record this way?

Hello all,

When a user click on the "Update" button on my program, I want to update the
database using the sub below. I was able to insert and modify records, but
can't delete records. The error reads: "Deleted row information cannot be
accessed through the row". I understand that when I refer to dr.Item(0) but
that datarow had been deleted thus I cannot get the data.... Is there any
workaround to this?
Thanks for reading.
VhD50

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button3.Click

Dim dsChanged As New Data.DataSet

dsChanged.Clear()

dsChanged = ds.GetChanges

Dim dr As Data.DataRow

Dim cmdStr As String

Dim Cmd As New OleDb.OleDbCommand

conn.Open()

Try

For Each dr In dsChanged.Tables(0).Rows

Select Case dr.RowState.ToString

Case "Modified"

cmdStr = "UPDATE Users SET " & _

"[Password] = '" & dr.Item(1) & "', " & _

"[Domain] = '" & dr.Item(2) & "' " & _

"WHERE [User] = '" & dr.Item(0) & "';"

MsgBox(cmdStr)

Cmd.CommandText = cmdStr

da.UpdateCommand = Cmd

da.UpdateCommand.Connection = conn

da.UpdateCommand.ExecuteNonQuery()

MsgBox("Modified OK")

Case "Added"

cmdStr = "INSERT INTO Users VALUES ( " & _

"'" & dr.Item(0) & "', " & _

"'" & dr.Item(1) & "', " & _

"'" & dr.Item(2) & "')"

MsgBox(cmdStr)

Cmd.CommandText = cmdStr

da.InsertCommand = Cmd

da.InsertCommand.Connection = conn

da.InsertCommand.ExecuteNonQuery()

Case "Deleted"

cmdStr = "DELETE FROM Users WHERE User = '" & dr.Item(0) & "'" <<<<== I
gets the error here at this line of code

MsgBox(cmdStr)

Cmd.CommandText = cmdStr

da.DeleteCommand = Cmd

da.DeleteCommand.Connection = conn

da.DeleteCommand.ExecuteNonQuery()

End Select

Next

MsgBox("Update OK")

ds.Reset()

Button2.PerformClick()

DataGrid1.Refresh()

Catch ex As Exception

MsgBox(ex.Message)

Finally

conn.Close()

End Try

End Sub
Nov 21 '05 #1
4 1756
On 2005-08-31, Stanav <st*******@hotmail.com> wrote:
Hello all,

When a user click on the "Update" button on my program, I want to update the
database using the sub below. I was able to insert and modify records, but
can't delete records. The error reads: "Deleted row information cannot be
accessed through the row". I understand that when I refer to dr.Item(0) but
that datarow had been deleted thus I cannot get the data.... Is there any
workaround to this? <snip>
Case "Deleted"

cmdStr = "DELETE FROM Users WHERE User = '" & dr.Item(0) & "'" <<<<== I
gets the error here at this line of code


dr.Item(0,DataRowVersion.Original)
Nov 21 '05 #2
Thanks a lot, dfoster, for the tip and for the lightning fast response.
It works now.
VhD50.
"dfoster" <df*****@woofix.local.dom> wrote in message
news:sl********************@localhost.localdomain. ..
On 2005-08-31, Stanav <st*******@hotmail.com> wrote:
Hello all,

When a user click on the "Update" button on my program, I want to update
the
database using the sub below. I was able to insert and modify records,
but
can't delete records. The error reads: "Deleted row information cannot be
accessed through the row". I understand that when I refer to dr.Item(0)
but
that datarow had been deleted thus I cannot get the data.... Is there any
workaround to this?

<snip>
Case "Deleted"

cmdStr = "DELETE FROM Users WHERE User = '" & dr.Item(0) & "'"
<<<<== I
gets the error here at this line of code


dr.Item(0,DataRowVersion.Original)

Nov 21 '05 #3
Why would you want to is my question? You are manually creating the same
functionality that is already available via a DataAdapter. Just create a
DataAdapter and configure the CommandText of the various sub command objects
to your liking and your done.
"Stanav" <st*******@hotmail.com> wrote in message
news:e%****************@tk2msftngp13.phx.gbl...
Hello all,

When a user click on the "Update" button on my program, I want to update
the database using the sub below. I was able to insert and modify records,
but can't delete records. The error reads: "Deleted row information cannot
be accessed through the row". I understand that when I refer to dr.Item(0)
but that datarow had been deleted thus I cannot get the data.... Is there
any workaround to this?
Thanks for reading.
VhD50

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button3.Click

Dim dsChanged As New Data.DataSet

dsChanged.Clear()

dsChanged = ds.GetChanges

Dim dr As Data.DataRow

Dim cmdStr As String

Dim Cmd As New OleDb.OleDbCommand

conn.Open()

Try

For Each dr In dsChanged.Tables(0).Rows

Select Case dr.RowState.ToString

Case "Modified"

cmdStr = "UPDATE Users SET " & _

"[Password] = '" & dr.Item(1) & "', " & _

"[Domain] = '" & dr.Item(2) & "' " & _

"WHERE [User] = '" & dr.Item(0) & "';"

MsgBox(cmdStr)

Cmd.CommandText = cmdStr

da.UpdateCommand = Cmd

da.UpdateCommand.Connection = conn

da.UpdateCommand.ExecuteNonQuery()

MsgBox("Modified OK")

Case "Added"

cmdStr = "INSERT INTO Users VALUES ( " & _

"'" & dr.Item(0) & "', " & _

"'" & dr.Item(1) & "', " & _

"'" & dr.Item(2) & "')"

MsgBox(cmdStr)

Cmd.CommandText = cmdStr

da.InsertCommand = Cmd

da.InsertCommand.Connection = conn

da.InsertCommand.ExecuteNonQuery()

Case "Deleted"

cmdStr = "DELETE FROM Users WHERE User = '" & dr.Item(0) & "'" <<<<==
I gets the error here at this line of code

MsgBox(cmdStr)

Cmd.CommandText = cmdStr

da.DeleteCommand = Cmd

da.DeleteCommand.Connection = conn

da.DeleteCommand.ExecuteNonQuery()

End Select

Next

MsgBox("Update OK")

ds.Reset()

Button2.PerformClick()

DataGrid1.Refresh()

Catch ex As Exception

MsgBox(ex.Message)

Finally

conn.Close()

End Try

End Sub

Nov 21 '05 #4
Stanav,

In fact the same question as Scott asked in another way.

Why are you using a dataset?

If you want to do it this way than your own collection is much easier to
use.

Cor
Nov 21 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Uwe Range | last post by:
Hi to all, I am displaying a list of records in a subform which is embedded in a popup main form (in order to ensure that users close the form when leaving it). It seems to be impossible to...
5
by: Magix | last post by:
Hi, This is about File I/O Operation. I want to delete a record from a file, let say FileA. Any one has any good and efficient example/approach? void DeleteRecord(int LineNum) { // open...
3
by: Bobbie | last post by:
One of my records is returning garbage. I tried to delete the record it a message comes back and says Search Key Cannot be found. I don't know what happened to the record but I sure need to get...
3
by: igendreau | last post by:
I'm trying to clean up a database of mine, and I need to convert some old DAO code over to ADO. When I was using DAO, I had no problem running this script and deleting the record using rs.Delete. ...
4
by: Phil Stanton | last post by:
Sorry to repost, but am having another look at deleting a record. I have a form (Member) and have removed all the event procedures associated with the Form (OnCurrent, OnDelete, OnActivate etc)...
0
by: Rote Rote | last post by:
Hi Guys, I have a simple Edit,Update Gridview and i'm using ObjectDatasource with dataset generated and TableAdapters I can do an update no problem. But can't get my delete to work. When i look at...
2
by: shapper | last post by:
Hello, I have two tables: TagId, PostId TagId, PostId How can I delete, given a TagId, the record from Tags and all records associated with it in PostTags.
3
by: shapper | last post by:
Hello, I have a table named Tags with the following columns: TagId and TagValue. How can I delete a record, using LINQ, given the TagId value. Thanks, Miguel
0
by: shapper | last post by:
Hello, I have a ListView as defined as follow: Private Sub lv_ItemDeleting(ByVal sender As Object, ByVal e As ListViewDeleteEventArgs) Handles lv.ItemDeleting Dim database As New...
1
by: Bob | last post by:
Hello, Using VB 2005.net. I have a bound datagridview and a delete button control (or when the user pressed the 'Delete' key) I can successfully delete the selected record from the table and...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.