why no row 6?

hog

hog

Well-known member
Joined
Mar 17, 2003
Messages
984
Location
UK
Any ideas why this code always reports the error "no row 6"?

If the dataset table has 12 rows which I confirm by looking in the Access table then run this code I keep getting the same error?

Code: 
            For intX = 0 To m_dsJob.Tables("tblJobs").Rows.Count - 1

                m_drJob = m_dsJob.Tables("tblJobs").Rows(intX)

                m_drJob.Delete()

                m_odaJob.DeleteCommand = m_ocbJob.GetDeleteCommand

                m_odaJob.Update(m_dsJob.Tables("tblJobs").GetChanges)

                m_dsJob.Tables("tblJobs").AcceptChanges()

Next
 
Hog, I think you are getting the error since you are deleting from the DataTable and Accepting the Changes. The rowcount will not be the same because of that.
 
OK that makes sense, but what of this problem then....

A contract table and a job table.

The contract table has a single entry stating 12 jobs

The jobs table has 12 entries, one for each job.

The use selects to delete the contract and thus the jobs.

Im trying to trap concurrency errors where some other user may be modifying the job data.....hence my code above.

Have I lost the plot on this one?
 
Because you update each loop, the number of rows is progressively decreasing. When you get to row = 6, you have deleted the first 6 rows and updated your ds thus there is now no row 6. Take the update out of your for loop. Incidently, update and fill methods automatically call an acceptchanges to the dataset. Unless you are directly implementing a command you dont need to call an acceptchanges.


edit: zy beat me to it.

Jon
 
Originally posted by hog
OK that makes sense, but what of this problem then....

A contract table and a job table.

The contract table has a single entry stating 12 jobs

The jobs table has 12 entries, one for each job.

The use selects to delete the contract and thus the jobs.

Im trying to trap concurrency errors where some other user may be modifying the job data.....hence my code above.

Have I lost the plot on this one?
Click to expand...

Can you not include a delete on both of the tables in your for loop?
 
Ive been up too long!

If I change to this,

Code: 
For intX = 0 To m_dsJob.Tables("tblJobs").Rows.Count - 1

                m_drJob = m_dsJob.Tables("tblJobs").Rows(intX)

                m_drJob.Delete()

                m_odaJob.DeleteCommand = m_ocbJob.GetDeleteCommand

                m_dsJob.Tables("tblJobs").AcceptChanges()

Next

I still get no row 6!
 
Why dont you try this

Code: 
Dim intY as Integer=m_dsJob.Tables("tblJobs").Rows.Count - 1
Dim intX as Integer=0
Dim int Z as Integer=0

While intZ<=intY
m_drJob = m_dsJob.Tables("tblJobs").Rows(intZ)
m_drJob.Delete()
m_odaJob.DeleteCommand = m_ocbJob.GetDeleteCommand
m_odaJob.Update(m_dsJob.Tables("tblJobs").GetChanges)
m_dsJob.Tables("tblJobs").AcceptChanges()
intY=intY-1
End While

Hope this helps. This is just a basic idea as to how to overcome this error.
 
Originally posted by hog

Code: 
For intX = 0 To m_dsJob.Tables("tblJobs").Rows.Count - 1

                m_drJob = m_dsJob.Tables("tblJobs").Rows(intX)

                m_drJob.Delete()

                m_odaJob.DeleteCommand = m_ocbJob.GetDeleteCommand

                m_dsJob.Tables("tblJobs").AcceptChanges()

Next
Click to expand...
 
A few thoughts,
When you do a delete on a dataset, it is different than when you call a delete against a db. The db deletes the data. A ds marks the row for deletion and sets the column values to null. The actual deletion occurs when an acceptchanges is called.

When you call an acceptchanges, the rowstate is reset to current and original. Consequently, if you call an acceptchanges before you call an update on your dataadapter, it wont read a modification in the data and no insert, delete, or update commands will be implemented against the db.

Jon
 
Result.........could you hear the penny drop :-)

Code: 
 For intX = 0 To m_dsJob.Tables("tblJobs").Rows.Count - 1

                m_drJob = m_dsJob.Tables("tblJobs").Rows(intX)

                m_drJob.Delete()

                m_odaJob.DeleteCommand = m_ocbJob.GetDeleteCommand

Next

m_odaJob.Update(m_dsJob.Tables("tblJobs").GetChanges)

Thanks to you both.....
 
You can move out the last line of your For statement, where you set the DeleteCommand, to after the Next. Theres no need to set it each time.

Also, Ive often deleted inside the loop. Just loop backwards, from Count-1 to 0 with a Step (if thats still the right keywork in .NET).

The issue is that VB calculates the from and to values only once. So once it goes past the line "For intX..." it figures out 0 and your Count-1 and stores them in internal variables. It does NOT check them on each pass. Counting backwards works because you dont care if the count is changing. You can also use a While loop (as suggested), which WILL check the count each pass.

-nerseus
 
Back
Top