[RESOLVED] 1st column not in order on rs.AddNew? SQL.

[soconnor]

I have the following code

Do Until i = rs_dao.Fields.Count
 
Do Until rs_dao.EOF
If i = 0 Then
rs.AddNew (rs.Fields(0).Name), (rs_dao.Fields(0).Value)
Else
rs.Update (rs.Fields(i).Name), (rs_dao.Fields(i).Value)
End If
 
rs.MoveNext
rs_dao.MoveNext
 
Loop
 
i = i + 1
rs.MoveFirst
rs_dao.MoveFirst
 
Loop

rs - SQL Server recordset

rs_dao - Access recordset

All columns work fine apart from the first one on the rs.AddNew. It's not in order, i.e 1 - 10. But the other rs.Update columns are fine and in order

The rs database is a blank one so the inital row will have to be added through the AddNew function.

Im using SQL Server 2000, I noticed that if I toggle the NULLs on in the SQL table design and then run it works fine. I do it again and it doesn't. Then if I toggle the NULLs off again it'll work perfectly for the first time only.

Any ideas?

[Hack]

Moved to Database Development

[Campion]

Try this bit of code. It's based off of the example used in the MSDN.

Count = RS_DAO.RecordCount
Columns = RS_DAO.Fields.Count

Dim Fields() As Variant
Dim Values() As Variant

RS_DAO.movefirst
For i = 1 To Count
    ReDim Fields(Columns - 1)
    ReDim Values(Columns - 1)
    For ii = 0 To Columns
        Fields(ii) = RS_DAO.Fields.Item(ii).Name
        Values(ii) = RS_DAO.Fields.Item(ii).Value
    Next
    rs.AddNew Fields, Values
    rs.Update
    RS_DAO.MoveNext
Next

See this for more info:
http://msdn2.microsoft.com/en-us/lib...36(VS.85).aspx

[soconnor]

I tried it, the function doesn't seem to input any fields or values into the SQL table and comes back with no errors.

[Campion]

Step through it to check and see if the Fields and Values arrays are being filled, and then also if the record is updated. If not, you may have to try it using SQL.

[soconnor]

There was a problem with the Count and Columns so I just hardcoded rs_dao.recordCount into every instance of Count and did the same for Columns.

The only problem now is im getting the error 3265 item cannot be found in collection.

Any ideas?

[Campion]

I'm assuming you hard coded rs_dao.fields.count for columns.
What line is throwing the error, and WHEN does it happen?

[soconnor]

Ha ha yeah i did that for columns, I'm not that bad... I think

The ii seem to be throwing the error, so I changed it to an int j...

For j = 0 To rs_dao.Fields.Count
        Fields(j) = rs.Fields.Item(j).Name
        Values(j) = rs_dao.Fields.Item(j).Value
    Next j

This works and creates the correct values.

Now the next line is giving the error number: 9 Subscript out of range.

    rs.AddNew Fields, Values

[Campion]

When is it giving a subscript out of range? During the last iteration?

If so, then change the first IF..Then statement to "Do While not RS_dao.EOF" instead. I only used the If..Then for efficiency.

Also, why did you change the Fields line to just RS? Do the two tables use different column names? If they are not in the same same order, then using RS instead of RS_dao will cause the wrong data to go into the wrong column.

[soconnor]

The subscipt is out of range when it tries to add a new record.

Both tables have the same names, no. of columns and column order. I meant to change that rs back.

I changed it like you said, now back to the 3265 error, item not found!

[Campion]

I see your issue. Change:

For j = 0 To rs_dao.Fields.Count

to

For j = 0 To rs_dao.Fields.Count - 1

And I see where I went wrong (I can't test this code without a DB.) Using zero based with a non-zero based number makes it so you are trying to add to a column that does not exist.

So now, it should be:

Count = RS_DAO.RecordCount
Columns = RS_DAO.Fields.Count

Dim Fields() As Variant
Dim Values() As Variant

RS_DAO.movefirst
For i = 1 To Count
    ReDim Fields(Columns - 1)
    ReDim Values(Columns - 1)
    For ii = 0 To Columns - 1 'error was here 
        Fields(ii) = RS_DAO.Fields.Item(ii).Name
        Values(ii) = RS_DAO.Fields.Item(ii).Value
    Next
    rs.AddNew Fields, Values
    rs.Update
    RS_DAO.MoveNext
Next

[soconnor]

Excellent that worked. It inserts all the values correctly!! Thanks man!

Solution:

Dim Fields() As Variant
Dim Values() As Variant
 
rs_dao.MoveFirst
 
Do Until rs_dao.EOF 'i = rs_dao.recordCount
    ReDim Fields(rs_dao.Fields.Count - 1)
    ReDim Values(rs_dao.Fields.Count - 1)
    For j = 0 To rs_dao.Fields.Count - 1
        Fields(j) = rs.Fields.Item(j).Name
        Values(j) = rs_dao.Fields.Item(j).Value
    Next j
    rs.AddNew Fields, Values
    rs.Update
    rs_dao.MoveNext
Loop

Thanks again!