Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 168
  • Last Modified:

Multiple Step operation generated errors

Hi

I have 2 stored procs like

1. Select T1.A, T1.B, T2.C
    From T1, T2
    where 0 = 1

This returns me an empty recordset

2. select T1.A, T1.B, T3.D, T4.E
    From T1, T3, T4
    where (**some conditions-**)

In VB I transfer data between these recordsets based on certain criteria.
1. RecordSet1.Addnew
2. RecordSet1.Fields("A").Value = RecordSet2.Fields("A").Value
3. RecordSet1.Fields("B").Value = RecordSet2.Fields("B").Value
4. RecordSet1.Fields("C").Value = Some constant value.

Now my problem is with line 2 above.
This is where i get the multi-step error.
If i skip this step and move on there are no errors.

I am getting this field A in both recordsets from the same table, so there shud not be a datatype related error.
field A is primary Key on the table.

Please help me get over this issue.
Let me know if you need more information.

Thanks
Abhi
0
pangaria
Asked:
pangaria
  • 3
  • 3
  • 2
  • +3
1 Solution
 
gbzhhuCommented:
The first thing I would do is display the contents to make sure they are valid

so

msgbox RecordSet2.Fields("B").Value
0
 
pangariaAuthor Commented:

Well, all values in recordset2 are good.
there are no null values either.

the problem is with field A assignment.
msgbox RecordSet2.Fields("A").Value works fine and so do msgboxes with all other fields.


0
 
gbzhhuCommented:
Check the value for RecordSet1.Fields.Count  
How many is in there.  If this zero then you have a problem.  You may have to add fields into the fields collection

RecordSet1.Fields.add()   'I don't know the parameters, check documentation
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
pangariaAuthor Commented:
Recordset1 is formed from query 1
1. . Select T1.A, T1.B, T2.C
    From T1, T2
    where 0 = 1

Recordset 2 is formed from query 2
2. select T1.A, T1.B, T3.D, T4.E
    From T1, T3, T4
    where (**some conditions-**)

i dont think it would give a multi-step error if fields are not available.
0
 
gbzhhuCommented:
But you said query 1 returned empty recordset.  I sm not really an ADO savvy at all but could you try this


Replace line 1 and 2 with with

RecordSet1.Fields.Append("A",adBSTR,len(RecordSet2.Fields("A").Value),,RecordSet2.Fields("A").Value)
0
 
PSSUserCommented:
The problem is the locking on the table. Am I correct in thinking this is an MS Access database? (I don't believe this is a problem in SQL server).

Your select statement has taken a lock on the required row, either through a table lock or locking the page the row is on.

The way I have worked around this in the past is to put the contents on the second record set into variables, close it, then carry out the update. If you have more than one record in the second record set then I would suggest using a collection to put the records into, and then looping through the collection once the second recordset is closed.
0
 
pangariaAuthor Commented:
I am sorry, I forgot to mention.
It is SQL Server 2000. MDAC 2.6
0
 
PSSUserCommented:
Might still be worth trying my suggestion. I'm sure I've only had this problem on MS Access, although I can't be certain. I'm using MDAC 2.8 (not sure if that makes the difference!).
0
 
RainUKCommented:
Have you tried the simple checks?

RecordSet1.Fields("A").Value = RecordSet2.Fields("A").Value

1. Are both the recordset fields the same data type? Not just in type but also in length. e.g. if RS1.FieldA is  nvarchar(3) then inputting a value a string greater than 2 in length is not going to work.
0
 
RainUKCommented:
if RS1.FieldA is  nvarchar(3) then inputting a value a string greater than 3 in length is not going to work.
0
 
GPrentice00Commented:
Excellent points made by RainUK, but which may not specifically be the problem here - but good info for other frustrated info seekers to keep in mind.
0
 
moduloCommented:
PAQed with no points refunded (of 500)

modulo
Community Support Moderator
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now