how to retrieve blank database fields without raising any error

[chessmonster]

hi,
thanks for reading the thread

is it possible to retrieve blank fields from my database even though the field does not contain anything. thus displaying blank on its destination like a label.

i tried it and it fired error that says "invalid use of null"

are there other ways to do this?

[si_the_geek]

Please don't assume that we know anything about what you are doing - as it stands we don't even know what language you are using, let alone what code you've got. For the kind of info you should always be giving us, read the sticky thread Before You Post: Getting Database questions answered quickly (from the top of this forum).

If you are using VB6 and ADO, there is an article about dealing with that error in our Database Development FAQs/Tutorials (at the top of this forum)

[chessmonster]

thanks for the reply, appreciated it and sorry for my ignorance. next time, i'll be more specific.

[Pradeep1210]

hi,
thanks for reading the thread

is it possible to retrieve blank fields from my database even though the field does not contain anything. thus displaying blank on its destination like a label.

i tried it and it fired error that says "invalid use of null"

are there other ways to do this?

If using SQL Server,
try
ISNULL(FieldName, '') -- for char/varchar fields
or
ISNULL(FieldName, 0) -- for numeric fields

[RunsWithScissors]

Since your profile says you are using vb6, I THINK you can also use the Nz Function (for "Null/Zero") on the client side:

e.g:

Syntax:
Nz(Value to test for null, Value to return IF null)

 '. . . All the code you use to poulate an ADODB.Recordset, then:

If not rs.eof then
    txtLastName.Text = Nz(rs!LastName, "")
    txtFirstName = Nz(rs!FirstName, "")
    txtDOB = Nz(rs!DOB, "")
End If

Be aware there are some other issues when you are going the other way (From the client TO the back-end). In that case you have to make sure that your Text, varchar, or whatever (depending on what database technology you are using) will either allow empty strings (Access defaults to False on this), Date fields are set to allow null values (or not), etc.

How you implement the flow of data and validation depends on how you set uip your client, and how you set up your back-end.

For example, if you are going to retreive Dates from your database, and feed them into a variable, use a Varaiant, unless you plan to handle the results of a null date value being returned to the client.

Hope that helped.

[chessmonster]

ei,

thanks for the reply. i already figured out this problem using a different way. but this way is much more easier to use and does save a lot of code.

thanks again

[chessmonster]

but vb6 won't read the nz() function. it says sub or function not defined. am i suppose to add a reference?

[RunsWithScissors]

Ahhh.

That function must be native to VBA. My mistake. In THAT case, you will need to either go with what Pradeep1210 suggested (which is also a perfectly valid solution) OR test for null the old-fashioned way:

If not rs.eof then
    If Not IsNull(rs!LastName) Then
         txtLastName.Text = rs!LastName
    Else
         txtLastName = ""
    End If
    If Not IsNull(rs!FirstName) Then
         txtFirstName = rs!FirstName
    Else
         txtFirstName  = ""
    End If

 . . . Etc. 

End If

Not NEARLY as convenient as good old Nz(). You might check out the help file in VB6 and search under "Null" and see what comes back-I have been working in .NET for some time now, and prior to that it was primarily VBA (which, admittedly, does a lot of the work for you, but is also very limiting!).

Sorry b'bout that!

[RunsWithScissors]

OR, of course, (to steal an idea I just found by searching the vb6 forum), you could write your OWN Nz Function (NOTE that I have not TESTED these . . .:

'A test for null values that you want an empty string return from"
Public Function NullString(ByVal value As Variant) As Variant
    Dim str As String
    
    str = ""
    If Not IsNull(value) Then
        str = value
    End If
    
    NullString = str

End Function

'A potential (and untested) test for values where you want 
'an empty string return if value is NOT numeric, and zero 
'if the value is numeric (But be careful here . . . Sometimes one
'passes numeric values which are SUPPOSED to be strings . . .)
Public Function NullValue(ByVal value As Variant) As Variant
    Dim Result As Variant
    
    If IsNumeric(value) Then
        Result = 0
    Else
        Result = ""
    End If
    
    If Not IsNull Then
        Result = value
    End If
    
    NullValue = Result
        
End Function

THEN, your code might look like THIS:

If not rs.eof then
    txtLastName.Text = NullString(rs!LastName)
    txtFirstName = NullString(rs!FirstName)

    'THIS will still work, because the return type is VARIANT:
    txtDOB = NullString(rs!DOB, "")
End If

All that said, it will not suprise me to learn that I have fed you bad advice again, and that vb6 is lacking an "IsNumeric" function, or can't use Variants, or some such . . .

Good luck. I would be interested in seeing the method you end up using!

[RunsWithScissors]

Lastly, HERE:

http://www.vbforums.com/search.php?searchid=2234659

Is a lively discussion on handling NULL in vb6, and HERE:

http://www.vbforums.com/search.php?searchid=2234659

Are the results of my search in the vb6 forum on this topic.

GOod Luck!

[chessmonster]

yo!,

thanks for all these advices. they were more than enough, this would really impress my instructor.

thanks man

[RunsWithScissors]

Anytime!

We learn by trying to explain to others . . .