Multi-step operation generated errors.

Posted on 2005-04-06
Last Modified: 2012-06-27
I am facing 2 inexplicable problems using ADO recordset in VB6 with SQL Server:
1) After populating data-bound TextBoxes with data if  I delete the value in a TextBox   & then execute the Update method,I observed that the value doesn't change at the backend.That is the value should now be NULL.But the original value remains intact.
2) If  I delete the value in a TextBox & then programmatically assign a value to another field in the same recordset object & then Update,I get this error:
   Multi-step operation generated errors.Check each status value.
      What is the problem?Although sans deleting the value if  I just alter it then the data is properly saved.
Question by:EXwithRaj
    LVL 53

    Expert Comment

    do you have the primary key in your recordset ?

    Expert Comment

    Dear EXwith Raj

    50 points is not very generous!! It is one of those errors that pops up all the time if

    a. Dhaest is absolutely correct - no primary key in the recordset causes it.
    b. I have frequently found that it occurs if the recordset spans more than one table and some other error occurs - for example a data tupe mismatch. For some reason it does not tell you that it is a data type mismatch, presumably because there is a hierarchy of errors and Multi-step Op comes earlier in the hierarchy. Are you sure that you can set the field to NULL or ....

    Just a thought- good luck!
    LVL 8

    Accepted Solution

    these things act funny like that when you have a databound textbox. when you delete the text in the textbox and then call the update method, you have not actually changed anything. only when you move focus from that text box to another textbox and change some data, or change another field will the previous field change take affect. try this

    instead of calling the update after you delete the text in the textbox, call the MoveNext method. i bet you get the same error. if that is the case, you have the datafield in the database set to "Allow Zero Length String=No" or "Required=Yes" is set on the field. if that is the case, Set Allow Zero Length String=Yes", and "Required=No". Then test again.

    MoveNext will actually try to save the current textbox data where as the Update method will not. if you get the MoveNext to work without error, then your: "programmatically assign a value to another field in the same recordset object & then Update" will work.

    But to tell you the truth. I would gather the data for the form with a recordset and populate the textboxes. when the user saves the data i would run an update statement or read each field and do
    rs.fields("FIELD").value = textbox1.text

    Take the Binding out. its too flakey. I usually control everything, don't rely on the controls to do it. they do it their way. you need to do it your way.

    I hope that makes sence.


    Author Comment

    1) I've the Primary Key set.I never create table sans setting the PK.
    2) wraith821,the "Allow Zero Length String" & Required properties are that of MS Access.But I am using SQL Server which I'd stated in my query.In SQL Server I've checked the AllowNull column for the relevant columns.
    3) I've also detected that the 2 problems occur only in the case of numeric fields - not string fields.
    4) MoveNext method is not required on my part.Because while saving my recordset is either empty(a query executed at start which returns nothing) or just 1 record.
    5) My query doesn't span multiple table.It spans just 1 table.
    6) My problems occur against SQL Server 7 & 2000.
    7) Is this a know/unknown bug that I've detected?I've never faced this strange problem in MS Access.
    8) Coming back to wraith821,your "Take the Binding out" suggestion is The Solution!Therefore,I've decided to collect data from these TextBoxes,assign it to the fields in the recordset & then save the data.It works.
        Sometimes simple solutions do escape your mind.Thanks anyway.
    LVL 8

    Expert Comment

    HAHAH! i sould have read better, i have been reading so many of these things i probably was thinking of another question when i typed half that answer then solved your dilema  with the end of it. Oh well as long as you got your solution.


    Expert Comment

    Glad you are solved but just one comment about data binding. I absolutely agree with Wraith - if you want to get on with it and be confident, do it yourself and forget the controls! However, I got to the stage where I was quite determined to make it work if I could. What I found most effective was to set the field in the datacontrol properties but not the datasource. In code I then do

    SQLStmt = SELECT ...."
    with adodc1
        set text1.datasource=.recordset
    end with

    It is a bit more coding but I do find it works consistently


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Introduction This article makes the case for using two modules in your VBA/VB6 applications to provide both case-sensitive and case-insensitive text comparison operations.  Recently, I solved an EE question using the LIKE function.  In order for th…
    Introduction In a recent article ( for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
    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…

    761 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

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now