The mystery of the empty fields...

[Shilohcity]

Hi there

I have a SQL database which allows <null> values for all fields. Now when I call the information from this database it is going into an html table and I dont want a blank cell which looks a little ugly in html. Now I figured it would be straight forward enough to go:

<% if objRS.(&quot;such&suchafield&quot = &quot;&quot; Then
Response.Write &quot;& n b s p ;&quot; '(without the spaces they are just for show)
Else
Response.Write objRS.(&quot;such&suchafield&quot
End If
%>

Little did I suspect that this would not work. Now I am confused. Is SQL passing a value to the asp code thus preventing the objRS.(&quot;such&suchafield&quot from equalling 0? or is there something else going on I have missed?

I hope somebody can help with the mystery of the empty spaces?


Justin. X-)
&quot;Creativity is the ability to introduce order into the randomness of nature.&quot; Eric Hoffer

Visit me at

http://www.dfodesign.co.nz

 

[TimLarkin]

try:
<% if IsNull(objRS.(&quot;such&suchafield&quot) OR objRS.(&quot;such&suchafield&quot = &quot;&quot; Then

 

[Mighty]

TimLarkin's solution should do the trick. Just a slight modification that should do the same thing:

<% if IsNull(objRS.(&quot;such&suchafield&quot) OR isEmpty(objRS.(&quot;such&suchafield&quot) Then


Mise Le Meas,

Mighty

 

[Shilohcity]

Hi there

Thanks for your suggestions however I am still not able to write anything if the field is empty. This is a mystery which is kind of growing on me now so any further suggestions are most welcome.

This is the current code I am using:

<%If isNull(objrs(&quot;per_fstname&quot) or isEmpty(objrs(&quot;per_fstname&quot) or objRS(&quot;per_fstname&quot = &quot;&quot; Then
Response.Write &quot;empty&quot;
Else
Response.Write objRS(&quot;per_fstname&quot
End If%>

Thanks again for your help. X-) &quot;Creativity is the ability to introduce order into the randomness of nature.&quot; Eric Hoffer

Visit me at

http://www.dfodesign.co.nz

 

[TimLarkin]

Your code looks ok as far as i can see.
looks like the data is not what your expecting.
Put in a Response.Write &quot;MyValue=&quot; & objrs(&quot;per_fstname&quot & &quot;**&quot; just before the if statement. see what it says.
Put Response.Write &quot;Else&quot; Response.End right after the Else statement and read in a record that you think should not be going to the Else section. If it writes &quot;Else&quot; then your not checking against what the data actually is. Etc........ Tim

Remember the KISS principle:
Keep It Simple, Stupid!

 

[Shilohcity]

Hi Tim

I have kind of narrowed things down a little bit in that if I put a value like &quot;-&quot; in the database field and alter my code to refelect thie i.e. if = &quot;-&quot; then.... Everything goes according to plan. So....now Im thinking that the page that actually submits the information to the database is entering something into the database fields, but what, is the question? When I view the SQL table it looks pretty empty. My submission page has heaps of field many of which get left blank and to complicate things a little is written in JSP. I am now assuming that this is submitting a blank text string to SQL when no values are entered, but this still doesnt really help as SQL must be interpreting this as a value so isNull is false and isEmpty is false etc...

The solution so far is to give all the fields a value of &quot;-&quot; but still isopen to probs as users can manually delete this from the text box...

Well I guess the struggle continues..

Thanks to everybody for your help so far.

Justin. X-) &quot;Creativity is the ability to introduce order into the randomness of nature.&quot; Eric Hoffer

Visit me at

http://www.dfodesign.co.nz

 

[Geee]

This may help.

I have seen instances when using HTML forms to populate SQL databases when if the length of the string is less than the size of the text field, trailing spaces are added. Try adding a TRIM statement before you check to see if its NULL, ie...

per_fstname = TRIM(per_fstname)

Hope this helps...

Gary