Updating views : cannot figure out what goes wrong - Mailing list pgsql-novice

Hi there,

First of all i'm using the postgresql 8.0rc2 on windows

I have a problem updating views and seem to be stuck.
My front-end to postgresql is ms-access and i have the following code in
my VBA

strSql = "CREATE VIEW bondetailtmp AS SELECT * FROM bondetail WHERE
subbonnr=" & Forms!bonnentest!bonnr & ";"
cn.Execute strSql
strSql = "CREATE RULE bondetailtmpupd AS ON UPDATE TO bondetailtmp " & _
        "DO INSTEAD NOTHING;"
cn.Execute strSql
strSql = "CREATE RULE bondetailtmpupd2 AS ON UPDATE TO bondetailtmp " & _
        "WHERE OLD.subbonbeschrijving <> NEW.subbonbeschrijving " & _
        "DO (UPDATE bondetail " & _
        "SET subbonbeschrijving=NEW.subbonbeschrijving);"
cn.Execute strSql
Forms!bonnentest.bondetailtmp.Form.RecordSource = "bondetailtmp"

So the recordsource for the form is set through a link I made to the
bondetailtmp view.
Now when I try to edit the date in the form and move to another record
then I get :
476 2005-01-06 18:46:34ERROR:  cannot update a view

476 2005-01-06 18:46:34HINT:  You need an unconditional ON UPDATE DO
INSTEAD rule.

476 2005-01-06 18:46:34STATEMENT:  BEGIN;UPDATE "public"."bondetailtmp"
SET "subbonbeschrijving"='Test 1000a'  WHERE "id" = 2417 AND "subbonnr"
= '4'::float4 AND "subbonlijnnr" = '410'::float4 AND "subbongroep" = ' '
AND "subbonartikelnr1" = ' 15' AND "subbonartikelnr2" = ' ' AND
"subboneenheid1" = 'st' AND "subboneenheid2" = ' ' AND "subbonaantal" =
'10'::float4 AND "subbonaantaltot" = '10'::float4 AND "subbonaantal2" =
'1'::float4 AND "subbonaantal3" = '1'::float4 AND "kleurcode" IS NULL
AND "subbonprijsbf" = '110'::float4 AND "subbonprijseuro" = '0'::float4
AND "subbontotaalbf" = '990'::float4 AND "subbontotaaleuro" =
'0'::float4 AND "berekend" IS NULL AND "berekeningen" IS NULL AND
"munt1" = 'Euro' AND "munt2" IS NULL AND "subtotaaldetail" = 410 AND
"subtotaal" = 0 AND "subtot" = 1 AND "btw" = '21'::float4 AND "tekening"
= ' ' AND "stock" = ' ' AND "produktnr" = '0'::float4 AND "produktid" =
0 AND "voorraadid" = '0'::float4 AND "rendementuur" = '0'::float4 AND
"totaaluren" = '0'::float4 AND "uur" = '0'::float4 AND "mat" =
'0'::float4 AND "matuur" = '0'::float4 AND "subbonprijsbfaankoopuur" =
'0'::float4 AND "subbonprijsbfaankoopmat" = '100'::float4 AND
"subbonprijsbfaankoopmet" = '0'::float4 AND "subbonprijsbfaankoopond" =
'0'::float4 AND "subbonprijsbfaankoopdir" = '0'::float4 AND
"subbonprijsbfaankoopbouw" = '0'::float4 AND "subbonprijsbfaankoopb1" =
'0'::float4 AND "subbonprijsbfaankoopb2" = '0'::float4 AND
"subbonprijsbfaankoopb3" = '0'::float4 AND "margeuur" = '10'::float4 AND
"margemat" = '10'::float4 AND "margemet" = '0'::float4 AND "margeond" =
'0'::float4 AND "margedir" = '0'::float4 AND "margebouw" = '0'::float4
AND "margeb1" = '0'::float4 AND "margeb2" = '0'::float4 AND "margeb3" =
'0'::float4 AND "aankoopprijs" = '1' AND "verkoopprijs" = '0' AND
"subbontotaalbfaankoop" = '1000'::float4 AND "korting" = '10'::float4
AND "datum1" IS NULL AND "aantal1" = '0'::float4 AND "datum2" IS NULL
AND "aantal2" = '0'::float4 AND "checkaantal2" = '0' AND "datum3" IS
NULL AND "aantal3" = '0'::float4 AND "checkaantal3" = '0' AND "datum4"
IS NULL AND "aantal4" = '0'::float4 AND "totaalgeleverd" = '0'::float4
AND "nogteleveren" = '0'::float4 AND "aantalgefaktureerd" = '0'::float4
AND "referentiegefaktureerd" IS NULL AND "onderaannemer" IS NULL AND
"onderaannemerh" IS NULL AND "vinkje" = '0' AND "toegewezen" = ' ' AND
"afdrukr" = '0' AND "barcode" IS NULL AND "begindatum" IS NULL AND
"einddatum" IS NULL AND "kleurprijs1" = '0'::float4 AND "kleurprijs2" =
'0'::float4 AND "kleurprijs3" = '0'::float4 AND "kleurprijs4" =
'0'::float4 AND "kleurprijs5" = '0'::float4 AND "kleurprijs6" =
'0'::float4 AND "kleurprijs7" = '0'::float4 AND "kleurprijs8" =
'0'::float4 AND "kleurprijs9" = '0'::float4 AND "kleurprijs10" =
'0'::float4 AND "stockprodukt" = '0' AND "lengte" = '1'::float4 AND
"breedte" = '1'::float4 AND "hoogte" = '1'::float4 AND "lettersb" IS
NULL AND "lettersf" IS NULL AND "zuiverekost" = '1000'::float4 AND
"afdrukuurtabel" = '1' AND "werfref" = '2410' AND "productieref" = ' '
AND "uwref" = ' ' AND "aanbesteding01" = '0'::float4 AND
"aanbesteding02" = '0'::float4 AND "aanbesteding03" = '0'::float4 AND
"aanbesteding04" = '0'::float4 AND "aanbesteding05" = '0'::float4 AND
"aanbestedingmarge" = '0'::float4 AND "aanbestedingvp" = '0'::float4 AND
"aanbestedingep" = '0'::float4 AND "aanbestedingvpa" = '0'::float4 AND
"aanbestedingepa" = '0'::float4 AND "aanbestedingmargea" = '0'::float4
AND "onderaannemeraanbesteding" IS NULL AND "bevatdetailstaat" = '0' AND
"groepselectie" = '0' AND "doorrekenenbk" = '1' AND "doorrekenenu" = '1'
AND "zuiverkostminuren" = '1000'::float4 AND "merk" IS NULL AND
"leverancier" = 0 AND "transferred" = '0' AND "nietmeerekenen" = '0

I did change the field 'subbonbeschrijving' from Test1000 into
Test1000a, so the sql query access sends seems to be ok.
What am I doing wrong. I looked in the manuel but I can't seem to find
what goes wrong.

Thanks

Dirk Cleenwerck


--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.6.9 - Release Date: 06-01-2005