weird vb6 odbc null problem

From: lshafranovich(at)baisyaakov(dot)net
To: <pgsql-odbc(at)postgresql(dot)org>
Subject: weird vb6 odbc null problem
Date: 2007-12-14 17:33:01
Message-ID: 18411256.511331197653581482.JavaMail.servlet@perfora
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-odbc

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

Responses

Browse pgsql-odbc by date

  From Date Subject
Next Message Sofer, Yuval 2007-12-16 11:53:54 Re: odbc problem on Japanese windows machine
Previous Message Kenny Cheng 2007-12-14 09:07:05 IIS Only Opens One Database Connection At A Time