The following functions implement a simple way to do this.
Limitations
- the current copy row routine copies all columns in a result set - for example all rows in a table grid.
- the routine for all practicle purposes will work with only a single table.
- if some columns have default values set at the database these willbe ignored. ( an example might be a row for the current entry time )
How to use this routine.
The simplest way would be to add a button to a form and then assign the 'When initializing' event on the button to something like this
Sub onBtnClickCopyRow( oEvent as object )
copyRow( oEvent.Source.Model.Parent )
end sub
Code: Select all
function CopyRow( aRS as com.sun.star.sdb.ResultSet ) as boolean
dim oCloneRS as variant
dim ColCntr as integer
CopyRow = false
if aRS.ResultSetConcurrency = com.sun.star.sdbc.ResultSetConcurrency.READ_ONLY then
msgBox( "ResultSet is Read Only" )
exit function
else
if aRS.IsModified then
if msgBox( "Save record before copy?",4,"Current Record Modified") <> 6 then
exit function
else
if aRS.IsNew then
aRS.InsertRow
else
aRS.UpdateRow
end if
end if
else
oCloneRS = aRS.createResultSet()
aRS.moveToInsertRow()
for ColCntr = 1 to aRS.Columns.Count -1
CopyColumn( oCloneRS, aRS, ColCntr )
next
CopyRow = True
end if
end if
end function
function CopyColumn( aRSSource as com.sun.star.sdb.ResultSet, _
aRSTarget as com.sun.star.sdb.ResultSet, _
aColNum as Integer ) as boolean
dim dType as integer
CopyColumn = False
' DO NO copy an autoincrement field
if aRSSource.Columns( aColNum ).isAutoIncrement then
CopyColumn = True
else
dType = aRSSource.Columns( aColNum ).Type
select case dType
case com.sun.star.sdbc.DataType.BIT
aRSSource.Columns( aColNum ).getByte
if not aRSSource.Columns( aColNum ).wasNull then
aRSTarget.Columns( aColNum ).UpdateByte( aRSSource.Columns( aColNum ).getByte )
else
aRSTarget.Columns( aColNum ).UpdateNull
endif
case com.sun.star.sdbc.DataType.TINYINT
aRSSource.Columns( aColNum ).getByte
if not aRSSource.Columns( aColNum ).wasNull then
aRSTarget.Columns( aColNum ).UpdateByte( aRSSource.Columns( aColNum ).getByte )
else
aRSTarget.Columns( aColNum ).UpdateNull
endif
case com.sun.star.sdbc.DataType.SMALLINT
aRSSource.Columns( aColNum ).getShort
if not aRSSource.Columns( aColNum ).wasNull then
aRSTarget.Columns( aColNum ).UpdateShort( aRSSource.Columns( aColNum ).getShort )
else
aRSTarget.Columns( aColNum ).UpdateNull
endif
case com.sun.star.sdbc.DataType.INTEGER
aRSSource.Columns( aColNum ).getInt
if not aRSSource.Columns( aColNum ).wasNull then
aRSTarget.Columns( aColNum ).UpdateInt( aRSSource.Columns( aColNum ).getInt )
else
aRSTarget.Columns( aColNum ).UpdateNull
endif
case com.sun.star.sdbc.DataType.BIGINT
aRSSource.Columns( aColNum ).getLong
if not aRSSource.Columns( aColNum ).wasNull then
aRSTarget.Columns( aColNum ).UpdateLong( aRSSource.Columns( aColNum ).getLong )
else
aRSTarget.Columns( aColNum ).UpdateNull
endif
case com.sun.star.sdbc.DataType.FLOAT, _
com.sun.star.sdbc.DataType.REAL, _
com.sun.star.sdbc.DataType.DECIMAL, _
com.sun.star.sdbc.DataType.NUMERIC
aRSSource.Columns( aColNum ).getFloat
if not aRSSource.Columns( aColNum ).wasNull then
aRSTarget.Columns( aColNum ).UpdateFloat( aRSSource.Columns( aColNum ).getFloat )
else
aRSTarget.Columns( aColNum ).UpdateNull
endif
case com.sun.star.sdbc.DataType.CHAR, _
com.sun.star.sdbc.DataType.VARCHAR, _
com.sun.star.sdbc.DataType.LONGVARCHAR
aRSSource.Columns( aColNum ).getString
if not aRSSource.Columns( aColNum ).wasNull then
aRSTarget.Columns( aColNum ).UpdateString( aRSSource.Columns( aColNum ).getString )
else
aRSTarget.Columns( aColNum ).UpdateNull
endif
case com.sun.star.sdbc.DataType.DATE
aRSSource.Columns( aColNum ).getDate
if not aRSSource.Columns( aColNum ).wasNull then
aRSTarget.Columns( aColNum ).UpdateDate( aRSSource.Columns( aColNum ).getDate )
else
aRSTarget.Columns( aColNum ).UpdateNull
endif
case com.sun.star.sdbc.DataType.TIME
aRSSource.Columns( aColNum ).getTime
if not aRSSource.Columns( aColNum ).wasNull then
aRSTarget.Columns( aColNum ).UpdateTime( aRSSource.Columns( aColNum ).getTime )
else
aRSTarget.Columns( aColNum ).UpdateNull
endif
case com.sun.star.sdbc.DataType.TIMESTAMP
aRSSource.Columns( aColNum ).getTimeStamp
if not aRSSource.Columns( aColNum ).wasNull then
aRSTarget.Columns( aColNum ).UpdateTimeStamp( aRSSource.Columns( aColNum ).getTimeStamp )
else
aRSTarget.Columns( aColNum ).UpdateNull
endif
case com.sun.star.sdbc.DataType.BOOLEAN
aRSSource.Columns( aColNum ).getBoolean
if not aRSSource.Columns( aColNum ).wasNull then
aRSTarget.Columns( aColNum ).UpdateBoolean( aRSSource.Columns( aColNum ).getBoolean )
else
aRSTarget.Columns( aColNum ).UpdateNull
endif
case com.sun.star.sdbc.DataType.CLOB
aRSSource.Columns( aColNum ).getClob
if not aRSSource.Columns( aColNum ).wasNull then
aRSTarget.Columns( aColNum ).UpdateClob( aRSSource.Columns( aColNum ).getClob )
else
aRSTarget.Columns( aColNum ).UpdateNull
endif
case com.sun.star.sdbc.DataType.SQLNULL
aRSTarget.Columns( aColNum ).UpdateNull
end select
end if
CopyColumn = True
end function