General discussion

Locked

Invalid Use Of Null

By mpalazzo ·
I'm sure everybody's run into this problem at least once, and I'm equally sure it has an easy solution.

I'm trying to update an ADO recordset in VB, and if I try to update a field whose value is null, I get "Invalid Use of Null". So to fix this,I go through each line of my code and add the condition "If not isnull(rs.fields("fieldname").value) then [update the field].

Is this really necessary? I can't imagine that vb programmers world wide have included this seemingly useless conditionbefore each field they update.

Many thanks!

This conversation is currently closed to new comments.

10 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Invalid Use Of Null

by Peyison In reply to Invalid Use Of Null

Not sure I understand. You get the error when you try to update a field and the value of the field being updated is null? This really shouldn't be happening. When you update a field, it doesn't matter what the current value of the field is.

So -is rs.fields("fieldname") the field you are updating, or the field you're getting the value from to update another field?

If it is the field you are getting the value from, the try:

rs.fields("fieldname").value & ""

Adding the & "" at the end will cast any null values to an empty string.

Hope this helps.

Collapse -

Invalid Use Of Null

by mpalazzo In reply to Invalid Use Of Null

Poster rated this answer

Collapse -

Invalid Use Of Null

by Lo In reply to Invalid Use Of Null

Might check your DB field definitions. Having a NULL value may be set in the db to prevent records from being added when required fields have not been filled in.

Using a Null String (""), may get past NULL (I think NULL for a string field is defined as never having been on the left on an equal sign, <field> = <value>).

Setting it to null may not be desirable, if the db was defined to disallow null entries, probably a reason, should check before overriding. May not be an issue.

Collapse -

Invalid Use Of Null

by mpalazzo In reply to Invalid Use Of Null

Poster rated this answer

Collapse -

Invalid Use Of Null

by Seagull Data Services In reply to Invalid Use Of Null

Sounds like your problem is in the table definition.
If the field in question is a required field then nulls are not allowed. Your solution of not updating the individual fields won't solve the problem because once a required field is not there therecord won't save.
You should disable your attempt to save until all required fields are present.

Collapse -

Invalid Use Of Null

by Seagull Data Services In reply to Invalid Use Of Null

It's also worth considering that a required field is usually required for a reason. If it's not a required field then you should go into the table design and remove the requirement. Inputting blanks is generally bad practice.

Collapse -

Invalid Use Of Null

by mpalazzo In reply to Invalid Use Of Null

Poster rated this answer

Collapse -

Invalid Use Of Null

by Limbo In reply to Invalid Use Of Null

You're right, everyone will have seen it at some time or another. No doubt you will have various solutions already, but please consider this good practice:


Public Function SQLText(FieldValue as Variant) as Variant

If IsNumeric(FieldValue) Then
SQLText = FieldValue
Else
If IsNull(FieldValue) And DBAcceptsNulls
SQLText = FieldValue
Else
SQLText = "'" & FieldValue & "'"
End If
End Function


Now the DBAcceptsNulls is an optional boolean function which can check the ADO ResultSet's field properties for Allow Nulls. I'm afraid I can't provide a generic example as this depends entirely on you DB Access architecture.

Most people fail to observe that when a result set is populated, all associated meta data (table name, field size, data type etc.) is also returned via ADO as an Attribute Property of the result set.

Therefore, when you pass your values it would in this fashion:

DBUpdate("fieldname") = SQLText(rs.fields("fieldname").value)


There are numerous other methods which also work such as bound data types, masked controls and control validation but I prefer the above method for extensibility.

Collapse -

Invalid Use Of Null

by mpalazzo In reply to Invalid Use Of Null

Poster rated this answer

Collapse -

Invalid Use Of Null

by mpalazzo In reply to Invalid Use Of Null

This question was closed by the author

Back to Web Development Forum
10 total posts (Page 1 of 1)  

Related Discussions

Related Forums