ADODB Recordsets


   I'm using an ADODB Recordset(Microsoft ActiveX Data Objects 2.1) and am having problems with the .addnew method. When I try and add a new record passing the two neccesary arrays (fields and values) I get an error saying :
"ADO could not find the object in the collection corresponding to the name or ordinal reference requested by the application".

However, the fields passed match those in the recordset exactly. What's going on? Any help appreciated.....


Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TimCotteeHead of Software ServicesCommented:
This is an example of using the .addnew method with fields and values lists, hope it helps.

    Dim aryFields(2)
    Dim aryValues(2)
    aryFields(0) = "Num"
    aryFields(1) = "id"
    aryFields(2) = "forname"
    aryValues(0) = 12
    aryValues(1) = 12
    aryValues(2) = "Tim 2"
    Adodc1.Recordset.AddNew aryFields, aryValues
Bob LearnedCommented:
Maybe a little code or more explanation would help.  It is always hard to help without looking over someone's shoulder.
NikWhitfieldAuthor Commented:
TimCottee - what are you dimming adadc1 as?

My code would look pretty much exactly as your does but it doesn't work.

dim astrFields(0 to 1)
dim astrValues(0 to 1)
dim rsData as recordset

astrFields(0) = "Country"
astrFields(1) = "Code"

astrValues(0) = "GB"
astrValues(1) = "Great Britain"

rsData.addnew astrFields, astrValues.

This produces the error mentioned above. As I mentioned before, the fields within the recordset are named correctly (in this case "Country" and "Code").
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Bob LearnedCommented:
Are mixing up Country and Code?

It looks like it should be:

astrValues(0) = "Great Britain"
astrValues(1) = "GB"
TimCotteeHead of Software ServicesCommented:
Adodc1 is just an ADO data control, I was using it just to test this out. Adodc1.Recordset is in fact an ado recordset equivalent to your rsData. Are the two fields you show the only ones in the table, or are there others? If so this may be part of the problem.
NikWhitfieldAuthor Commented:
TheLearenedOne(how modest), no I'm not mixing the 2 values up.

TimCotte - there are other fields in the recordset, yes.
TimCotteeHead of Software ServicesCommented:
My assumption was that if there were other fields then it might be that you have to declare all the fields and values. Having tried to get this to fail it doesn't, that of course doesn't help you much though. I assume that if you try to break out the code along the lines of:

rsData!Country = "GB"
rsData!Code = "Great Britain"

It works, anyway try it and see, if not it may give a better clue as to which field(s)/value(s) are causing the error.
The particular error that U mention occurs ONLY IF there is some discrepency in the field names or order of the fields (in your case). This is a very specific error and does not occur otherwise. Please cross-check the field names in your code with those in the actual database. Note: Check if U have included any spaces in your code.. Hope this helps
TimCotteeHead of Software ServicesCommented:
lpsharma, you obviously didn't read the question thoroughly, Nik says that the fields do match exactly. However I do agree that that is what the error implies, and why I asked Nik to try it out a different way to see whether the error is generated again. If so it will be much easier to see which field / value is causing the problem.
NikWhitfieldAuthor Commented:
This is was obtained through the immediate window......

As you can see, the Field names are identical in the recordset and the fields array.

When I try using the other syntax (recordset!FIELDNAME = "VALUE") it works OK. I cannot, however, use this syntax because the ActiveX control of which it is part will not know the format of the recordsets to be passed into it. This is why I must collate the Field information and then use the addnew method passing arrays.
TimCotteeHead of Software ServicesCommented:
But you could use
for intCount = 0 to ubound(aryFields)
  recordset.fields(aryFields(intCount)).value = aryValues(intCount)

Where aryFields contains the field numbers/names and aryValues contains the values.

NikWhitfieldAuthor Commented:
I could do that, but there's a requirement that the data is removed from another recordset previously (also without knowing the field structure). For this reason, it must be by name.

However, I have solved the problem myself - and it's not the most obvious. Basically, the arrays were being declared as string arratys. If this is changed to arrays of variants, the problem disappears! So Ipsharma - you are wrong - the error occurs if the type of the array is incorrect, this error will be produced.

I guess i have to give the points away, so TimCottee, stick an answer in and you can have 'em.
TimCotteeHead of Software ServicesCommented:
Thanks Nik, I know it hurts when you find an answer yourself but it may help someone else. Thanks anyway, I will try to remember this if I ever need it.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.