Array keeps returning Null Values

[Guest]

I am having trouble with the Array portion of the attached code - as I step
through it works fine until it hits where I am loading the Excel information
into the variable.
.Update VBA.Array("ID", "Fname", "Lname"), Array(vaData(i, 1),
vaData(i, 2), vaData(i, 3)) when I hover over vaData it will show me the
information from the cells but on the Next step I get an error Message
"Cannot insert the Value NULL into column ID, table
GPReports.dbo.My_Employees; column does not allow nulls. Insert fails. If I
were to remove restrictions from the table it post NULL in the row.

Here is the full code:
Sub Export_Data_Excel()

Dim cnt As ADODB.connection
Dim rst As ADODB.recordset
Dim xlCalc As XlCalculation
Dim rnData As Range, rnCell As Range
Dim stDB As String, stConn As String
Dim vaData As Variant

Dim i As Long

'Change settings in order to increase the performance.
With Application
xlCalc = .Calculation
.Calculation = xlCalculationManual
.EnableEvents = False
.ScreenUpdating = False
End With

Set rnData = ActiveSheet.Range("J21:l23")

'Instantiate the ADO COM's objects.
Set cnt = New ADODB.connection
Set rst = New ADODB.recordset

stDB = ThisWorkbook.Path & "\" & "FirstTry"

'Create the connectionstring
stConn =
"Provider=SQLOLEDB;Server=CorpDyndb;Trusted_Connection=Yes;Initial
Catalog=GPReports;UID=;"

'Populate the array with data from the range.
vaData = rnData.Value

'If the data is stored in rows instead of columns then the
'solution would be the following:
Set rnData = ActiveSheet.Range("j21:l23")
'vaData = Application.Transpose(rnData.Value)

cnt.Open stConn

'Open the recordset.
rst.Open "My_Employees", cnt, adOpenKeyset, adLockOptimistic,
adCmdTableDirect

'Read data, add new data and update the recordset.

For i = 1 To UBound(vaData)
With rst
.AddNew
.Update VBA.Array("ID", "Fname", "Lname"), Array(vaData(i, 1),
vaData(i, 2), vaData(i, 3))
End With
Next i
MsgBox "Successfully updated the table!", vbInformation

'Close recordset and connection.
rst.Close
cnt.Close

'Release objects from memory.
Set rst = Nothing
Set cnt = Nothing

'Clear inputrange.
rnData.ClearContents

'Restore the settings.
With Application
.Calculation = xlCalc
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub

Any ideas will be greatly appreciated.

Norma

 

[George Nicholson]

Guess #1:

I bet you have "Option Base 1" at the top of your module.

From the VB help entry on Array function:
"...The lower bound of an array created using the Array function is
determined by the lower bound specified with the Option Base statement,
unless Array is qualified with the name of the type library (for example
VBA.Array). If qualified with the type-library name, Array is unaffected by
Option Base...."

and since the default lower boundary for an array is 0, that means that your
Field name array (VBA.Array(..)) has lbound of 0 but your Value array (just
Array(..)) has lbound of 1. (i.e., there's a lbound mismatch)

*If* ADO puts the 2 arrays together by position, the first field (ID) won't
have a corresponding Value even though the arrays contain the same number of
elements.

Example: I'm thinking ADO may *internally* handle the assignments like this:
For i = lbound(varFields) to ubound(varFields)
' If lbound(varFields) = 0 and lbound(varValues) = 1,
' an out-of bound error occurs when i = 0 (the 1st pass).
' The error becomes a Null assignment.
rs.Fields(varfields(i)) = varValues(i)
Next i

Guess #2
Since the error message mentions "column ID", I would assume that
VBA.Array("ID", "Fname", "Lname") works.
Try prefacing Array(vaData(i, 1), vaData(i, 2), vaData(i, 3)) with VBA.
too.

Lots of guessing here. I'm very curious if that's the answer. Post back
(either way) if you can.

HTH,

 

[Guest]

Thank you for your suggestions. I did not have Option 1 Base in the
declarations section (thanks for pointing out - I had not checked that). I
made sure VBA was before Array. Still gives me the same error messages.

This should be a simple evolution but ????

Again, thanks.

Norma