Updating SQL Server from Excel with ADO
Hello,
Since this hits ADO, Excel and SQL Server, I'm posting it across groups.
My Estimate creation/information is in Excel. One of the things it does is determine which comments (verbiage from a default library) apply.
The spreadsheet has a column of ID values for each comment and a true/false to determine if each comment applies. The ID values correspond to a table of "Estimate Comments" in SQL Server.
I have a routine to cycle through the values and only add the ID's for comments that apply, but it doesn't get past the first one because of that pesky message:
Identity could not be determined for newly inserted rows
Editing existing rows works fine, but adding new ones seems to not work.
Here's what I have:
Code:
' Estimate Level Notes
With Sheets("EstimateNotes")
SpecId = Val(Sheets("Input").Range("J23")) ' Get the spec ID
If SpecId > 0 Then
sSql = "SELECT ItemSpecID, NoteID, IncludeNote FROM EstimateDetailNotes WHERE ItemspecID =" & SpecId
Set rs = New ADODB.Recordset
rs.Open sSql, gcn, adOpenKeyset, adLockOptimistic
If rs.RecordCount > 0 Then ' clear the existing note references
Do
rs.Fields("IncludeNote") = 0
rs.Update
rs.MoveNext
Loop Until rs.EOF
rs.MoveFirst
End If
nLastRow = Val(.Range("M1")) ' this allows setting the limit sheet side
If nLastRow > 0 Then
For Row = 1 To nLastRow
iC = .Range("B" & Row + 4).Value ' this checks the "include me or not" value
If iC = 1 Then ' include me
If rs.EOF Then
rs.AddNew ' ###The first "AddNew" works, the second one trips the error message ###
blNew = True
End If
rs.Fields("ItemSpecID") = SpecId
rs.Fields("NoteID") = .Range("A" & Row + 4).Value ' the actual note ID (matching SQL Server's notes table)
rs.Fields("IncludeNote").Value = iC
rs.Update
If Not blNew Then rs.MoveNext
End If
Next
End If
End If
End With
Any ideas?
Thanks!
dBforums
>
Updating SQL Server from Excel with ADO
01-16-12, 17:32
Linear Mode
