weird vb6 odbc null problem
am 14.12.2007 18:33:01 von lshafranovich------=_Part_30181_11445657.1197653581434
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
Hi, I'm a newbie to Postgres a nd ADO/ODB=
C programming.  I have a weird  problem.=
  I have a table called s coredata w=
hich every time I update (from=C2=A 0VB6 via=
 ODBC driver 08.02.0200 Postgres ve rsion 8.2.=
5) a specific smallint field to=C2= A0null, it=
 gets filled in with a 2,=C2=A 04, or=C2=
=A06.
Here is the applicable code (I=C2=A 0simplified=C2=
=A0it a little to make it shor ter):
dim bysCN As New ADODB.Connection
dim scoredataRecordset as ADODB.Recordset
     bysCN.ConnectionString =3 D "DRIVER=
=3D{PostgreSQLÂ
unicode};Server=3Dservername;Port=3D5432;Database=3Dmslocal; Uid=3Ddbadmin;P=
wd=3Dpassword;"
     bysCN.Mode =Â=A0adMod eReadWrite
     bysCN.Open
     Set scoredataRecordset=C2 = New=
 ADODB.Recordset
     scoredataRecordset.CursorLocat ion =Â=
=A0adUseClient
     scoredataRecordset.Open " select *=
 from scoredata where "+Chr(34)+"class=C 2=A0id"+Chr(34)=
+" =Â=A06271", bysCN,Â
adOpenKeyset, adLockOptimistic
     ...
     scoredataRecordset.Fields("ave rage")=3DNull
     scoredataRecordset.Update
At this point if I query the=C 2=A0database=C2=
=A0it the "average" field is filled  in w=
ith a 2,4, or 6.  This pr oblem only=
 seems to happen if the field= C2=A0was n=
ot null before but then was ch anged to=
 null.
This is my table:
 class id      =C2=A 0  =
 | bigint      =C2=A 0  =
            =C2=
=A0| not null
 stud id         =C2=
Â=A0| integer      = C2  Â=
           Â=A0=
| not null
 average       =C2=A 0  =
 | smallint      =C2  Â=A0=
           |
 othergrade       =C 2=A0| char=
acter varying(5)      =C2 Â=A0|
 override       =C2= A0  |=
 character varying(5)    =C2=A 0  =
 |
 deportment       =C 2=A0| char=
acter varying(5)      =C2 Â=A0|
 numericcomments   | character=C2=A 0varying(25)=C2=
     Â=A0|
 datecreated       |  timestamp=
 without time zone | default n ow()
 usercreated       |  character=
 varying(5)       =C 2=A0|
 userupdated       |  character=
 varying(5)       =C 2=A0|
 othergrade_reason | character varying(2 048) =C2=
  Â=A0|
 deportment_reason | character varying(2 048) =C2=
  Â=A0|
 id        =C2= A0  =
     | integer  =C2= A0  =
            =C2=
  Â=A0| not null default nextval('scored=
ata_id_seq'::regclass)
 thelevel       =C2= A0  |=
 smallint       =C2= A0  =
          | default=
 0
 comment       =C2=A 0  =
 | character varying(2048)   = C2 Â=A0|
Indexes:
    "scoredata_pkey" PRIMARY K EY, btree=
 (id)
    "scoredata_stud_id_class_id" UN IQUE, btre=
e ("class id", "stud id")
    "scoredata_class_id" btree=C2=A 0("class i=
d")
    "scoredata_stud_id" btree ("stud id"=
)
Foreign-key constraints:
    "scoredata_class id_fkey" FOREIGN KE=
Y ("class id") REFERENCES classes("class  id")
    "scoredata_deportment_fkey" FOR EIGN KEY=
 (deportment) REFERENCES deportment(deportmen t)
    "scoredata_othergrade_fkey" FOR EIGN KEY=
 (othergrade) REFERENCES other_grades(other_g rades)
Thank you so much for your hel p.  I=
 apologize if I posted or form atted this=
 incorrectly.
L. Shafranovich
------=_Part_30181_11445657.1197653581434
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
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.
Here is the applicable co=
de (I simplified it a little to make&nbs=
p;it shorter):
As New ADODB.Connection
dim scoredataRecordset =
;as ADODB.Recordset
bysCN.Connect=
ionString =3D "DRIVER=3D{PostgreSQL
unicode};Server=3Dse=
rvername;Port=3D5432;Database=3Dmslocal;Uid=3Ddbadmin;Pwd=3D password;"
&=
nbsp; bysCN.Mode =3D adModeReadWrite
&n=
bsp; bysCN.Open
&=
nbsp;Set scoredataRecordset =3D New ADODB.Recordset
=
scoredataRecordset.CursorLocation =3D&nb=
sp;adUseClient
scoredataRecordset.Open&nbs=
p;"select * from scoredata where "+Chr(34)+"class&=
nbsp;id"+Chr(34)+" =3D 6271", bysCN,
adOpenKeyset,&=
nbsp;adLockOptimistic
...
&=
nbsp; scoredataRecordset.Fields("average")=3DNull
=
; scoredataRecordset.Update
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.
=
This is my table:
;class id |=
bigint &nb=
sp; | =
;not null
stud id &nb=
sp; | integer &nbs=
p; &=
nbsp; | not null
average &nb=
sp; | smallint &nb=
sp; =
|
othergrade =
| character varying(5) &=
nbsp; |
override &nb=
sp; | character varying(5) &nbs=
p; |
deportment &nbs=
p; | character varying(5) =
; |
numericcomments =
| character varying(25) =
|
datecreated |&nbs=
p;timestamp without time zone | default now()=
usercreated | chara=
cter varying(5) |
&n=
bsp;userupdated | character&n=
bsp;varying(5) |
ot=
hergrade_reason | character varying(2048) &=
nbsp; |
deportment_reason | character varying(=
2048) |
id &nb=
sp; | =
;integer &=
nbsp; | not =
null default nextval('scoredata_id_seq'::regclass)
thele=
vel | small=
int =
| default 0
&n=
bsp;comment &nbs=
p;| character varying(2048) |
Ind=
exes:
"scoredata_pkey" PRIMARY KEY,&nb=
sp;btree (id)
"scoredata_stud_id_class_id"&=
nbsp;UNIQUE, btree ("class id", "stud id")
&nbs=
p; "scoredata_class_id" btree ("class id")<=
br> "scoredata_stud_id" btree ("stud =
id")
Foreign-key constraints:
"scoredata=
_class id_fkey" FOREIGN KEY ("class id") REFE=
RENCES classes("class id")
"scoredata_=
deportment_fkey" FOREIGN KEY (deportment) REFERENCES&nb=
sp;deportment(deportment)
"scoredata_othergrade_=
fkey" FOREIGN KEY (othergrade) REFERENCES other_gr=
ades(other_grades)
Thank you so much for&n=
bsp;your help. I apologize if I posted&=
nbsp;or formatted this incorrectly.
L. Shafranov=
ich
------=_Part_30181_11445657.1197653581434--