VB6/ADO/SQL Server -- Error 3265 : Item not found in this collection ...

[marcohod]

Hi,
I'm using ADO 2.8 in VB6 to use an SQL Server 2000 database.

This works:
Dim MyRS2 As New ADODB.Recordset
MySQL = "Select * from table1"
MyRS2.Open MySQL, sqlConnection, adOpenKeyset, adLockOptimisti c
If MyRS2.Supports( adAddNew) Then
'With MyRS2
' .AddNew
' .Fields("Record number") = "12347"
' .Fields("COMPLE TE1") = "test3"
' .Update
'End With
End If
MyRS2.Close

Note: table1 is empty.

The following gives me the nasty 3265 error. Microsoft must assume we only have one-column tables so it does need to tell us what the offending name is!
Dim avarFldNames As Variant
Dim avarFldValues As Variant
Dim a1() As String, a2() As String

ReDim a1(1)
ReDim a2(1)
a1(1) = "Record number"
a2(1) = "12347"

avarFldNames = a1
avarFldValues = a2

Dim MySQL As String
Dim MyRS2 As New ADODB.Recordset
MySQL = "Select * from " & Me.tableNameBox
MyRS2.Open MySQL, sqlConnection, adOpenKeyset, adLockOptimisti c

If MyRS2.Supports( adAddNew) Then
MyRS2.AddNew avarFldNames, avarFldValues
End If
MyRS2.Close

I've tried doing a1(0) = "Record number" and a2(0) = "12347" --> same error

Any suggestions?

I need to fill hundreds of column/fields in the table, so hoping to use the array method. Am I correct in assuming that the array way would execute faster than the first way?

By the way, I haven't seen a decent example of using this array technique.


Thanks in advance,
--Mark

[ck9663]

i think this is more of a scripting question than a sql-server question. you might want to ask assistance on the scripting/web development/languages forum...

-- ck

[debasisdas]

Are you sure the fields exists in the table and are spelled correctly.