Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011

    Unanswered: 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!
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011

    Updating SQL Server from Excel with ADO

    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!
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  3. #3
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011

    Identity could not be determined for newly inserted rows

    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!
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •