Subject Re: Insert a row into a Table, Not Append Blank
From Robbie Nott <robnott@mweb.co.za>
Date Fri, 12 Mar 2021 00:31:16 +0200
Newsgroups dbase.getting-started
Attachment(s) InsertRow.prg


Hi Stuart

This what I use.
No guarantee.
Not fancy, not polished, just the bare bones.









* Pass a table name, and 1 or 2key field names and values.

Procedure InsertLine
                *
                Parameters qTable, cKeyFieldName, vKeyFieldValue, cKeyFieldName2, vKeyFieldValue2
                *
                * Generic insert a line from gCodeParse
                *
                * Insert a line above the selected item
                *
                Local        aTableData, i, j, cFieldType
                Local nCols, lFirst, nArrayRows, bBook
                *
                
                If qTable.RowSet.EndOfSet
                        MsgBox("Please add records before inserting")
                        Return
                Endif
                
                *
                nCols = qTable.RowSet.Fields.Size
                bBook = qTable.RowSet.BookMark()
                lFirst = True
                *
                
                * Array to hold the Template records
                aTableData = New Array(1, nCols)
                
                qTable.RowSet.NotifyControls := False
                
                * Read the file from here down into the array
                Do while not qTable.RowSet.EndOfSet
                        *
                        If lFirst = True
                                *
                                lFirst := False
                                *
                        Else
                                aTableData.Grow(1)                        && Add a row
                        Endif
                        *
                        nArrayRows = aLen(aTableData, 1)                && Rows
                        *
                        For i = 1 to nCols
                                *
                                aTableData[nArrayRows, i] := qTable.RowSet.Fields[i].Value
                                *
                        Next i
                        *
                        qTable.RowSet.Next()
                        *
                EndDo
                
                * Go back to the bookmark
                * and delete all the records from here on down
                qTable.RowSet.GoTo(bBook)
                *
                If lFirst = True
                        *
                        qTable.RowSet.NotifyControls := True
                        MsgBox("No records were found")
                        Return
                        *
                Endif
                *
                
                
                If empty(cKeyFieldName)
                        nKeyFieldNumber = 0
                Else
                        * Find the key field's number
                        nKeyFieldNumber = 0
                        For i = 1 to nCols
                                If qTable.RowSet.Fields[i].FieldName = cKeyFieldName
                                        nKeyFieldNumber := i
                                        Exit
                                Endif
                        Next i
                        *
                Endif
                
                
                * Delete the rest of the table under the current one
                Do while not qTable.RowSet.EndOfSet
                        *
                        * No key field
                        If empty(cKeyFieldName)
                                * just delete it
                                qTable.RowSet.Delete()
                        Else
                                * Double check in case a setrange() isn't working or something
                                *  don't want to delete the entire table...
                                If qTable.RowSet.Fields[nKeyFieldNumber].Value = vKeyFieldValue
                                        qTable.RowSet.Delete()
                                Else
                                        qTable.RowSet.Next()
                                Endif
                        Endif
                        *
                EndDo
                
                
                * Now append the new row, then dump the array
                *
                
                * Nah - just renumber the whole range afterwards
                * Get the line number from the array
                
                qTable.RowSet.BeginAppend()
                *
                
                cFieldType = ""
                * Populate the fields according to their type
                For i = 1 to qTable.RowSet.Fields.Size
                        *
                        * If there's no keyfield
                        If empty(cKeyFieldName)
                                * zip
                        Else
                                *
                                * First key
                                If qTable.RowSet.Fields[i].FieldName = cKeyFieldName
                                        qTable.RowSet.Fields[i].Value := vKeyFieldValue
                                        Loop
                                Endif
                                *
                                * Second key field and value
                                If pCount() >= 5
                                        *
                                        If qTable.RowSet.Fields[i].FieldName = cKeyFieldName2
                                                qTable.RowSet.Fields[i].Value := vKeyFieldValue2
                                                Loop
                                        Endif
                                        *
                                Endif
                                *
                        Endif
                        *
                        
                        
                        *
                        If qTable.RowSet.Fields[i].FieldName = "_DBASELOCK"
                                * Ignore this field
                                Loop
                        Endif
                        *

                        *
                        cFieldType := Substr(qTable.RowSet.Fields[i].Type, 1, 1)
                        *
                        Do Case
                                Case SubStr(cFieldType,1,1) = "C"
                                        *
                                        qTable.RowSet.Fields[i].Value := ""
                                        *
                                Case cFieldType                                  = "N"
                                        qTable.RowSet.Fields[i].Value := 0
                                        *
                                Case SubStr(cFieldType,1,1) = "D"
                                        qTable.RowSet.Fields[i].Value := Dtoc("")
                                        *
                                Case SubStr(cFieldType,1,1) = "L"
                                        qTable.RowSet.Fields[i].Value := False
                                        *
                                Otherwise
                                        *
                                        * Don't care
                                        *
                        EndCase
                        *
                Next i
                
                qTable.RowSet.Save()
                
                * Get a new bookmark
                bBook = qTable.RowSet.BookMark()
                
                
                * Now dump the array into the table, creating records after the blank line
                *
                nArrayRows = aLen(aTableData, 1)                && Rows
                * nArrayCols = aLen(aTableData, 2)                && Cols
                
                *
                For i = 1 to nArrayRows
                        *
                        qTable.RowSet.BeginAppend()
                        *
                        For j = 1 to nCols
                                
                                qTable.RowSet.Fields[j].Value := aTableData[i,j]
                        
                        Next j
                        *
                        qTable.RowSet.Save()
                        *
                Next i
                
                
                
                qTable.RowSet.NotifyControls := True
                qTable.RowSet.GoTo(bBook)
                
                
                
                
                Return