Hi,
Attached is a small basic module.
If you would be willing to hep me check it out:
Import the bas file into a basic library embedded in an ODB file - will
not work as a stand alone Basic library
Create a form with the Property Insert Data Only true.
You should make sure the dataform Cycle property is set to "Active Record "
You should disable the navigator.
OK - you can add a button to the form, a custom toolbar or a custom menu
assign the macro insertFirstDataForm
Just try it - let me know if you get any errors, please.
I've used it with HSQLdb and dBase Base files w/out problem.
MS Access has an error in the executeUpdate command on the prepared
statement.
MySQL and PostgerSQL haven't checked.
Anyway, if anyone gives it a try I'd appreciate it.
Thanks
Drew
' Copyright (C) 2010 Andrew 'Drew' Jensen
' atjen...@openoffice.org
'
' This program is free software: you can redistribute it and/or modify
' it under the terms of the GNU General Public License as published by
' the Free Software Foundation, either version 3 of the License, or
' (at your option) any later version.
'
' This program is distributed in the hope that it will be useful,
' but WITHOUT ANY WARRANTY; without even the implied warranty of
' MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
' GNU General Public License for more details.
'
' To review a full a copy of the GNU General Public License
' please see, <http://www.gnu.org/licenses/>.
'
'//
'// The library module was intended to be used as a aid in
'// working around an anomily in the 3.2 release of OpenOffice.org Base
'//
'// For reference see:
'// http://www.openoffice.org/issues/show_bug.cgi?id=108377
'//
'// The library includes 4 rooutines for use in these
'// Insert Data Only forms
'//
'// Two are suitable for use from menu, toolbar, HotKey,
'// or dialog displayed by data entry form
'//
'// insertdFistDataForm
'// reloadFistDataForm
'//
'// Two when called from a button owned by the dataform
'// that owns the controls to write to the database
'//
'// insertThisDataForm
'// reloadThisDataForm
option explicit
'// GLOBALS
'//
GLOBAL BoundFields() as string
'// Timestamp fields can be associeated with
'// both a date and time GUI control
'//
'// In face any data field could be bound to any
'// number of GUI Control Models
'//
'// Data fields do not have to be bound to
'// any GUI Control Model
'//
'// in our loops then we will iterated
'// over the GUI Control Models
'// to avoid any non bound data fields
'//
'// but this means we must watch for
'// controls bound to more then one
'// data field - such as timestamp fields
'//
'// to do this we will employ an array
'// to keep track of each field name
'// we come across as we move through
'// the Control Models
'//
'// haveField
'//
'// helper function for insertDataForm
'//
function haveField( DataFieldName as string ) as boolean
dim cntr
dim foundit as boolean
if UBound(BoundFields) = 0 then
haveField = False
REDIM BoundFields(1)
BoundFields(1) = DataFieldName
else
foundit = false
for cntr = 0 to UBound(BoundFields) - 1
if BoundFields(cntr) = DataFieldName then
foundit = True
end if
next
if not foundit then
cntr = UBound(BoundFields)
REDIM BoundFields( cntr + 1 )
BoundFields( uBound(BoundFields) ) = DataFieldName
haveField = False
else
haveField = True
end if
end if
end function
'//
'// reloadFistDataForm
'//
'// can be called from menu or toolbar or HotKey
'// can be called from dialog displayed by data entry form
'//
'// will clear all controls of the first dataform
'// on the top most base form window
'//
sub reloadFistDataForm()
'//
'// only want to work with forms
'//
'// a Query window will not be picked up with
'// in ActiveFrame
'// NULL Model then
if isNull(
thisDatabaseDocument.CurrentController.Frame.ActiveFrame.Controller.Model ) then
exit sub
end if
'//
'// Report Builder editor
'//
if
thisDatabaseDocument.CurrentController.Frame.ActiveFrame.Controller.Model.supportsService("com.sun.star.sdb.ReportDesign")
then
exit sub
end if
'// ASSUME - ASSUME - ASSUME
'// this is a form
'// unlses of course it is a ReportWizard report?
'//
thisDatabaseDocument.CurrentController.Frame.ActiveFrame.Controller.Model.DrawPage.Forms(0).reload
end sub
'//
'// insertFirstDataForm
'//
'// can be called from menu or toolbar or Kotkey
'// can be called from dialog displayed by data entry form
'//
'// will write the values from the bound data columns
controls
'// of the first dataform on the top most base form window
'//
sub insertFirstDataForm()
'//
'// only want to work with forms
'//
'// a Query window will not be picked up with
'// in ActiveFrame
'// NULL Model then
if isNull(
thisDatabaseDocument.CurrentController.Frame.ActiveFrame.Controller.Model ) then
exit sub
end if
'//
'// Report Builder editor
'//
if
thisDatabaseDocument.CurrentController.Frame.ActiveFrame.Controller.Model.supportsService("com.sun.star.sdb.ReportDesign")
then
exit sub
end if
'// ASSUME - ASSUME - ASSUME
'// this is a form
'// unlses of course it is a ReportWizard report?
'//
InsertDataForm(
thisDatabaseDocument.CurrentController.Frame.ActiveFrame.Controller.Model.DrawPage.Forms(0)
)
end sub
'//
'// reloadThisDataForm
'//
'// can be called from a button that has a dataform as
parent
'//
sub reloadThisDataForm( oEvent as object )
oEvent.Source.Model.Parent.reload
end sub
'//
'// insertThisDataForm
'//
'// can be called from a button that has a dataform as
parent
'//
sub insertThisDataForm( oEvent as object )
InsertDataForm( oEvent.Source.Model.Parent )
end sub
'//
'// insertDataForm
'// do it
'//
sub InsertDataForm(oDataForm as object )
dim CurrentConnection
dim CurrentControl
dim cntr, fldcnt
dim strCmdFirst, strCmdLast, SQLCmd, QuoteString
dim QryComposer
dim prepStatement
'//
'// use the connection that is
'// used by the datatform control
'//
CurrentConnection = oDataForm.ActiveCOnnection
'//
'// only inerested in quote used for identifieers
'//
QuoteString = CurrentConnection.MetaData.IdentifierQuoteString
'//
'// use this for getting different
'// parts of the SQL command used
'// by the dataform control
'// in our case here
'// the table name for the SQL statement
'//
QryComposer = CurrentConnection.createQueryComposer
'//
'// populate the composer with the
'// current dataforms SQL command
'//
QryComposer.Query = oDataForm.ActiveCommand
'// reset our BoudFields array for keeping
'// track of duplicate data fields
'//
REDIM BoundFields(0)
'//
'// build the two parts of
'// of an SQL insert statement
'//
'//
'// iterate over the contols
'// to extract the column names
'// from the bound data controls
'//
for cntr = 0 to oDataForm.Count - 1
CurrentControl = oDataForm.ControlModels(cntr)
select case CurrentControl.ServiceName
'//
'// include only those controls types
'// that support bound data controls
'//
case "stardiv.one.form.component.Edit",
"stardiv.one.form.component.DateField",
"stardiv.one.form.component.ImageControl"
if not haveField(
CurrentControl.BoundField.Name ) then
strCmdFirst = strCmdFirst + QuoteString
+ CurrentControl.BoundField.Name + QuoteString
strCmdLast = strCmdLast + " ? "
if cntr <> oDataForm.Count - 1 then
strCmdFirst = strCmdFirst + +
", "
strCmdLast = strCmdLast + ", "
end if
end if
end select
next
'//
'// build the final SQL statement
'//
SQLCmd = "INSERT INTO " + QuoteString + QryComposer.Tables(0).Name +
QuoteString + " ( " + strCmdFirst + " ) VALUES ( " + strCmdLast + " )"
'//
'// and prepare it for data
'//
prepStatement = CurrentConnection.PrepareStatement( SQLCmd )
'// reset our BoudFields array for keeping
'// track of duplicate data fields
'// again
'//
REDIM BoundFields(0)
'//
'// again iterate over the contols
'// this time to fill the
'// parameters in the prepared statement
'// with data
'//
for cntr = 0 to oDataForm.Count - 1
CurrentControl = oDataForm.ControlModels(cntr)
select case CurrentControl.ServiceName
case "stardiv.one.form.component.Edit",
"stardiv.one.form.component.DateField",
"stardiv.one.form.component.ImageControl"
if not haveField(
CurrentControl.BoundField.Name ) then
'//
'// fldnct <>
oDataForm.Count
'// only count controls
with bound columns
'//
fldcnt = fldcnt + 1
'//
'// ensure that data in GUI conttol
is
'// written to the bound data
controls
'//
'// no commit for image
controls?
'//
if CurrentControl.ServiceName <>
"stardiv.one.form.component.ImageControl" then
CurrentControl.commit
end if
'//
'// fetch the columns value
'// to make wasNull valid
'//
CurrentControl.BoundField.getString
'//
'// NULLS get special attention
'//
if CurrentControl.BoundField.wasNull
then
'//
'// should account
for different data typs
'// but for
expediency
'//
prepStatement.setNull( fldcnt,
0 )
else
'//
'// now put the
data into
'// the prepared
statement
'// parameters
'//
'// image controls
are special
'//
if CurrentControl.ServiceName =
"stardiv.one.form.component.ImageControl" then
'//
'//
setBinaryStream insted of setBlob
'// as
setBlob is not implemented on all
'// drivers
'//
dim oStream as object
oStream =
CurrentControl.BoundField.getBinaryStream
prepStatement.setBinaryStream( fldcnt, oStream, oStream.Length )
else
'//
'// for
expediency
'// move
the rest as strings
'//
prepStatement.setString( fldcnt, CurrentControl.BoundField.getString )
end if
end if
end if
end select
next
'// set error trap
'//
on error goto executeUpdateError
'//
'// post the data to the table
'//
prepStatement.executeUpdate
'//
'// clean up the GUI controls
'// by reseting the dataform
'//
oDataForm.Reload
'//
'// and exit
exit sub
'//
'// error on insert
'// tell and resume
'//
executeUpdateError:
MsgBox "Error " & Err & ": " & Error$ + chr(13) + "At line : " + Erl +
chr(13) + Now , 16 ,"<< insertDataForm >>"
end sub
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org
For additional commands, e-mail: dev-h...@dba.openoffice.org
