OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

[OOoBasic] Inserting row in database without duplicating it

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API
View previous topic :: View next topic  
Author Message
Douzout
General User
General User


Joined: 01 Aug 2005
Posts: 19
Location: France (Réunion)

PostPosted: Tue Aug 23, 2005 3:53 am    Post subject: [OOoBasic] Inserting row in database without duplicating it Reply with quote

Hello ! Smile

I made a function to insert a row in database

Code:


InsertionTableDPI:

Dim maRequete As Object, resuQuery As Object
Dim instrSQL As String
Dim insertion As Long

instrSQL  = " insert into DPI (IEP, UF) values ('"& iepSaisie &"', '"& ufSaisie &"') "
maRequete = maConnexion.createStatement()
insertion  = maRequete.executeUpdate(instrSQL)]



I would like to add some code that would make :
" If this row already exists then overwrite it"
because for the moment it creates an other row with exactly the same values.

Is it possible ? I don't find how to do.

Thank you for helping Smile

--
Guillaume, D-14
Back to top
View user's profile Send private message Send e-mail Visit poster's website MSN Messenger
probe1
Moderator
Moderator


Joined: 18 Aug 2004
Posts: 2560
Location: Chonburi Thailand Asia

PostPosted: Tue Aug 23, 2005 5:25 am    Post subject: Reply with quote

Try and read the record in question, when notfound: insert, when found: update

Wink
_________________
Cheers
Winfried
My Macros
DateTime2 extension: insert date, time or timestamp, formatted to your needs
Back to top
View user's profile Send private message Visit poster's website
DrewJensen
Super User
Super User


Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

PostPosted: Tue Aug 23, 2005 6:02 am    Post subject: Reply with quote

The steps Probe1 mentioned are it.

If you want to insure that you never get a second record in the table you should also add a UNIQUE index to the table that uses both the IEP and UF columns.

You can do this either from the edit dialog for the table by selecting menu TOOLS>Index Design

or from the SQL window at the databae enter the command

Code:
ALTER TABLE  DPI ADD CONSTRAINT IDX_UNIQ UNIQUE ( IEP, UF )



With this constriant in place your insert statement would generate an error instead of adding a new record.

Drew
_________________
Blog - http://baseanswers.spaces.live.com/
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Douzout
General User
General User


Joined: 01 Aug 2005
Posts: 19
Location: France (Réunion)

PostPosted: Tue Aug 23, 2005 9:42 pm    Post subject: Reply with quote

Thank you very much ! Smile

First I made what DrewJensen asked me : ALTER TABLE DPI ADD CONSTRAINT IDX_UNIQ UNIQUE ( IEP, UF )

Then I wrote :

Code:

InsertionTableDPI:
Dim maRequete As Object, resuQuery As Object
Dim instrSQL As String
Dim insertion As Long

instrSQL = " select * from DPI where IEP = '" & iepSaisie & "' AND UF = '" & ufSaisie & "'"
maRequete = maConnexion.createStatement()
resuQuery  = maRequete.executeQuery(instrSQL)
   With resuQuery

If execOK = .next Then
 maRequete = maConnexion.CreateStatement()
 On Error Resume Next
 resuQuery = maRequete.executeUpdate(" delete from DPI where IEP = '" & iepSaisie & "' and UF = '" & ufSaisie & "'")
End If

instrSQL  = " insert into DPI (IEP, UF) values ('"& iepSaisie &"', '"& ufSaisie &"') "
maRequete = maConnexion.createStatement()
insertion = maRequete.executeUpdate(instrSQL)
   End With


If I don't write "On Error Resume Next",
the row is deleted, but I have an error on
Code:
 
resuQuery = maRequete.executeUpdate(" delete from DPI where IEP = '" & iepSaisie & "' and UF = '" & ufSaisie & "'")


(×) Incorrect use of the object

But now it seems to work... Cool

Thank you very much ! Very Happy
If you think this code isn't very academic, tell me Smile

--
Guillaume, who will test the REAL database now Confused
Back to top
View user's profile Send private message Send e-mail Visit poster's website MSN Messenger
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group