|
 |
asp_databases thread: RE: ASP script not working in either SQL/Access
Message #1 by Neile Bermudes <Neile.Bermudes@e...> on Tue, 26 Nov 2002 08:45:09 -0000
|
|
Peter,
OK-- i've added in your function. I'm getting another unterminated string
constant in the function code, on the str=Replace line. Here's my script
(check for too many/too little spaces in the function you wrote for me, as i
can't tell where i need to put a space or not):
<%@ 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=Sharepoint;DATABASE=pubs;" & _
"USER ID=neile;PASSWORD=password;"
SqlJunk = "SELECT tblPeople.PersonFirstName,
tblOrganisations.OrganisationName FROM tblPeople INNER JOIN
(tblOrganisations INNER JOIN xrfPeopleToOrganisation ON
tblOrganisations.OrganisationID = xrfPeopleToOrganisation.OrganisationID) ON
tblPeople.PersonID = xrfPeopleToOrganisation.PersonID"
If Request.Form("TypeSearch") = "PersonFirstName" Then
tmpStr="%" & Request.Form("DaInBox") & "%"
SqlJunk = SqlJunk & " WHERE PersonFirstName LIKE " & sql_quote(tmpStr)
End If
If Request.Form("TypeSearch") = "PersonLastName" Then
SqlJunk = SqlJunk & " WHERE PersonLastName LIKE '%" & _
Request.Form("DaInBox") & "%'"
End If
If Request.Form("TypeSearch") = "JobTitle" Then
SqlJunk = SqlJunk & " WHERE JobTitle LIKE '%" & _
Request.Form("DaInBox") & "%'"
End If
If Request.Form("TypeSearch") = "OrganisationName" Then
SqlJunk = SqlJunk & " WHERE OrganisationName LIKE '%" & _
Request.Form("DaInBox") & "%'"
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" width="1024">
<tr>
<th bgcolor="blue"><font face="Arial" color="#FFFFFF">Full Name
</font></th>
<th bgcolor="blue"><font face="Arial" color="#FFFFFF">Job Title
</font></th>
<th bgcolor="blue"><font face="Arial" color="#FFFFFF">Function
</font></th>
<th bgcolor="blue"><font face="Arial"
color="#FFFFFF">Organization</font></th>
<th bgcolor="blue"><font face="Arial" color="#FFFFFF">Business Address
</font></th>
<th bgcolor="blue"><font face="Arial" color="#FFFFFF">Telephone
</font></th>
<th bgcolor="blue"><font face="Arial" color="#FFFFFF">Mobile
</font></th>
<th bgcolor="blue"><font face="Arial" color="#FFFFFF">E-mail
</font></th>
</tr>
<%
Do While Not rsECBCon.EOF
%>
<tr>
<td width="300"><%=rsECBCon("PersonFirstName")%>
<%=rsECBCon("PersonLastName")%>
</td>
<td width="300"><%=rsECBCon("JobTitle")%>
</td>
<td width="150"><%=rsECBCon("ColumnFunction")%>
</td>
<td><%=rsECBCon("OrganisationName")%>
</td>
<td width="400"><%=rsECBCon("PersonAddress1")%>
<%=rsECBCon("PersonAddress2")%>
<%=rsECBCon("PersonAddress3")%>
<%=rsECBCon("PersonAddress4")%>
<%=rsECBCon("PersonPostCode")%>
</td>
<td><%=rsECBCon("PersonWorkPhone")%>
</td>
<td width="200"><%=rsECBCon("PersonMobilePhone")%>
</td>
<td width="200"><%=rsECBCon("WorkEmail")%>
</td>
<tr>
<% rsECBCon.MoveNext
Loop
%>
</table>
<%End If%>
<%End If%>
<%
rsECBCon.Close
ECBCon.Close
%>
<p> </p>
</body>
</html>
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 "Craig Flannigan" <ckf@k...> on Tue, 26 Nov 2002 09:10:41 -0000
|
|
I might be wrong here, but it looks like you've put the wrong character into
your Replace command.
You've got: Replace(str,"'",""")
You've used a " where it should be two '
Remove the spaces from this...
Replace(str," ' ", " ' ' ")
and it should work.
HTH
Craig.
-----Original Message-----
From: Neile Bermudes [mailto:Neile.Bermudes@e...]
Sent: 26 November 2002 08:45
To: ASP Databases
Subject: [asp_databases] RE: ASP script not working in either SQL/Access
Peter,
OK-- i've added in your function. I'm getting another unterminated string
constant in the function code, on the str=Replace line. Here's my script
(check for too many/too little spaces in the function you wrote for me, as i
can't tell where i need to put a space or not):
<%@ 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=Sharepoint;DATABASE=pubs;" & _
"USER ID=neile;PASSWORD=password;"
SqlJunk = "SELECT tblPeople.PersonFirstName,
tblOrganisations.OrganisationName FROM tblPeople INNER JOIN
(tblOrganisations INNER JOIN xrfPeopleToOrganisation ON
tblOrganisations.OrganisationID = xrfPeopleToOrganisation.OrganisationID) ON
tblPeople.PersonID = xrfPeopleToOrganisation.PersonID"
If Request.Form("TypeSearch") = "PersonFirstName" Then
tmpStr="%" & Request.Form("DaInBox") & "%"
SqlJunk = SqlJunk & " WHERE PersonFirstName LIKE " & sql_quote(tmpStr)
End If
If Request.Form("TypeSearch") = "PersonLastName" Then
SqlJunk = SqlJunk & " WHERE PersonLastName LIKE '%" & _
Request.Form("DaInBox") & "%'"
End If
If Request.Form("TypeSearch") = "JobTitle" Then
SqlJunk = SqlJunk & " WHERE JobTitle LIKE '%" & _
Request.Form("DaInBox") & "%'"
End If
If Request.Form("TypeSearch") = "OrganisationName" Then
SqlJunk = SqlJunk & " WHERE OrganisationName LIKE '%" & _
Request.Form("DaInBox") & "%'"
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" width="1024">
<tr>
<th bgcolor="blue"><font face="Arial" color="#FFFFFF">Full Name
</font></th>
<th bgcolor="blue"><font face="Arial" color="#FFFFFF">Job Title
</font></th>
<th bgcolor="blue"><font face="Arial" color="#FFFFFF">Function
</font></th>
<th bgcolor="blue"><font face="Arial"
color="#FFFFFF">Organization</font></th>
<th bgcolor="blue"><font face="Arial" color="#FFFFFF">Business Address
</font></th>
<th bgcolor="blue"><font face="Arial" color="#FFFFFF">Telephone
</font></th>
<th bgcolor="blue"><font face="Arial" color="#FFFFFF">Mobile
</font></th>
<th bgcolor="blue"><font face="Arial" color="#FFFFFF">E-mail
</font></th>
</tr>
<%
Do While Not rsECBCon.EOF
%>
<tr>
<td width="300"><%=rsECBCon("PersonFirstName")%>
<%=rsECBCon("PersonLastName")%>
</td>
<td width="300"><%=rsECBCon("JobTitle")%>
</td>
<td width="150"><%=rsECBCon("ColumnFunction")%>
</td>
<td><%=rsECBCon("OrganisationName")%>
</td>
<td width="400"><%=rsECBCon("PersonAddress1")%>
<%=rsECBCon("PersonAddress2")%>
<%=rsECBCon("PersonAddress3")%>
<%=rsECBCon("PersonAddress4")%>
<%=rsECBCon("PersonPostCode")%>
</td>
<td><%=rsECBCon("PersonWorkPhone")%>
</td>
<td width="200"><%=rsECBCon("PersonMobilePhone")%>
</td>
<td width="200"><%=rsECBCon("WorkEmail")%>
</td>
<tr>
<% rsECBCon.MoveNext
Loop
%>
</table>
<%End If%>
<%End If%>
<%
rsECBCon.Close
ECBCon.Close
%>
<p> </p>
</body>
</html>
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.
_____________________________________________________________________
Please contact I.T. Support if you have received this email in error.
This e-mail has been scanned for all viruses by Star Internet.
_____________________________________________________________________
_____________________________________________________________________
Kingfield Heath Ltd. Email Disclaimer
Confidentiality : This email and its attachments are intended for the
above-named only and may be confidential. If they have come to you in
error you must take no action based on them, nor must you copy or
show them to anyone; please reply to this email and highlight the
error.
Security Warning : Please note that this email has been created in
the knowledge that the internet is not a 100% secure communications
medium. We advise that you understand and observe this lack of
security when emailing us.
Viruses : Although we have taken steps to ensure that this email and
attachments are free from any virus, we advise that, in keeping with
good computing practice, the recipient should ensure they are
actually virus free.
_____________________________________________________________________
|
|
|
View Cart
