|
 |
asp_databases thread: ASP Script not working: with either SQL or Access
Message #1 by "neile" <neile.bermudes@e...> on Mon, 25 Nov 2002 13:09:34
|
|
Hi There,
I have an ASP script that seems to work perfectly with an Access database
containing only one table. Now the database has been modified and has
several tables. My script no longer works, even though i know my SQL
syntax is correct as i've tested it in Query Analyzer. I will paste my
whole script below. If you could check my whole script i'd be grateful,
but the specific error i'm getting is "Microsoft VBScript Compilation
(0x800A0409)-- Unterminated String Constant"-- it refers to the line
reading: SQLQuery = "Select..."
Keep in mind that the database connection is now for SQL Server as we are
moving to SQL now. But the script wont work in either ACCess or SQL Server
at the moment. HEre it is:
<%@ LANGUAGE="VBSCRIPT" %>
<html>
<head>
<title>Directory Search Results</title>
</head>
<body>
<%
Dim ECBCon
Dim SqlJunk
Set ECBCon = Server.CreateObject("ADODB.Connection")
ECBCon.Open "PROVIDER=SQLOLEDB;" & _
"DATA SOURCE=Sharepoint;DATABASE=pubs;" & _
"USER ID=sa;PASSWORD=;"
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") = "PersonFirstName" Then
SqlJunk = SqlJunk & " WHERE PersonFirstName LIKE '%" & Request.Form
("DaInBox") & "%'"
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>
Message #2 by "Craig Flannigan" <ckf@k...> on Mon, 25 Nov 2002 12:59:01 -0000
|
|
It means you're missing a closing speech mark " or you've too many of them.
Check that for each " you open, that you have a closing one " too.
The common area for this to fail is when you drop in and out of SQL and ASP
Variables.
-----Original Message-----
From: neile [mailto:neile.bermudes@e...]
Sent: 25 November 2002 13:10
To: ASP Databases
Subject: [asp_databases] ASP Script not working: with either SQL or
Access
Hi There,
I have an ASP script that seems to work perfectly with an Access database
containing only one table. Now the database has been modified and has
several tables. My script no longer works, even though i know my SQL
syntax is correct as i've tested it in Query Analyzer. I will paste my
whole script below. If you could check my whole script i'd be grateful,
but the specific error i'm getting is "Microsoft VBScript Compilation
(0x800A0409)-- Unterminated String Constant"-- it refers to the line
reading: SQLQuery = "Select..."
Keep in mind that the database connection is now for SQL Server as we are
moving to SQL now. But the script wont work in either ACCess or SQL Server
at the moment. HEre it is:
<%@ LANGUAGE="VBSCRIPT" %>
<html>
<head>
<title>Directory Search Results</title>
</head>
<body>
<%
Dim ECBCon
Dim SqlJunk
Set ECBCon = Server.CreateObject("ADODB.Connection")
ECBCon.Open "PROVIDER=SQLOLEDB;" & _
"DATA SOURCE=Sharepoint;DATABASE=pubs;" & _
"USER ID=sa;PASSWORD=;"
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") = "PersonFirstName" Then
SqlJunk = SqlJunk & " WHERE PersonFirstName LIKE '%" & Request.Form
("DaInBox") & "%'"
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>
_____________________________________________________________________
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.
_____________________________________________________________________
Message #3 by Neile Bermudes <Neile.Bermudes@e...> on Mon, 25 Nov 2002 14:20:18 -0000
|
|
I've read and re-read my whole script.....there really doesn't seem like
there's any missing quotes.... Could there be something else that might be
causing this?
-----Original Message-----
From: Craig Flannigan [mailto:ckf@k...]
Sent: 25 November 2002 12:59
To: ASP Databases
Subject: [asp_databases] RE: ASP Script not working: with either SQL or
Access
It means you're missing a closing speech mark " or you've too many of them.
Check that for each " you open, that you have a closing one " too.
The common area for this to fail is when you drop in and out of SQL and ASP
Variables.
-----Original Message-----
From: neile [mailto:neile.bermudes@e...]
Sent: 25 November 2002 13:10
To: ASP Databases
Subject: [asp_databases] ASP Script not working: with either SQL or
Access
Hi There,
I have an ASP script that seems to work perfectly with an Access database
containing only one table. Now the database has been modified and has
several tables. My script no longer works, even though i know my SQL
syntax is correct as i've tested it in Query Analyzer. I will paste my
whole script below. If you could check my whole script i'd be grateful,
but the specific error i'm getting is "Microsoft VBScript Compilation
(0x800A0409)-- Unterminated String Constant"-- it refers to the line
reading: SQLQuery = "Select..."
Keep in mind that the database connection is now for SQL Server as we are
moving to SQL now. But the script wont work in either ACCess or SQL Server
at the moment. HEre it is:
<%@ LANGUAGE="VBSCRIPT" %>
<html>
<head>
<title>Directory Search Results</title>
</head>
<body>
<%
Dim ECBCon
Dim SqlJunk
Set ECBCon = Server.CreateObject("ADODB.Connection")
ECBCon.Open "PROVIDER=SQLOLEDB;" & _
"DATA SOURCE=Sharepoint;DATABASE=pubs;" & _
"USER ID=sa;PASSWORD=;"
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") = "PersonFirstName" Then
SqlJunk = SqlJunk & " WHERE PersonFirstName LIKE '%" & Request.Form
("DaInBox") & "%'"
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>
_____________________________________________________________________
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.
_____________________________________________________________________
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 #4 by "Roy, Siddarth S" <sroy@b...> on Mon, 25 Nov 2002 09:25:31 -0500
|
|
Why dont u make the sql statement smaller first like two coloumns and if
it work then
make it four and test it again and another two columns . This will take
some time but it will surely get u near ur probelm
Hope this helps
sid
-----Original Message-----
From: Neile Bermudes [mailto:Neile.Bermudes@e...]
Sent: Monday, November 25, 2002 9:20 AM
To: ASP Databases
Subject: [asp_databases] RE: ASP Script not working: with either SQL o r
Access
I've read and re-read my whole script.....there really doesn't seem like
there's any missing quotes.... Could there be something else that might
be
causing this?
-----Original Message-----
From: Craig Flannigan [mailto:ckf@k...]
Sent: 25 November 2002 12:59
To: ASP Databases
Subject: [asp_databases] RE: ASP Script not working: with either SQL or
Access
It means you're missing a closing speech mark " or you've too many of
them.
Check that for each " you open, that you have a closing one " too.
The common area for this to fail is when you drop in and out of SQL and
ASP
Variables.
-----Original Message-----
From: neile [mailto:neile.bermudes@e...]
Sent: 25 November 2002 13:10
To: ASP Databases
Subject: [asp_databases] ASP Script not working: with either SQL or
Access
Hi There,
I have an ASP script that seems to work perfectly with an Access
database
containing only one table. Now the database has been modified and has
several tables. My script no longer works, even though i know my SQL
syntax is correct as i've tested it in Query Analyzer. I will paste my
whole script below. If you could check my whole script i'd be grateful,
but the specific error i'm getting is "Microsoft VBScript Compilation
(0x800A0409)-- Unterminated String Constant"-- it refers to the line
reading: SQLQuery =3D "Select..."
Keep in mind that the database connection is now for SQL Server as we
are
moving to SQL now. But the script wont work in either ACCess or SQL
Server
at the moment. HEre it is:
<%@ LANGUAGE=3D"VBSCRIPT" %>
<html>
<head>
<title>Directory Search Results</title>
</head>
<body>
<%
Dim ECBCon
Dim SqlJunk
Set ECBCon =3D Server.CreateObject("ADODB.Connection")
ECBCon.Open "PROVIDER=3DSQLOLEDB;" & _
"DATA SOURCE=3DSharepoint;DATABASE=3Dpubs;" & _
"USER ID=3Dsa;PASSWORD=3D;"
SqlJunk =3D "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 =3D
xrfCategoryToOrganisation.OrganisationID) INNER JOIN (tblPeople INNER
JOIN
xrfPeopleToOrganisation ON tblPeople.PersonID =3D
xrfPeopleToOrganisation.PersonID) ON tblOrganisations.OrganisationID =3D
xrfPeopleToOrganisation.OrganisationID) ON tblFunctions.FunctionID =3D
xrfPeopleToOrganisation.FunctionID"
If Request.Form("TypeSearch") =3D "PersonFirstName" Then
SqlJunk =3D SqlJunk & " WHERE PersonFirstName LIKE '%" & Request.Form
("DaInBox") & "%'"
End If
If Request.Form("TypeSearch") =3D "PersonLastName" Then
SqlJunk =3D SqlJunk & " WHERE PersonLastName LIKE '%" & _
Request.Form("DaInBox") & "%'"
End If
If Request.Form("TypeSearch") =3D "JobTitle" Then
SqlJunk =3D SqlJunk & " WHERE JobTitle LIKE '%" & _
Request.Form("DaInBox") & "%'"
End If
If Request.Form("TypeSearch") =3D "OrganisationName" Then
SqlJunk =3D SqlJunk & " WHERE OrganisationName LIKE '%" & _
Request.Form("DaInBox") & "%'"
End If
Set rsECBCon =3D Server.CreateObject("ADODB.Recordset")
rsECBCon.Open SqlJunk, ECBCon, 3
%>
<%
If rsECBCon.BOF and rsECBCon.EOF Then%>
<h2 align=3D"center"> <font color=3D"#0000FF"> We did not find a match!
</font></h2>
<%Else%>
<%If Not rsECBCon.BOF Then%>
<h2><font color=3D"#0000FF">Here are the results of your
search: </font></h2>
<table BORDER=3D"0" width=3D"1024">
<tr>
<th bgcolor=3D"blue"><font face=3D"Arial" color=3D"#FFFFFF">Full
Name
</font></th>
<th bgcolor=3D"blue"><font face=3D"Arial" color=3D"#FFFFFF">Job
Title
</font></th>
<th bgcolor=3D"blue"><font face=3D"Arial" color=3D"#FFFFFF">Function
</font></th>
<th bgcolor=3D"blue"><font face=3D"Arial"
color=3D"#FFFFFF">Organization</font></th>
<th bgcolor=3D"blue"><font face=3D"Arial" color=3D"#FFFFFF">Business
Address
</font></th>
<th bgcolor=3D"blue"><font face=3D"Arial"
color=3D"#FFFFFF">Telephone
</font></th>
<th bgcolor=3D"blue"><font face=3D"Arial" color=3D"#FFFFFF">Mobile
</font></th>
<th bgcolor=3D"blue"><font face=3D"Arial" color=3D"#FFFFFF">E-mail
</font></th>
</tr>
<%
Do While Not rsECBCon.EOF
%>
<tr>
<td width=3D"300"><%=3DrsECBCon("PersonFirstName")%>
<%=3DrsECBCon("PersonLastName")%>
</td>
<td width=3D"300"><%=3DrsECBCon("JobTitle")%>
</td>
<td width=3D"150"><%=3DrsECBCon("ColumnFunction")%>
</td>
<td><%=3DrsECBCon("OrganisationName")%>
</td>
<td width=3D"400"><%=3DrsECBCon("PersonAddress1")%>
<%=3DrsECBCon("PersonAddress2")%>
<%=3DrsECBCon("PersonAddress3")%>
<%=3DrsECBCon("PersonAddress4")%>
<%=3DrsECBCon("PersonPostCode")%>
</td>
<td><%=3DrsECBCon("PersonWorkPhone")%>
</td>
<td width=3D"200"><%=3DrsECBCon("PersonMobilePhone")%>
</td>
<td width=3D"200"><%=3DrsECBCon("WorkEmail")%>
</td>
<tr>
<% rsECBCon.MoveNext
Loop
%>
</table>
<%End If%>
<%End If%>
<%
rsECBCon.Close
ECBCon.Close
%>
<p> </p>
</body>
</html>
_____________________________________________________________________
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.
_____________________________________________________________________
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 #5 by "Peter Foti (PeterF)" <PeterF@S...> on Mon, 25 Nov 2002 10:48:10 -0500
|
|
Error #1:
ECBCon.Open "PROVIDER=SQLOLEDB;" & _
"DATA SOURCE=Sharepoint;DATABASE=pubs;" & _
"USER ID=sa;PASSWORD=;"
You can't have that empty line. You are using the line continuation, but
the line following it is blank. Change it to this:
ECBCon.Open "PROVIDER=SQLOLEDB;" & _
"DATA SOURCE=Sharepoint;DATABASE=pubs;" & _
"USER ID=sa;PASSWORD=;"
Error #2:
Your WHERE clauses do not handle single quotes within the values of the
TypeSearch input. So if TypeSearch contains a single quote, that will mess
up your query string. You can use a function like this:
Function sql_quote( str )
str = Replace( str, "'", "''" )
sql_quote = "'" & str & "'"
End Function
And then:
If Request.Form("TypeSearch") = "PersonFirstName" Then
tmpStr = "%" & Request.Form("DaInBox") & "%"
SqlJunk = SqlJunk & " WHERE PersonFirstName LIKE " & sql_quote(
tmpStr )
End If
Or you can just do the replace inline (but I prefer the function).
Hope this helps.
Regards,
Peter Foti
-----Original Message-----
From: neile [mailto:neile.bermudes@e...]
Sent: Monday, November 25, 2002 1:10 PM
To: ASP Databases
Subject: [asp_databases] ASP Script not working: with either SQL or
Access
Hi There,
I have an ASP script that seems to work perfectly with an
Access database
containing only one table. Now the database has been modified and has
several tables. My script no longer works, even though i know my SQL
syntax is correct as i've tested it in Query Analyzer. I will paste my
whole script below. If you could check my whole script i'd be grateful,
but the specific error i'm getting is "Microsoft VBScript Compilation
(0x800A0409)-- Unterminated String Constant"-- it refers to the line
reading: SQLQuery = "Select..."
Keep in mind that the database connection is now for SQL Server
as we are
moving to SQL now. But the script wont work in either ACCess or
SQL Server
at the moment. HEre it is:
<%@ LANGUAGE="VBSCRIPT" %>
<html>
<head>
<title>Directory Search Results</title>
</head>
<body>
<%
Dim ECBCon
Dim SqlJunk
Set ECBCon = Server.CreateObject("ADODB.Connection")
ECBCon.Open "PROVIDER=SQLOLEDB;" & _
"DATA SOURCE=Sharepoint;DATABASE=pubs;" & _
"USER ID=sa;PASSWORD=;"
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") = "PersonFirstName" Then
SqlJunk = SqlJunk & " WHERE PersonFirstName LIKE '%" &
Request.Form
("DaInBox") & "%'"
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>
Message #6 by "Craig Flannigan" <ckf@k...> on Mon, 25 Nov 2002 16:17:10 -0000
|
|
Most scripts fail if the user enters a ' into any text field.
This tells the SQL that you've come to the end of a particular field - even
if you've not. This then mucks up the rest of the SQL code.
To get around this, replace each instance of single quote, with a double.
<% strSurname = Replace(rs("Surname"),"'","''") %>
You'll probably need to do this for each field, so you're best to write it
into a function.
-----Original Message-----
From: Neile Bermudes [mailto:Neile.Bermudes@e...]
Sent: 25 November 2002 14:20
To: ASP Databases
Subject: [asp_databases] RE: ASP Script not working: with either SQL o r
Access
I've read and re-read my whole script.....there really doesn't seem like
there's any missing quotes.... Could there be something else that might be
causing this?
-----Original Message-----
From: Craig Flannigan [mailto:ckf@k...]
Sent: 25 November 2002 12:59
To: ASP Databases
Subject: [asp_databases] RE: ASP Script not working: with either SQL or
Access
It means you're missing a closing speech mark " or you've too many of them.
Check that for each " you open, that you have a closing one " too.
The common area for this to fail is when you drop in and out of SQL and ASP
Variables.
-----Original Message-----
From: neile [mailto:neile.bermudes@e...]
Sent: 25 November 2002 13:10
To: ASP Databases
Subject: [asp_databases] ASP Script not working: with either SQL or
Access
Hi There,
I have an ASP script that seems to work perfectly with an Access database
containing only one table. Now the database has been modified and has
several tables. My script no longer works, even though i know my SQL
syntax is correct as i've tested it in Query Analyzer. I will paste my
whole script below. If you could check my whole script i'd be grateful,
but the specific error i'm getting is "Microsoft VBScript Compilation
(0x800A0409)-- Unterminated String Constant"-- it refers to the line
reading: SQLQuery = "Select..."
Keep in mind that the database connection is now for SQL Server as we are
moving to SQL now. But the script wont work in either ACCess or SQL Server
at the moment. HEre it is:
<%@ LANGUAGE="VBSCRIPT" %>
<html>
<head>
<title>Directory Search Results</title>
</head>
<body>
<%
Dim ECBCon
Dim SqlJunk
Set ECBCon = Server.CreateObject("ADODB.Connection")
ECBCon.Open "PROVIDER=SQLOLEDB;" & _
"DATA SOURCE=Sharepoint;DATABASE=pubs;" & _
"USER ID=sa;PASSWORD=;"
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") = "PersonFirstName" Then
SqlJunk = SqlJunk & " WHERE PersonFirstName LIKE '%" & Request.Form
("DaInBox") & "%'"
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>
_____________________________________________________________________
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.
_____________________________________________________________________
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
