[Webinar] Streamline your web hosting managementRegister Today

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

ADODB Recordsets

Hi,

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

Nik.

0
NikWhitfield
Asked:
NikWhitfield
  • 6
  • 4
  • 2
  • +1
1 Solution
 
TimCotteeCommented:
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
    Adodc1.Refresh
0
 
Bob LearnedCommented:
Maybe a little code or more explanation would help.  It is always hard to help without looking over someone's shoulder.
0
 
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").
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Bob LearnedCommented:
Are mixing up Country and Code?

It looks like it should be:

astrValues(0) = "Great Britain"
astrValues(1) = "GB"
0
 
TimCotteeCommented:
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.
0
 
NikWhitfieldAuthor Commented:
TheLearenedOne(how modest), no I'm not mixing the 2 values up.

TimCotte - there are other fields in the recordset, yes.
0
 
TimCotteeCommented:
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.AddNew
rsData!Country = "GB"
rsData!Code = "Great Britain"
rsData.Update

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.
0
 
lpsharmaCommented:
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
0
 
TimCotteeCommented:
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.
0
 
NikWhitfieldAuthor Commented:
This is was obtained through the immediate window......
****************************************
?astrFieldList(0)
PRODUCT_ID
?astrFieldList(1)
PRODUCT_NAME
****************************************
?values(0)
5
?values(1)
Evacuation
****************************************
?rstempdata.Fields(0).Name
LOCATION_HIERARCHY_VERSION_ID
?rsTempData.Fields(1).Name
PRODUCT_ID
?rstempdata.Fields(2).Name
PRODUCT_NAME
****************************************

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.
0
 
TimCotteeCommented:
But you could use
for intCount = 0 to ubound(aryFields)
  recordset.fields(aryFields(intCount)).value = aryValues(intCount)
next

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


0
 
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.
0
 
TimCotteeCommented:
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.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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