Thread: Problem with VB6, DAO, Data Control: Update does not work
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.fuertbauer@eunet.at
Wolfgang Fürtbauer wrote: > 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 ? You can't reasonably compare floating point values for equality, because they only store approximations of what you see. And you certainly shouldn't store monetary values in floating point columns, because you are asking for rounding problems. Always use numeric. -- Peter Eisentraut http://developer.postgresql.org/~petere/
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.fuertbauer@eunet.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
informationthat is privileged, confidential and exempt from disclosure under applicable law. If you are not the
intendedaddressee, 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
thismessage in error, please immediately advise the sender by reply email, and delete the message. Thank you.
Re: Problem with VB6, DAO, Data Control: Update does not work
From
Wolfgang.Fuertbauer@ebewe.com
Date:
> Wolfgang Fürtbauer wrote: > > 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 ? > You can't reasonably compare floating point values for equality, because > they only store approximations of what you see. And you certainly > shouldn't store monetary values in floating point columns, because you > are asking for rounding problems. Always use numeric. OK; I changed to numeric(8,4) for this field; what happens is: VB does'nt regocnize decimals anymore: from pgsql: update auftrag set rabproz = 5.5; from VB in debug mode: ? data1.recordset!rabproz 5.5 ? data.recordset!rabproz > 5 True ? data.recordset!rabproz < 6 False ? data.recordset!rabproz > 50 True ? data.recordset!rabproz < 60 True and bound controls display the value without decimals ? What am I doing wrong ? Wolfgang > -- > Peter Eisentraut > http://developer.postgresql.org/~petere/ ---------------------------------------------------------------------- Wolfgang Fuertbauer (E-Mail: Wolfgang.Fuertbauer@ebewe.com) EBEWE Pharma Tel: ++43 7665 8123 315 Mondseestrasse 11 Fax: ++43 7665 8123 11 4866 Unterach, Austria http://www.ebewe.com