Invalid Use Of Null

[visualsander]

Hi group,

(Pardon my English, I'm from holland)
When I am changing records (next, previous, first, last record) and a field is empty I get an error: Invalid Use of Null.

I filled the database with records (10,000+) from a different application and a lot of records have empty fields.

How do I 'handle' the error?

PS I'm a VB beginner and defenitly a ErrorHandler Beginner!

Sander

[Rob Brown]

Hi,

the problem is probably within your database.

If it is in access then select the field that is creating the problem in design view and change the 'Allow Zero-Length' property to yes.

Alternatively if you want to handle the error from VB then you could try something like this:

Get the run time error number from the message box produced when the error occurs.

At the start of your procedure slip in a line of code ie:

On Error GoTo Handler

Then at the bottom of your procedure type:

Exit Sub

Handler:
If Err.Number = (error number) then
Move Next
End If

Hope this helps.

Best regards,

Rob Brown.

[honeybee]

The problem is because your records contain null values, they cannot directly be assigned to textboxes and other controls. I guess you are using a data control and bound controls.

In that case I don't know the solution to your problem, except that you can make all the fields in your database compulsory, i.e. Required = Yes and AllowZeroLength = No, or else you can provide default values to those fields which you think will remain null.

In code I have got this trick to work around the nulls:


Text1.Text = rs.Fields("FirstName") & ""


This ensures that the null values in the database don't crop up in your forms.

[davidrobin]

There is another option to handling fields with NULL values, if you are using code to retrieve records, i.e. as in a select statement then you can use the ISNULL SQL function like so

select ISNULL(Fred,'') as Fred FROM Customers

here Fred is the name of the field, you must include the "as Fred" or else the column of data will not have an identity and you will get unknown data error (or something like that). This effectively replaces a null field with and empty string so you do not have to think any more about the data in the field after that point. The problem is checked and resolved from the start.

If the data type of the field is numeric then ISNULL(Fred,10) would work just as well, you can specify what you want for the default value as long as it is the correct data type.
When assigning to a control like a text box you don't have to keep putting the & "" at the end every time.

Text1.text=rs.Fields("FirstName")

BTW, there is also a similar vb function