ADODB Recordsets

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

Maybe a little code or more explanation would help.  It is always hard to help without looking over someone's shoulder.

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

Are mixing up Country and Code?

It looks like it should be:

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

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.

TheLearenedOne(how modest), no I'm not mixing the 2 values up.

TimCotte - there are other fields in the recordset, yes.

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.

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

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.

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.

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.

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.