database called form posting 'null' values

[Jeremy]

I'm hoping someone can help me with my newbie question.

I have a form which is calling from 2 tables, which are linked by a
person's name. One table has information specific data to a thing and
the other table has contact information such as address1, address2,
city, state, zip, coutry, org, phone1, phone2, email. Currently, the
information posts as the following example:

Bob Jones
111 South St.
null
Anywhere, NY, 14101 USA
(202) 327 4511, null
null

wherein the blank database feilds are showing null. The way I have it
set up is to call these values and insert it into a form table which
is populated until EOF.

I would like to have the null fields be ignored, but if I put an If
NOT ISNULL into the table generating code, it comes up with an
error...

Am I being clear about the problem? If so...any ideas?

Thanks,

Jeremy

[Bob Barrows]

Jeremy wrote:

> I'm hoping someone can help me with my newbie question.
>
> I have a form which is calling from 2 tables, which are linked by a
> person's name. One table has information specific data to a thing and
> the other table has contact information such as address1, address2,
> city, state, zip, coutry, org, phone1, phone2, email. Currently, the
> information posts as the following example:
>
> Bob Jones
> 111 South St.
> null
> Anywhere, NY, 14101 USA
> (202) 327 4511, null
> null
>
> wherein the blank database feilds are showing null. The way I have it
> set up is to call these values and insert it into a form table which
> is populated until EOF.
>
> I would like to have the null fields be ignored, but if I put an If
> NOT ISNULL into the table generating code, it comes up with an
> error...
>
> Am I being clear about the problem? If so...any ideas?
>
> Thanks,
>
> Jeremy

You need to show us the line of code you are using .... and tell us what the
error is!

Bob Barrows


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Samuel Hon]

Hi

I'm guessing you want this:

> Bob Jones
> 111 South St.
> null
> Anywhere, NY, 14101 USA
> (202) 327 4511, null
> null

to be like this:

> Bob Jones
> 111 South St.
> Anywhere, NY, 14101 USA
> (202) 327 4511

This depends on how you're extracting the data from the database. I'm
assuming (again) that you're doing

objRst.Open YourSQL

Do Until objRst.EOF
Response.Write objRst.Fields("Name") & "<BR />"
Response.Write objRst.Fields("Address1") & "<BR />"
Response.Write objRst.Fields("Address2") & "<BR />"
Response.Write objRst.Fields("City") & "<BR />"
...

objRst.MoveNext
Loop

What you need to do is add this function

Private Function NZ(vntValue, vntNull)
If IsNull(vntValue) Then
NZ = vntNull
Else
NZ = vntValue
End If
End Function

and change the lines
Response.Write objRst.Fields("Name") & "<br />"
to
If Len(NZ(objRst.Fields("Name"),"")) <> 0 Then Response.Write
objRst.Fields("Name") & "<br />

The function NZ checks to see if the field is null, and if it is,
replace it with vntNull which is "". By using len aroundn NZ, you can
check Null and an empty string in one line because you cant do
If IsNull(objRst.Fields("Name")) = False And objRst.Fields("Name")
<> "" Then
because if the field is null, the line will fail on the second test

I hope this helps (and I've assumed correctly)

Sam

[email]jeremy_zifchock@yahoo.com[/email] (Jeremy) wrote in message news:<f9d85033.0402141110.6073f952@posting.google. com>...

> I'm hoping someone can help me with my newbie question.
>
> I have a form which is calling from 2 tables, which are linked by a
> person's name. One table has information specific data to a thing and
> the other table has contact information such as address1, address2,
> city, state, zip, coutry, org, phone1, phone2, email. Currently, the
> information posts as the following example:
>
> Bob Jones
> 111 South St.
> null
> Anywhere, NY, 14101 USA
> (202) 327 4511, null
> null
>
> wherein the blank database feilds are showing null. The way I have it
> set up is to call these values and insert it into a form table which
> is populated until EOF.
>
> I would like to have the null fields be ignored, but if I put an If
> NOT ISNULL into the table generating code, it comes up with an
> error...
>
> Am I being clear about the problem? If so...any ideas?
>
> Thanks,
>
> Jeremy

[Jeremy]

Thanks,

But actually, my code is like this...

<%@ language = "JavaScript" %>
<%
var strconnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:/Inetpub/wwwroot/ITI/fpdb/photo.mdb";
%>

<head>
<script language=JavaScript>

<%
var tbl="";
var rs = Server.CreateObject("ADODB.Recordset");
var sql = "SELECT DISTINCT Photos.Photo, Photos.Artist,
Artists.Organization, Artists.Address1 FROM Photos, Artists WHERE
Photos.Artist = Artists.Artist";

rs.Open(sql,strconnection);
var n=0;
while (!rs.EOF)
{
var img = rs.Fields("Photo");
var art = rs.Fields("Artist");
var org = rs.Fields("Organization");
var ad1 = rs.Fields("Address1");

{
var tbl = tbl += "<table border='0'>\n";
{
tbl += " <td>\n";
tbl += " <IMG src='images/" + img + "'>\n";
tbl += " </td>\n";
tbl += " <td>Artist:" + art + " \n";
tbl += " <br>" + org + "\n";
tbl += " <br> " + ad1 + "\n";
}
tbl += "</table>\n";
}

rs.MoveNext();
n++;
}

rs.Close();
Response.Write("document.repForm.write('" + tbl + "')")

%>

</script>
</head>
<html>

<BODY>

<%=tbl%>

</BODY>
</HTML>


Hopefully this helps....

Perhaps you'd suggest another way?.? I'd be happy to hear it.

Thanks,

Jeremy

[Samuel Hon]

Lost me now, my Javascript isnt that good. Handing over to the next man...

[email]jeremy_zifchock@yahoo.com[/email] (Jeremy) wrote in message news:<f9d85033.0402161608.e971198@posting.google.c om>...

> Thanks,
>
> But actually, my code is like this...
>
> <%@ language = "JavaScript" %>
> <%
> var strconnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data
> Source=C:/Inetpub/wwwroot/ITI/fpdb/photo.mdb";
> %>
>
> <head>
> <script language=JavaScript>
>
> <%
> var tbl="";
> var rs = Server.CreateObject("ADODB.Recordset");
> var sql = "SELECT DISTINCT Photos.Photo, Photos.Artist,
> Artists.Organization, Artists.Address1 FROM Photos, Artists WHERE
> Photos.Artist = Artists.Artist";
>
> rs.Open(sql,strconnection);
> var n=0;
> while (!rs.EOF)
> {
> var img = rs.Fields("Photo");
> var art = rs.Fields("Artist");
> var org = rs.Fields("Organization");
> var ad1 = rs.Fields("Address1");
>
> {
> var tbl = tbl += "<table border='0'>\n";
> {
> tbl += " <td>\n";
> tbl += " <IMG src='images/" + img + "'>\n";
> tbl += " </td>\n";
> tbl += " <td>Artist:" + art + " \n";
> tbl += " <br>" + org + "\n";
> tbl += " <br> " + ad1 + "\n";
> }
> tbl += "</table>\n";
> }
>
> rs.MoveNext();
> n++;
> }
>
> rs.Close();
> Response.Write("document.repForm.write('" + tbl + "')")
>
> %>
>
> </script>
> </head>
> <html>
>
> <BODY>
>
> <%=tbl%>
>
> </BODY>
> </HTML>
>
>
> Hopefully this helps....
>
> Perhaps you'd suggest another way?.? I'd be happy to hear it.
>
> Thanks,
>
> Jeremy