Null values from ADODB recordset
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?
Re: Null values from ADODB recordset
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
Re: Null values from ADODB recordset
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
Re: Null values from ADODB recordset
Thanks:
JP - I was afraid of that!
eric: That sounds interesting...shame we can't set the default value to " ".
Re: Null values from ADODB recordset
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:
Code:
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
Re: Null values from ADODB recordset
Another quite easy way to prevent errors from occuring is to do the following.
Code:
textbox1.text = "" & rs("Field")
Works everytime for me ..
Hope it helps.
Regards,
Michael