Re: Problem with VB6, DAO, Data Control: Update does not work

[Andrew Ayers <aayers(at)eldocomp(dot)com>]

Wolfgang Fürtbauer wrote:

If you have a choice (that is, this is a new application you are 
developing or something) - go with ADO instead of DAO. About a year or 
so back I did a conversion of an application from Access 97 to PG, and 
most of it was in DAO. I had a lot of problems, switched to ADO and 
things cleared up nicely (look in the archives for GENERAL and ODBC 
lists to see my experiences).

Now, some of this may have been related to a combo of older ODBC driver 
issues and older PG installs, which may be different today - but I don't 
regret having to switch the application over...

Andrew

> Dear list,
> 
> I have the following problem with VB6 and the Data control:
> 
> SQL Update statement generated by "data1.recordset.update" does
> not work (0 records updated); here is an example:
> 
> -- from /var/log/postgresql --
> update Auftrag SET kdland='D', kdplz='54497', kdort='MORBACH' , 
> kdstrasse='Bahnhofstrasse 8' ,
> kdwhrg='EUR' , kurs='1'::float4 , sktotage=10 , sktoproz='4'::float4 , 
> sktotage2=0 ,
> sktoproz2='0'::float4 , zieltage=30 , rabproz='5'::float4 , valutatage=2 
> , zako=4
> WHERE "datum" = '2002-03-25'::date AND "lieferdatum" = '2002-03-25'::date
> AND "kdname" = 'MODE - TREFF' AND "kdbranche" = ' ' AND "kdland" = 'D'
> AND "kdplz" = '54497' AND "kdort" = 'MORBACH' AND "kdstrasse" = 
> 'Bahnhofstrasse 8'
> AND "kdwhrg" = 'EUR' AND "kurs" = '1'::float4 AND "sktotage" = 10
> AND "sktoproz" = '4'::float4 AND "sktotage2" = 0 AND "sktoproz2" = 
> '0'::float4
> AND "netto" = '122.7'::float4 AND "zieltage" = 30 AND "vkart" = 2 AND 
> "zaart" = 1
> AND "druckkz" = 'D' AND "vertreter" = 19 AND "vtprovision" = 19 AND 
> "rabproz" = '7'::float4
> AND "rabwert" = '5.225'::float4 AND "provision" = 100 AND "valutatage" = 0
> AND "zako" = 4 AND "nachlassprozent" = '0'::float4 AND "nachlasswert" = 
> '0'::float4
> AND "dringlichkeit" = 0 AND "nr" = 39277 AND "eadresse" = 0 AND "kdnr" = 
> 99624
> AND "notizen" = 'Brief VE dazu'
> -- end from /var/log/postgresql --
> 
> I found out, after removing the "rabwert" = '5.225'::float4 from the 
> where-clause,
> the update worked; so I had a look with the VB-Debugger at the value of 
> data1.recordset!rabwert
> just before update and: the value was 5.225
> 
> Why does the update not work ?
> 
> Environment:
> ------------
> 
> psql (PostgreSQL) 7.4.2
> ODBC-Driver 7.03.02.00
> VB 6 SP 5
> 
> Data control created with:
>     With data1
>         .Connect = myConn.Connect
>         .DatabaseName = ""
>         .DefaultType = dbUseODBC
>         .DefaultCursorType = dbUseODBCCursor
>         .Options = dbExecDirect
>         .Readonly = false
>         .RecordSource = "Select * from auftrag where nr = 39277"
>         .Exclusive = False
>         .Refresh
>          .edit
>     End With
> Connection:
> Set myConn = myWs.OpenConnection("faktconn",
>                                    dbDriverNoPrompt,
>                                    False,
>                                    
> "ODBC;dsn=PostgreSQL;uid=postgres;pwd=;")
> 
> 
> ---------------------------------------------------------------------
> Wolfgang Fürtbauer                              Tel:   +43 7612 77620
> Steinbichlstrasse 58d                           Mobil: +43 650 7762001
> A-4812 Pinsdorf                         w(dot)fuertbauer(at)eunet(dot)at      
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>      joining column's datatypes do not match
> 
> 
> 


-- CONFIDENTIALITY NOTICE --

This message is intended for the sole use of the individual and entity to whom it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the intended addressee, nor authorized to receive for the intended addressee, you are hereby notified that you may not use, copy, disclose or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email, and delete the message. Thank you.