|
 |
asp_databases thread: Script fails if any SQL fields are null
Message #1 by Neile Bermudes <Neile.Bermudes@e...> on Fri, 29 Nov 2002 16:17:52 -0000
|
|
Hi,
I've got an ASP script that searches for information in an SQL database. It
returns rows according to the search criteria it picks up from the user HTML
form i've created. The script runs perfectlt (after help from some of you
guys) but it will fail if any of the fields it is supposed to return are
NULL. I've tired updating my SQL tables with an update statement, changing
all null fields to contain some value, but that didn't work. So i'm
wondering if i can add something to my script that will enable it to return
null values too?
Here's my script:
<%@ LANGUAGE="VBSCRIPT" %>
<html>
<head>
<title>Directory Search Results</title>
</head>
<body>
<%
Dim ECBCon
Dim SqlJunk
Function sql_quote(str)
str=Replace(str,"'","''")
sql_quote="'"&str&"'"
End Function
Set ECBCon = Server.CreateObject("ADODB.Connection")
ECBCon.Open "PROVIDER=SQLOLEDB;" & _
"DATA SOURCE=ECB-DELL04;DATABASE=ECB Directory 2003;" & _
"USER ID=sa;PASSWORD=thames;"
SqlJunk = "SELECT tblPeople.PersonID, tblOrganisations.OrganisationName,
tblFunctions.ColumnFunction, xrfPeopleToOrganisation.JobTitle,
tblPeople.PersonTitle, tblPeople.PersonFirstName,
tblPeople.PersonMiddleNameOrInitial, tblPeople.PersonLastName,
tblPeople.PersonPostNominals, tblPeople.PersonAddress1,
tblPeople.PersonAddress2, tblPeople.PersonAddress3,
tblPeople.PersonAddress4, tblPeople.PersonPostCode,
tblPeople.PersonHomePhone, tblPeople.PersonHomeFax,
tblPeople.[PersonHomee-mail], tblPeople.PersonHomeWebSite,
tblPeople.PersonMobilePhone, tblPeople.PersonWorkPhone,
tblPeople.PersonWorkFax, tblPeople.[PersonWorke-mail],
tblPeople.PersonWorkWebSite FROM tblFunctions INNER JOIN ((tblOrganisations
INNER JOIN xrfCategoryToOrganisation ON tblOrganisations.OrganisationID
xrfCategoryToOrganisation.OrganisationID) INNER JOIN (tblPeople INNER JOIN
xrfPeopleToOrganisation ON tblPeople.PersonID
xrfPeopleToOrganisation.PersonID) ON tblOrganisations.OrganisationID
xrfPeopleToOrganisation.OrganisationID) ON tblFunctions.FunctionID
xrfPeopleToOrganisation.FunctionID"
If Request.Form("TypeSearch") = "FirstName" Then
tmpStr="%" & Request.Form("DaInBox") & "%"
SqlJunk = SqlJunk & " WHERE PersonFirstName LIKE " & sql_quote(tmpStr)
End If
If Request.Form("TypeSearch") = "LastName" Then
tmpStr="%" & Request.Form("DaInBox") & "%"
SqlJunk = SqlJunk & " WHERE PersonLastName LIKE " & sql_quote(tmpStr)
End If
If Request.Form("TypeSearch") = "JobTitle" Then
tmpStr="%" & Request.Form("DaInBox") & "%"
SqlJunk = SqlJunk & " WHERE JobTitle LIKE " & sql_quote(tmpStr)
End If
If Request.Form("TypeSearch") = "OrganisationName" Then
tmpStr="%" & Request.Form("DaInBox") & "%"
SqlJunk = SqlJunk & " WHERE OrganisationName LIKE " & sql_quote(tmpStr)
End If
Set rsECBCon = Server.CreateObject("ADODB.Recordset")
rsECBCon.Open SqlJunk, ECBCon, 3
%>
<%
If rsECBCon.BOF and rsECBCon.EOF Then%>
<h2 align="center"> <font color="#0000FF"> We did not find a
match!</font></h2>
<%Else%>
<%If Not rsECBCon.BOF Then%>
<h2><font color="#0000FF">Here are the results of your
search: </font></h2>
<table BORDER="0">
<tr>
<th bgcolor="blue" width="152">
<p align="left"><font face="Arial" color="#FFFFFF">Full Name
</font></th>
<th bgcolor="blue" width="148">
<p align="left"><font face="Arial" color="#FFFFFF">Job Title
</font></th>
<th bgcolor="blue" width="262">
<p align="left"><font face="Arial" color="#FFFFFF">Organisation
</font></th>
</tr>
<%
Do While Not rsECBCon.EOF
%>
<tr>
<td width="152"><%=rsECBCon("PersonTitle")%>
<%=rsECBCon("PersonFirstName")%>
<%=rsECBCon("PersonLastName")%>
</td>
<td width="148"><%=rsECBCon("JobTitle")%>
</td>
<td width="262"><%=rsECBCon("OrganisationName")%>
</td>
<tr>
<% rsECBCon.MoveNext
Loop
%>
</table>
<%End If%>
<%End If%>
<%
rsECBCon.Close
ECBCon.Close
%>
<p> </p>
</body>
</html>
Any help MUCh appreciated
Thanx
Neile
--
Neile Bermudes
Network Support Analyst
ECB IT Dept
--> 0207 4321241
The contents of this e-mail are the property of the ECB. This e-mail (and any attachments)
are confidential and intended for use by the named recipient(s) only. It may contain
confidential or privileged information. Any opinions expressed herein are those of the
individual and not necessarily those of the ECB. If you are not the intended recipient would
you please immediately delete this e-mail and any attachments from your computer system
without publishing, copying or distributing it, and notify the sender at the ECB. Do not
disclose this e-mail or the information disclosed herein to any other person, use it for any
purpose other than intended, or copy, distribute or store the information on any medium.
Copyright in this e-mail and attachments created by us belong to ECB and the author also
asserts the right to be identified as such and to prevent any misuse. Please conduct your
own virus checks. The ECB does not accept responsibility or liability for any virus or any
loss or damage caused by such software virus. Our web site is at http://www.ecb.co.uk.
Message #2 by Sam Clohesy <sam@e...> on Fri, 29 Nov 2002 16:27:15 -0000
|
|
You could do something like:
If rsECBCon("PersonTitle") = "" Then response.write " "
else
response.write rsECBCon("PersonTitle")
End if
Cheers
Sam
-----Original Message-----
From: Neile Bermudes [mailto:Neile.Bermudes@e...]
Sent: 29 November 2002 16:18
To: ASP Databases
Subject: [asp_databases] Script fails if any SQL fields are null
Hi,
I've got an ASP script that searches for information in an SQL database. It
returns rows according to the search criteria it picks up from the user HTML
form i've created. The script runs perfectlt (after help from some of you
guys) but it will fail if any of the fields it is supposed to return are
NULL. I've tired updating my SQL tables with an update statement, changing
all null fields to contain some value, but that didn't work. So i'm
wondering if i can add something to my script that will enable it to return
null values too?
Here's my script:
<%@ LANGUAGE="VBSCRIPT" %>
<html>
<head>
<title>Directory Search Results</title>
</head>
<body>
<%
Dim ECBCon
Dim SqlJunk
Function sql_quote(str)
str=Replace(str,"'","''")
sql_quote="'"&str&"'"
End Function
Set ECBCon = Server.CreateObject("ADODB.Connection")
ECBCon.Open "PROVIDER=SQLOLEDB;" & _
"DATA SOURCE=ECB-DELL04;DATABASE=ECB Directory 2003;" & _
"USER ID=sa;PASSWORD=thames;"
SqlJunk = "SELECT tblPeople.PersonID, tblOrganisations.OrganisationName,
tblFunctions.ColumnFunction, xrfPeopleToOrganisation.JobTitle,
tblPeople.PersonTitle, tblPeople.PersonFirstName,
tblPeople.PersonMiddleNameOrInitial, tblPeople.PersonLastName,
tblPeople.PersonPostNominals, tblPeople.PersonAddress1,
tblPeople.PersonAddress2, tblPeople.PersonAddress3,
tblPeople.PersonAddress4, tblPeople.PersonPostCode,
tblPeople.PersonHomePhone, tblPeople.PersonHomeFax,
tblPeople.[PersonHomee-mail], tblPeople.PersonHomeWebSite,
tblPeople.PersonMobilePhone, tblPeople.PersonWorkPhone,
tblPeople.PersonWorkFax, tblPeople.[PersonWorke-mail],
tblPeople.PersonWorkWebSite FROM tblFunctions INNER JOIN ((tblOrganisations
INNER JOIN xrfCategoryToOrganisation ON tblOrganisations.OrganisationID
xrfCategoryToOrganisation.OrganisationID) INNER JOIN (tblPeople INNER JOIN
xrfPeopleToOrganisation ON tblPeople.PersonID
xrfPeopleToOrganisation.PersonID) ON tblOrganisations.OrganisationID
xrfPeopleToOrganisation.OrganisationID) ON tblFunctions.FunctionID
xrfPeopleToOrganisation.FunctionID"
If Request.Form("TypeSearch") = "FirstName" Then
tmpStr="%" & Request.Form("DaInBox") & "%"
SqlJunk = SqlJunk & " WHERE PersonFirstName LIKE " & sql_quote(tmpStr)
End If
If Request.Form("TypeSearch") = "LastName" Then
tmpStr="%" & Request.Form("DaInBox") & "%"
SqlJunk = SqlJunk & " WHERE PersonLastName LIKE " & sql_quote(tmpStr)
End If
If Request.Form("TypeSearch") = "JobTitle" Then
tmpStr="%" & Request.Form("DaInBox") & "%"
SqlJunk = SqlJunk & " WHERE JobTitle LIKE " & sql_quote(tmpStr)
End If
If Request.Form("TypeSearch") = "OrganisationName" Then
tmpStr="%" & Request.Form("DaInBox") & "%"
SqlJunk = SqlJunk & " WHERE OrganisationName LIKE " & sql_quote(tmpStr)
End If
Set rsECBCon = Server.CreateObject("ADODB.Recordset")
rsECBCon.Open SqlJunk, ECBCon, 3
%>
<%
If rsECBCon.BOF and rsECBCon.EOF Then%>
<h2 align="center"> <font color="#0000FF"> We did not find a
match!</font></h2>
<%Else%>
<%If Not rsECBCon.BOF Then%>
<h2><font color="#0000FF">Here are the results of your
search: </font></h2>
<table BORDER="0">
<tr>
<th bgcolor="blue" width="152">
<p align="left"><font face="Arial" color="#FFFFFF">Full Name
</font></th>
<th bgcolor="blue" width="148">
<p align="left"><font face="Arial" color="#FFFFFF">Job Title
</font></th>
<th bgcolor="blue" width="262">
<p align="left"><font face="Arial" color="#FFFFFF">Organisation
</font></th>
</tr>
<%
Do While Not rsECBCon.EOF
%>
<tr>
<td width="152"><%=rsECBCon("PersonTitle")%>
<%=rsECBCon("PersonFirstName")%>
<%=rsECBCon("PersonLastName")%>
</td>
<td width="148"><%=rsECBCon("JobTitle")%>
</td>
<td width="262"><%=rsECBCon("OrganisationName")%>
</td>
<tr>
<% rsECBCon.MoveNext
Loop
%>
</table>
<%End If%>
<%End If%>
<%
rsECBCon.Close
ECBCon.Close
%>
<p> </p>
</body>
</html>
Any help MUCh appreciated
Thanx
Neile
--
Neile Bermudes
Network Support Analyst
ECB IT Dept
--> 0207 4321241
The contents of this e-mail are the property of the ECB. This e-mail (and
any attachments)
are confidential and intended for use by the named recipient(s) only. It
may contain
confidential or privileged information. Any opinions expressed herein are
those of the
individual and not necessarily those of the ECB. If you are not the intended
recipient would
you please immediately delete this e-mail and any attachments from your
computer system
without publishing, copying or distributing it, and notify the sender at the
ECB. Do not
disclose this e-mail or the information disclosed herein to any other
person, use it for any
purpose other than intended, or copy, distribute or store the information on
any medium.
Copyright in this e-mail and attachments created by us belong to ECB and the
author also
asserts the right to be identified as such and to prevent any misuse.
Please conduct your
own virus checks. The ECB does not accept responsibility or liability for
any virus or any
loss or damage caused by such software virus. Our web site is at
http://www.ecb.co.uk.
|
|
|
View Cart
