------=_Part_30181_11445657.1197653581434
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
Hi,=C2=A0I'm=C2=A0a=C2=A0newbie=C2=A0to=C2=A0Postgres=C2=A0and=C2=A0ADO/ODB=
C=C2=A0programming.=C2=A0=C2=A0I=C2=A0have=C2=A0a=C2=A0weird=C2=A0problem.=
=C2=A0=C2=A0I=C2=A0have=C2=A0a=C2=A0table=C2=A0called=C2=A0scoredata=C2=A0w=
hich=C2=A0every=C2=A0time=C2=A0I=C2=A0update=C2=A0(from=C2=A0VB6=C2=A0via=
=C2=A0ODBC=C2=A0driver=C2=A008.02.0200=C2=A0Postgres=C2=A0version=C2=A08.2.=
5)=C2=A0a=C2=A0specific=C2=A0smallint=C2=A0field=C2=A0to=C2=A0null,=C2=A0it=
=C2=A0gets=C2=A0filled=C2=A0in=C2=A0with=C2=A0a=C2=A02,=C2=A04,=C2=A0or=C2=
=A06.
Here=C2=A0is=C2=A0the=C2=A0applicable=C2=A0code=C2=A0(I=C2=A0simplified=C2=
=A0it=C2=A0a=C2=A0little=C2=A0to=C2=A0make=C2=A0it=C2=A0shorter):
dim=C2=A0bysCN=C2=A0As=C2=A0New=C2=A0ADODB.Connection
dim=C2=A0scoredataRecordset=C2=A0as=C2=A0ADODB.Recordset
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0bysCN.ConnectionString=C2=A0=3D=C2=A0"DRIVER=
=3D{PostgreSQL=C2=A0
unicode};Server=3Dservername;****t=3D5432;Database=3Dmslocal;Uid=3Ddbadmin;P=
wd=3Dpassword;"
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0bysCN.Mode=C2=A0=3D=C2=A0adModeReadWrite
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0bysCN.Open
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0Set=C2=A0scoredataRecordset=C2=A0=3D=C2=A0New=
=C2=A0ADODB.Recordset
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0scoredataRecordset.CursorLocation=C2=A0=3D=C2=
=A0adUseClient
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0scoredataRecordset.Open=C2=A0"select=C2=A0*=
=C2=A0from=C2=A0scoredata=C2=A0where=C2=A0"+Chr(34)+"class=C2=A0id"+Chr(34)=
+"=C2=A0=3D=C2=A06271",=C2=A0bysCN,=C2=A0
adOpenKeyset,=C2=A0adLockOptimistic
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0...
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0scoredataRecordset.Fields("average")=3DNull
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0scoredataRecordset.Update
At=C2=A0this=C2=A0point=C2=A0if=C2=A0I=C2=A0query=C2=A0the=C2=A0database=C2=
=A0it=C2=A0the=C2=A0"average"=C2=A0field=C2=A0is=C2=A0filled=C2=A0in=C2=A0w=
ith=C2=A0a=C2=A02,4,=C2=A0or=C2=A06.=C2=A0=C2=A0This=C2=A0problem=C2=A0only=
=C2=A0seems=C2=A0to=C2=A0happen=C2=A0if=C2=A0the=C2=A0field=C2=A0was=C2=A0n=
ot=C2=A0null=C2=A0before=C2=A0but=C2=A0then=C2=A0was=C2=A0changed=C2=A0to=
=C2=A0null.
This=C2=A0is=C2=A0my=C2=A0table:
=C2=A0class=C2=A0id=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0|=C2=A0bigint=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0|=C2=A0not=C2=A0null
=C2=A0stud=C2=A0id=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0|=C2=A0integer=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
|=C2=A0not=C2=A0null
=C2=A0average=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0|=C2=A0smallint=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0|
=C2=A0othergrade=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0|=C2=A0char=
acter=C2=A0varying(5)=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0|
=C2=A0override=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0|=
=C2=A0character=C2=A0varying(5)=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0|
=C2=A0de****tment=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0|=C2=A0char=
acter=C2=A0varying(5)=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0|
=C2=A0numericcomments=C2=A0=C2=A0=C2=A0|=C2=A0character=C2=A0varying(25)=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0|
=C2=A0datecreated=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0|=C2=A0timestamp=
=C2=A0without=C2=A0time=C2=A0zone=C2=A0|=C2=A0default=C2=A0now()
=C2=A0usercreated=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0|=C2=A0character=
=C2=A0varying(5)=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0|
=C2=A0userupdated=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0|=C2=A0character=
=C2=A0varying(5)=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0|
=C2=A0othergrade_reason=C2=A0|=C2=A0character=C2=A0varying(2048)=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0|
=C2=A0de****tment_reason=C2=A0|=C2=A0character=C2=A0varying(2048)=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0|
=C2=A0id=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0|=C2=A0integer=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0|=C2=A0not=C2=A0null=C2=A0default=C2=A0nextval('scored=
ata_id_seq'::regclass)
=C2=A0thelevel=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0|=
=C2=A0smallint=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0|=C2=A0default=
=C2=A00
=C2=A0comment=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0|=C2=A0character=C2=A0varying(2048)=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0|
Indexes:
=C2=A0=C2=A0=C2=A0=C2=A0"scoredata_pkey"=C2=A0PRIMARY=C2=A0KEY,=C2=A0btree=
=C2=A0(id)
=C2=A0=C2=A0=C2=A0=C2=A0"scoredata_stud_id_class_id"=C2=A0UNIQUE,=C2=A0btre=
e=C2=A0("class=C2=A0id",=C2=A0"stud=C2=A0id")
=C2=A0=C2=A0=C2=A0=C2=A0"scoredata_class_id"=C2=A0btree=C2=A0("class=C2=A0i=
d")
=C2=A0=C2=A0=C2=A0=C2=A0"scoredata_stud_id"=C2=A0btree=C2=A0("stud=C2=A0id"=
)
Foreign-key=C2=A0constraints:
=C2=A0=C2=A0=C2=A0=C2=A0"scoredata_class=C2=A0id_fkey"=C2=A0FOREIGN=C2=A0KE=
Y=C2=A0("class=C2=A0id")=C2=A0REFERENCES=C2=A0cl*****("class=C2=A0id")
=C2=A0=C2=A0=C2=A0=C2=A0"scoredata_de****tment_fkey"=C2=A0FOREIGN=C2=A0KEY=
=C2=A0(de****tment)=C2=A0REFERENCES=C2=A0de****tment(de****tment)
=C2=A0=C2=A0=C2=A0=C2=A0"scoredata_othergrade_fkey"=C2=A0FOREIGN=C2=A0KEY=
=C2=A0(othergrade)=C2=A0REFERENCES=C2=A0other_grades(other_grades)
Thank=C2=A0you=C2=A0so=C2=A0much=C2=A0for=C2=A0your=C2=A0help.=C2=A0=C2=A0I=
=C2=A0apologize=C2=A0if=C2=A0I=C2=A0posted=C2=A0or=C2=A0formatted=C2=A0this=
=C2=A0incorrectly.
L.=C2=A0Shafranovich
------=_Part_30181_11445657.1197653581434
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
<html><head></head><body>Hi, I'm a newbie to Postg=
res and ADO/ODBC programming. I have a&=
nbsp;weird problem. I have a table call=
ed scoredata which every time I update (=
from VB6 via ODBC driver 08.02.0200 Postgres&=
nbsp;version 8.2.5) a specific smallint field =
;to null, it gets filled in with a =
2, 4, or 6.<br>Here is the applicable co=
de (I simplified it a little to make&nbs=
p;it shorter):<br><br><div style=3D"margin-left:
40px;">dim bysCN=
As New ADODB.Connection<br>dim scoredataRecordset =
;as ADODB.Recordset<br><br> bysCN.Connect=
ionString =3D "DRIVER=3D{PostgreSQL <br>unicode};Server=3Dse=
rvername;****t=3D5432;Database=3Dmslocal;Uid=3Ddbadmin;Pwd=3Dpassword;"<br>&=
nbsp; bysCN.Mode =3D adModeReadWrite<br>&n=
bsp; bysCN.Open<br><br><br> &=
nbsp;Set scoredataRecordset =3D New ADODB.Recordset<br>=
scoredataRecordset.CursorLocation =3D&nb=
sp;adUseClient<br> scoredataRecordset.Open&nbs=
p;"select * from scoredata where "+Chr(34)+"class&=
nbsp;id"+Chr(34)+" =3D 6271", bysCN, <br>adOpenKeyset,&=
nbsp;adLockOptimistic<br> ...<br> &=
nbsp; scoredataRecordset.Fields("average")=3DNull<br>  =
; scoredataRecordset.Update<br></div><br>At this =
;point if I query the database it the&nb=
sp;"average" field is filled in with a 2=
,4, or 6. This problem only seems =
to happen if the field was not null =
;before but then was changed to null.<br><br>=
This is my table:<br><div style=3D"margin-left:
40px;"> =
;class id |=
bigint &nb=
sp; | =
;not null<br> stud id &nb=
sp; | integer &nbs=
p; &=
nbsp; | not null<br> average &nb=
sp; | smallint &nb=
sp; =
|<br> othergrade =
| character varying(5) &=
nbsp; |<br> override &nb=
sp; | character varying(5) &nbs=
p; |<br> de****tment &nbs=
p; | character varying(5)  =
; |<br> numericcomments =
| character varying(25) =
|<br> datecreated |&nbs=
p;timestamp without time zone | default now()=
<br> usercreated | chara=
cter varying(5) |<br>&n=
bsp;userupdated | character&n=
bsp;varying(5) |<br> ot=
hergrade_reason | character varying(2048) &=
nbsp; |<br> de****tment_reason | character varying(=
2048) |<br> id &nb=
sp; | =
;integer &=
nbsp; | not =
null default nextval('scoredata_id_seq'::regclass)<br> thele=
vel | small=
int =
| default 0<br>&n=
bsp;comment &nbs=
p;| character varying(2048) |<br>Ind=
exes:<br> "scoredata_pkey" PRIMARY KEY,&nb=
sp;btree (id)<br> "scoredata_stud_id_class_id"&=
nbsp;UNIQUE, btree ("class id", "stud id")<br>&nbs=
p; "scoredata_class_id" btree ("class id")<=
br> "scoredata_stud_id" btree ("stud =
id")<br>Foreign-key constraints:<br> "scoredata=
_class id_fkey" FOREIGN KEY ("class id") REFE=
RENCES cl*****("class id")<br> "scoredata_=
de****tment_fkey" FOREIGN KEY (de****tment) REFERENCES&nb=
sp;de****tment(de****tment)<br> "scoredata_othergrade_=
fkey" FOREIGN KEY (othergrade) REFERENCES other_gr=
ades(other_grades)<br></div><br>Thank you so much for&n=
bsp;your help. I apologize if I posted&=
nbsp;or formatted this incorrectly.<br><br>L. Shafranov=
ich<br><br></body></html>
------=_Part_30181_11445657.1197653581434--
|
