[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Error -2147217887 (80040e21) when deleting a recordset.

Posted on 2005-05-02
14
Medium Priority
?
429 Views
Last Modified: 2012-06-27
Hi, guys, I'm working with SQL7, VB6 and ADO in a program. I have a form where I do the update of a master table, which has two more tables related in a father-child-grand child structure, ok ? I have a problem when deleting a recordset from the master table. When doing the deletion process, first are deleted the records from the child and the grand child tables, and at last master recordset. The error occurs in the delete command, the error message is as follows:

Run-time error -2147217887 (80040e21)
Multiple-step operation generated errors. Check each status value.

I've tested many things, but the error still go on. First I changed the opening mode of the recordsets as Keyset or Dynamic mode. Then I changed the way to delete the recordsets from the tables, from a SQL command to a simple VB delete command. Well, if you need I can send you same of the code to help you find out the problem, ok ?

Thank's.
0
Comment
Question by:LillianJohnsson
  • 8
  • 6
14 Comments
 
LVL 12

Expert Comment

by:Preece
ID: 13911843
Hmmmmmmm....you mentioned "first are deleted the records from the child and the grand child tables, and at last master recordset."

Shouldn't this be the grandchild, then child, then father?

0
 

Author Comment

by:LillianJohnsson
ID: 13912005
Yes, that's rights, You are thinking that erasing the child before thet grand child makes an error ? It's not that same that erasing the grand child an then the child. Remeber that the error ocurrs when deleting the master table recordset, ok ?
Anyway, here are the main parts of the process:

Private Const strCnnSQLServer = "Provider=MSDATASHAPE;Data Provider=SQLOLEDB.1;Data Source=PC133;User ID=sa;PWD=;Initial Catalog=Stock;"

rsMaster.CursorType = adOpenKeyset
rsMaster.LockType = adLockOptimistic
rsMaster.CursorLocation = adUseServer
rsMaster.Open "SELECT * FROM MasterTable ORDER BY InputNumber", cnnStock, , , adCmdText

'The same process to open the other two tables.

   With rsChild
        .MoveFirst
        .Find "InputNumber='" & txtInputnumber.Text & "'"

        Do While Not .EOF
           If .Fields("InputNumber").Value = txtInputNumber.Text Then
              .Delete
           Else
              Exit Do
           End If
   
           .MoveNext
        Loop
   End With

   Dim cmdGrandChild    As New ADODB.Command
   Dim rsCmdGrandChild As New ADODB.Recordset
   cmdGrandChild.CommandType = adCmdText
   cmdGrandChild.ActiveConnection = cnnInventario
   cmdGrandChild.CommandText = "DELETE FROM GrandChildTable WHERE InputNumber='" & txtInputNumber.Text & "'"
   Set rsCmdGrandChild = cmdGrandChild.Execute
   rsGrandChild.Requery

   'Master recordset erase.
   rsMaster.Delete    'HERE SHOWS THE ERROR ******
   rsMaster.MovePrevious
   If rsMaster.BOF Then
      rsMaster.MoveFirst
   End If
0
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.

 
LVL 12

Expert Comment

by:Preece
ID: 13912185
Maybe someone can help me out, but these two lines don't make sense, specifically the second one:

cmdGrandChild.CommandText = "DELETE FROM GrandChildTable WHERE InputNumber='" & txtInputNumber.Text & "'"
Set rsCmdGrandChild = cmdGrandChild.Execute

The cmdGrandChild.Execute isn't going to return a recordset to be assigned to rsCmdGrandChild.

Honestly, I would try to delete the grandchildren, then the children, then the master.
0
 
LVL 12

Expert Comment

by:Preece
ID: 13912214
Also, assuming you are ok with the order in which you are deleting, then the following line may just be the culprit:

 Set rsCmdGrandChild = cmdGrandChild.Execute

Preece
0
 

Author Comment

by:LillianJohnsson
ID: 13912463
first, let me tell you that I've changed the order as you told me. second, you're right the delete execute command are returning nothing, I'm fixing that, but still have the problem. Why ? 'case I tried other process instead that sql command, a do while loop process to delete one by one the grand child recordsets.
By the way, I looked the two links you showed me, but the first is caused by a missing field in an insert command, and seemed to me the seconds one don't apply to my case. But anyway, thansk for keep helping me :-)
0
 
LVL 12

Expert Comment

by:Preece
ID: 13912666
Perhaps you should not query the master recordset until you are done with your grandchild, child deletions.  Or requery before attempting the master delete...
0
 

Author Comment

by:LillianJohnsson
ID: 13918376
ok, thanks, now I'm going to test that...
0
 

Author Comment

by:LillianJohnsson
ID: 13918949
Hi again Preece, I've been testing what you told me but still the same error occurs. I've seen something strange at the opening command. I open the master recordset as dynamic, but after the open command, it set to Static by the system, I don't understand why, so I think may be that is why I can't do deletions. But anyway, the strange thing is that I can add new recordsets in the same module. Here I describe the connection string and the open command:

Private cnnStock       As New ADODB.Connection
Public rsMasterTable As New ADODB.Recordset

Private Const strCnnSQLServer = "Provider=MSDATASHAPE;Data Provider=SQLOLEDB.1;Data Source=PC133;User ID=sa;PWD=;Initial Catalog=Stock;"

rsMasterTable.CursorLocation = adUseServer
rsMasterTable.Open "SELECT * FROM Master ORDER BY InputNumber", cnnStock, adOpenDynamic, adLockOptimistic, adCmdText

Ok, I hope this help you solve that for me, thank you again.
0
 
LVL 12

Expert Comment

by:Preece
ID: 13919009
I'm going to go get breakfast first!  Be back in a bit!
0
 
LVL 12

Expert Comment

by:Preece
ID: 13919182
I'm curious about your connect string...  Have a look at this paq and see how I formed the connect string:

http://www.experts-exchange.com/Programming/Q_21405277.html
0
 

Author Comment

by:LillianJohnsson
ID: 13920294
I've been testing that string with no result. But let me tell you that in the module where occurs the error, I'm using two MSHFlexGrid controls to show the child and grand child recordsets. I made the changes to the connection string based on yours, and when I run the program, both grid controls shows nothing.
In my connection string, the strange thing could be the MSDATASHAPE provider. As I read, it can be used to show hierarchical recordsets, so I think it helps me in this case, is that ok ?
0
 
LVL 12

Accepted Solution

by:
Preece earned 1500 total points
ID: 13920450
Does SQL Server 7 support hierarchical recordsets?  Are you working on a non-relational data model?  I've only done development on relational db's.  
0
 

Author Comment

by:LillianJohnsson
ID: 13927708
Preece, I solved the problem ! It's something strange, but you have to listen that:

Yeasterday I tested changing the place of the line where I do that deletion, to see where the problem come up. But I included by accident before the erase a Find command. the result was that the deletion was succesfull. I did a debug, and I found that if I don't include the Find command, the value of the bookmark is different of the active record. I don't know why, 'cause moment before that command, I can see the value of the bookmark is correct. Well, anyway, I include the command to help the answer you gave me.

rsMaster.Find "InputNumber = '" & txtInputNumber.Text & "'"
rsMaster.Delete

Preece, thanks a lot for helping me ! :-)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month19 days, 17 hours left to enroll

873 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