why no row 6?

[hog]

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?

 

           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

[zy_abc]

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.

[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.

 

I'm 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?

[jfackler]

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 don't need to call an acceptchanges.

 

 

edit: zy beat me to it.

 

Jon

[jfackler]

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.

 

I'm 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?

 

Can you not include a delete on both of the tables in your for loop?

[hog]

I've been up too long!

 

If I change to this,

 

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!

[zy_abc]

Why don't you try this

 

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.

[jfackler]

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

[jfackler]

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

[hog]

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

 

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.....

[Nerseus]

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

 

Also, I've often deleted inside the loop. Just loop backwards, from Count-1 to 0 with a Step (if that's 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 don't care if the count is changing. You can also use a While loop (as suggested), which WILL check the count each pass.

 

-nerseus

[hog]

Good advice.....cheers Nerseus :-)