Null values from ADODB recordset

[cskatesv]

I am using disconnected datasets in an appliation, stored with an ADODB recordset into an .MDB database (MS Jet).

For testing, I add a new record, only partially filled out. No problem.

BUT when I retreive the record, and try to load it into the components on my form (text boxes, combo boxes, etc), the fields which I left blank won't load. The error message is "Invalid use of null", which happens regardless of the data type. Even if I try to convert the field into a sting (Cstr()), I get the same message.

How can I prevent this?

[jp140768]

I had a similar problem, and the only way I could find to get around it was as follows:

if isnull(adrTable.Fields("fld")) then
txtBox = ""
else
txtBox = adrTable.Fields("fld")
endif

HTH

[erickwidya]

maybe if u set the default value of the field to '-' so that u always have value '-' if u don't provide them..so that it can't display it on the textbox..
it depends what ur app's needs

regards

[cskatesv]

Thanks:

JP - I was afraid of that!

eric: That sounds interesting...shame we can't set the default value to " ".

[jp140768]

Database design dictates that you need a null for referential integrity / foreign key fields, anything else could have a space.

For example I have a helpdesk system, a user logs a call (we call this an issue), that issue may turn out to be a bug fix (we call this a StIR). My Issues table, has a field called STIR which points to the bug fix record. The rules in my database state that for every value in ISSUES.STIR, there must be a STIR record. When I populate this value with NULL, the database knows that there is no matching record, however if I populate it with something else, the database goes looking for a stir with that key.

Having said that, you said you are using a disconnected recordset, so if you are not planning on updating it, you could set up a loop that replaces null characters with another eg:

dim adrTable as adodb.recordset
dim adrField as adodb.field

'
' Open the recordset
'

for each adrField in adrTable.Fields
   If isnull(adrField.Value) then
      adrField.Value = " "
   EndIf.
Next

HTH

[MichaelNattfalk]

Another quite easy way to prevent errors from occuring is to do the following.

textbox1.text = "" & rs("Field")

Works everytime for me ..

Hope it helps.

Regards,
Michael