| View previous topic :: View next topic |
| Author |
Message |
Douzout
General User
Joined: 01 Aug 2005
Posts: 19
Location: France (Réunion)
|
 Posted: Tue Aug 23, 2005 3:53 am Post subject: [OOoBasic] Inserting row in database without duplicating it |
 |
|
Hello ! 
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
--
Guillaume, D-14 |
|
| Back to top |
|
 |
probe1
Moderator
Joined: 18 Aug 2004
Posts: 2465
Location: Chonburi Thailand Asia
|
| Posted: Tue Aug 23, 2005 5:25 am Post subject: |
|
|
Try and read the record in question, when notfound: insert, when found: update
_________________
Cheers
Winfried
My Macros
DateTime2 extension: insert date, time or timestamp, formatted to your needs |
|
| Back to top |
|
|
DrewJensen
Super User
Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD
|
| Posted: Tue Aug 23, 2005 6:02 am Post subject: |
|
|
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 |
|
|
Douzout
General User
Joined: 01 Aug 2005
Posts: 19
Location: France (Réunion)
|
| Posted: Tue Aug 23, 2005 9:42 pm Post subject: |
|
|
Thank you very much !
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... 
Thank you very much ! 
If you think this code isn't very academic, tell me
--
Guillaume, who will test the REAL database now  |
|
| Back to top |
|
|
|
