Hi, I'm a newbie to Postgres and ADO/ODBC programming. I have a weird problem. I have a table called scoredata which every time I update (from VB6 via ODBC driver 08.02.0200 Postgres version 8.2.5) a specific smallint field to null, it gets filled in with a 2, 4, or 6.
Here is the applicable code (I simplified it a little to make it shorter):
dim bysCN As New ADODB.Connection
dim scoredataRecordset as ADODB.Recordset
bysCN.ConnectionString = "DRIVER={PostgreSQL
unicode};Server=servername;Port=5432;Database=mslocal;Uid=dbadmin;Pwd=password;"
bysCN.Mode = adModeReadWrite
bysCN.Open
Set scoredataRecordset = New ADODB.Recordset
scoredataRecordset.CursorLocation = adUseClient
scoredataRecordset.Open "select * from scoredata where "+Chr(34)+"class id"+Chr(34)+" = 6271", bysCN,
adOpenKeyset, adLockOptimistic
...
scoredataRecordset.Fields("average")=Null
scoredataRecordset.Update
At this point if I query the database it the "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 | not null
stud id | integer | not null
average | smallint |
othergrade | character varying(5) |
override | character varying(5) |
deportment | character varying(5) |
numericcomments | character varying(25) |
datecreated | timestamp without time zone | default now()
usercreated | character varying(5) |
userupdated | character varying(5) |
othergrade_reason | character varying(2048) |
deportment_reason | character varying(2048) |
id | integer | not null default nextval('scoredata_id_seq'::regclass)
thelevel | smallint | default 0
comment | character varying(2048) |
Indexes:
"scoredata_pkey" PRIMARY KEY, btree (id)
"scoredata_stud_id_class_id" UNIQUE, btree ("class id", "stud id")
"scoredata_class_id" btree ("class id")
"scoredata_stud_id" btree ("stud id")
Foreign-key constraints:
"scoredata_class id_fkey" FOREIGN KEY ("class id") REFERENCES classes("class id")
"scoredata_deportment_fkey" FOREIGN KEY (deportment) REFERENCES deportment(deportment)
"scoredata_othergrade_fkey" FOREIGN KEY (othergrade) REFERENCES other_grades(other_grades)
Thank you so much for your help. I apologize if I posted or formatted this incorrectly.
L. Shafranovich