|
 |
asp_databases thread: re: asp script not working in either SQL or Access
Message #1 by Neile Bermudes <Neile.Bermudes@e...> on Tue, 26 Nov 2002 07:02:22 -0000
|
|
OK guys, i fixed my first VB compilation Unterminated problem!! All i did
was put the whole SQL query on the same darn line! It's so picky!! Anyway,
i'm now having problems further in my script. If you refer to my script
below, at the first "If" statement-- this is where i am getting an error.
Error = Microsoft VBScript compilation (0x800A0408)
Invalid character
/ECB2002Engine.asp, line 23, column 50
SqlFunk = SqlFunk & " WHERE PersonFirstName LIKE "%" & _
-------------------------------------------------^
AT first it seems to reference the & _ part, so i've taken that out and put
the rest of the code on the same line, so it looks like this:
SqlFunk = SqlFunk & "WHERE PersonFirstName LIKE "%" Request.Form("DaInBox")
& "%"" (all on same line in my script, no space between Request.Form and
paranthesis)
Then i get:
Microsoft VBScript compilation (0x800A0408)
Invalid character
/ECB2002Engine.asp, line 23, column 49
SqlFunk = SqlFunk & "WHERE PersonFirstName LIKE
"%" Request.Form("DaInBox") & "%""
------------------------------------------------^
Can anyone help??? WHole script below:
<%@ LANGUAGE="VBSCRIPT" %>
<html>
<head>
<title>Directory Search Results</title>
</head>
<body>
<%
Dim ECBCon
Dim SqlFunk
SqlFunk = replace(SqlFunk,"'","''")
Set ECBCon = Server.CreateObject("ADODB.Connection")
ECBCon.Open "PROVIDER=SQLOLEDB;" & _
"DATA SOURCE=Sharepoint;DATABASE=pubs;" & _
"USER ID=neile;PASSWORD=password;"
SqlFunk = "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
SqlFunk = SqlFunk & "WHERE PersonFirstName LIKE "%" Request.Form("DaInBox")
& "%""
End If
If Request.Form("TypeSearch") = "PersonLastName" Then
SqlFunk = SqlFunk & " WHERE PersonLastName LIKE "%" Request.Form("DaInBox")
& "%""
End If
If Request.Form("TypeSearch") = "JobTitle" Then
SqlFunk = SqlFunk & " WHERE JobTitle LIKE "%" Request.Form("DaInBox")& "%""
End If
If Request.Form("TypeSearch") = "OrganisationName" Then
SqlFunk = SqlFunk & " WHERE OrganisationName LIKE "%"
Request.Form("DaInBox") & "%""
End If
Set rsECBCon = Server.CreateObject("ADODB.Recordset")
rsECBCon.Open SqlFunk, 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
set ECBCon = Nothing
set rsECBCon = Nothing
%>
<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:13:15 -0000
|
|
Change your SQL to this...
SqlFunk = SqlFunk & " WHERE PersonFirstName LIKE '%" & _
-------------------------------------------------------------^
Single ' not ".
Use " to start a string. If your SQL needs a speechmark in it, then change
them to '.
I usually use MS Query to build my SQL and them copy it over to my webpages.
This means that I end up with " where really it should be '.
You'll need to ensure the other side to that query also uses '. Only use "
at the very start and end of the SQL line.
Craig.
-----Original Message-----
From: Neile Bermudes [mailto:Neile.Bermudes@e...]
Sent: 26 November 2002 07:02
To: ASP Databases
Subject: [asp_databases] re: asp script not working in either SQL or
Access
OK guys, i fixed my first VB compilation Unterminated problem!! All i did
was put the whole SQL query on the same darn line! It's so picky!! Anyway,
i'm now having problems further in my script. If you refer to my script
below, at the first "If" statement-- this is where i am getting an error.
Error = Microsoft VBScript compilation (0x800A0408)
Invalid character
/ECB2002Engine.asp, line 23, column 50
SqlFunk = SqlFunk & " WHERE PersonFirstName LIKE "%" & _
-------------------------------------------------^
AT first it seems to reference the & _ part, so i've taken that out and put
the rest of the code on the same line, so it looks like this:
SqlFunk = SqlFunk & "WHERE PersonFirstName LIKE "%" Request.Form("DaInBox")
& "%"" (all on same line in my script, no space between Request.Form and
paranthesis)
Then i get:
Microsoft VBScript compilation (0x800A0408)
Invalid character
/ECB2002Engine.asp, line 23, column 49
SqlFunk = SqlFunk & "WHERE PersonFirstName LIKE
"%" Request.Form("DaInBox") & "%""
------------------------------------------------^
Can anyone help??? WHole script below:
<%@ LANGUAGE="VBSCRIPT" %>
<html>
<head>
<title>Directory Search Results</title>
</head>
<body>
<%
Dim ECBCon
Dim SqlFunk
SqlFunk = replace(SqlFunk,"'","''")
Set ECBCon = Server.CreateObject("ADODB.Connection")
ECBCon.Open "PROVIDER=SQLOLEDB;" & _
"DATA SOURCE=Sharepoint;DATABASE=pubs;" & _
"USER ID=neile;PASSWORD=password;"
SqlFunk = "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
SqlFunk = SqlFunk & "WHERE PersonFirstName LIKE "%" Request.Form("DaInBox")
& "%""
End If
If Request.Form("TypeSearch") = "PersonLastName" Then
SqlFunk = SqlFunk & " WHERE PersonLastName LIKE "%" Request.Form("DaInBox")
& "%""
End If
If Request.Form("TypeSearch") = "JobTitle" Then
SqlFunk = SqlFunk & " WHERE JobTitle LIKE "%" Request.Form("DaInBox")& "%""
End If
If Request.Form("TypeSearch") = "OrganisationName" Then
SqlFunk = SqlFunk & " WHERE OrganisationName LIKE "%"
Request.Form("DaInBox") & "%""
End If
Set rsECBCon = Server.CreateObject("ADODB.Recordset")
rsECBCon.Open SqlFunk, 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
set ECBCon = Nothing
set rsECBCon = Nothing
%>
<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
