FP and connection to a SQL database

Guest · Sep 16, 2005

I am using FP2000 and connecting to a SQL2000 database/table on our LAN.
THis table is pretty hefty, it has 7.5m records.

I am connecting to the machine via ODBC, but the performance when doing a
keyword search using the QRY:

SELECT * FROM xxx WHERE
(abc LIKE '%::zzz::%')
OR (xyz LIKE '%::zzz::%')

is appauling. In fact I keep getting timeout messages similar to:

Microsoft OLE DB Provider for ODBC Drivers (0x80004005) [Microsoft][ODBC SQL
Server Driver]Timeout expired

How can I look up records in a large database with quickness with ASP?

THe table is indexed and is quickly retrievable by using Crystal Reports via
ODBC.

skc

Thomas A. Rowe · Sep 16, 2005

Only request the specific fields that you actually need, instead of all fields (*)

If using the FP generated ASP code, don't, instead learn to hand code.

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
==============================================
If you feel your current issue is a results of installing
a Service Pack or security update, please contact
Microsoft Product Support Services:
http://support.microsoft.com
If the problem can be shown to have been caused by a
security update, then there is usually no charge for the call.
==============================================

Guest · Sep 16, 2005

Can you give me a sample "hand coded" page to look up a parameterised search
if I have a form on the previous page passing a parameter called
'organisation' please.

Thanks.

Thomas A. Rowe said:
Only request the specific fields that you actually need, instead of all fields (*)

If using the FP generated ASP code, don't, instead learn to hand code.

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
==============================================
If you feel your current issue is a results of installing
a Service Pack or security update, please contact
Microsoft Product Support Services:
http://support.microsoft.com
If the problem can be shown to have been caused by a
security update, then there is usually no charge for the call.
==============================================

Skc said:

I am using FP2000 and connecting to a SQL2000 database/table on our LAN.
THis table is pretty hefty, it has 7.5m records.

I am connecting to the machine via ODBC, but the performance when doing a
keyword search using the QRY:

SELECT * FROM xxx WHERE
(abc LIKE '%::zzz::%')
OR (xyz LIKE '%::zzz::%')

is appauling. In fact I keep getting timeout messages similar to:

Microsoft OLE DB Provider for ODBC Drivers (0x80004005) [Microsoft][ODBC SQL
Server Driver]Timeout expired

How can I look up records in a large database with quickness with ASP?

THe table is indexed and is quickly retrievable by using Crystal Reports via
ODBC.

skc

Click to expand...

Thomas A. Rowe · Sep 16, 2005

You need to show me you current pages:

1. Submit Page
2. Query/Display Page

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
==============================================
If you feel your current issue is a results of installing
a Service Pack or security update, please contact
Microsoft Product Support Services:
http://support.microsoft.com
If the problem can be shown to have been caused by a
security update, then there is usually no charge for the call.
==============================================

Skc said:
Can you give me a sample "hand coded" page to look up a parameterised search
if I have a form on the previous page passing a parameter called
'organisation' please.

Thanks.

Thomas A. Rowe said:

Only request the specific fields that you actually need, instead of all fields (*)

If using the FP generated ASP code, don't, instead learn to hand code.

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
==============================================
If you feel your current issue is a results of installing
a Service Pack or security update, please contact
Microsoft Product Support Services:
http://support.microsoft.com
If the problem can be shown to have been caused by a
security update, then there is usually no charge for the call.
==============================================

Skc said:

I am using FP2000 and connecting to a SQL2000 database/table on our LAN.
THis table is pretty hefty, it has 7.5m records.

I am connecting to the machine via ODBC, but the performance when doing a
keyword search using the QRY:

SELECT * FROM xxx WHERE
(abc LIKE '%::zzz::%')
OR (xyz LIKE '%::zzz::%')

is appauling. In fact I keep getting timeout messages similar to:

Microsoft OLE DB Provider for ODBC Drivers (0x80004005) [Microsoft][ODBC SQL
Server Driver]Timeout expired

How can I look up records in a large database with quickness with ASP?

THe table is indexed and is quickly retrievable by using Crystal Reports via
ODBC.

skc

Click to expand...

Click to expand...

Guest · Sep 16, 2005

Submit Page:

<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta http-equiv="Content-Language" content="en-us">
<title>Home Page</title>
<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
</head>

<body>
<form method="POST" action="result.asp" onSubmit="">
Keyword: <input type="text" name="organisation" size="20"><input
type="submit" value="Submit" name="B1"><input type="reset" value="Reset"
name="B2">
</form>
</body>

</html>

Query Page (.asp)

<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>New Page 1</title>
</head>

<body>

<table width="100%" border="1">
<thead>
<tr>
<td>NUM</td>
<td>DST</td>
<td>STR</td>
<td>DDL</td>
<td>DLO</td>
<td>TWN</td>
<td>PCD</td>
<td>ORG</td>
<td>CTA</td>
<td>CTP</td>
<td>CTT</td>
</tr>
</thead>
<tbody>

<%
fp_sQry="SELECT * FROM PAF_7t_VIew_by_sachin where (ORG LIKE
'%::Organisation::%') OR (NUM LIKE '%::Organisation::%') OR (DST LIKE
'%::Organisation::%') OR (STR LIKE '%::Organisation::%') OR (DLO LIKE
'%::Organisation::%') OR (TWN LIKE '%::Organisation::%')
fp_sDefault="Organisation=&Organisation=&Organisation=&Organisation=&Organisation=&Organisation="
fp_sNoRecords="<tr><td colspan=11 align=left width=""100%"">No records
returned.</td></tr>"
fp_sDataConn="Database2"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=50
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice="NUM"
fp_sMenuValue="NUM"
fp_iDisplayCols=11
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>


<tr>
<td><%=FP_FieldVal(fp_rs,"NUM")%>
</td>
<td><%=FP_FieldVal(fp_rs,"DST")%>
</td>
<td><%=FP_FieldVal(fp_rs,"STR")%>
</td>
<td><%=FP_FieldVal(fp_rs,"DDL")%>
</td>
<td><%=FP_FieldVal(fp_rs,"DLO")%>
</td>
<td><%=FP_FieldVal(fp_rs,"TWN")%>
</td>
<td><%=FP_FieldVal(fp_rs,"PCD")%>
</td>
<td><%=FP_FieldVal(fp_rs,"ORG")%>
</td>
<td><%=FP_FieldVal(fp_rs,"CTA")%>
</td>
<td><%=FP_FieldVal(fp_rs,"CTP")%>
</td>
<td><%=FP_FieldVal(fp_rs,"CTT")%>
</td>
</tr>
<!--webbot bot="DatabaseRegionEnd" startspan b-tableformat="TRUE"
b-menuformat="FALSE" u-dbrgn2="_fpclass/fpdbrgn2.inc" i-groupsize="50"
clientside tag="TBODY"
local_preview="<tr><td colspan=64 bgcolor="#FFFF00" align="left"
width="100%">This is the end of a Database Results
region.</td></tr><TR><TD ALIGN=LEFT VALIGN=MIDDLE

COLSPAN=64> said:
| "> [1/50]</FORM></td></tr>"

preview="<tr><td colspan=64 bgcolor="#FFFF00" align="left"
width="100%">This is the end of a Database Results
region.</td></tr><TR><TD ALIGN=LEFT VALIGN=MIDDLE
COLSPAN=64><INPUT TYPE=Button VALUE=" |< "><INPUT TYPE=Button VALUE="
< "><INPUT TYPE=Button VALUE=" > "><INPUT TYPE=Button VALUE=" >| ">
[1/50] </td></tr>" -->

</tbody>
</table>

</body>

</html>

Global.asa file:

<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Sub Application_OnStart
'==FrontPage Generated - startspan==
Dim FrontPage_UrlVars(2)
'--Project Data Connection
Application("Database1_ConnectionString") = "DSN=ST2;DRIVER={SQL
Server};UID=user1;PWD=letmein"
Application("Database1_ConnectionTimeout") = 15
Application("Database1_CommandTimeout") = 30
Application("Database1_CursorLocation") = 3
Application("Database1_RuntimeUserName") = "user1"
Application("Database1_RuntimePassword") = "letmein"
'--Project Data Connection
Application("Database2_ConnectionString") = "DSN=ST;DRIVER={SQL
Server};UID=user1;PWD=letmein"
Application("Database2_ConnectionTimeout") = 15
Application("Database2_CommandTimeout") = 30
Application("Database2_CursorLocation") = 3
Application("Database2_RuntimeUserName") = "user1"
Application("Database2_RuntimePassword") = "letmein"
'--
Application("FrontPage_UrlVars") = FrontPage_UrlVars
'==FrontPage Generated - endspan==
End Sub
Sub Session_OnStart
FrontPage_StartSession '==FrontPage Generated==
End Sub
Sub FrontPage_StartSession
On Error Resume Next
if Len(Application("FrontPage_VRoot")) > 0 then Exit Sub
' discover the VRoot for the current page;
' walk back up VPath until we find global.asa
Vroot = Request.ServerVariables("PATH_INFO")
strG1 = "global.asa"
strG2 = "Global.asa"
iCount = 0
do while Len(Vroot) > 1
idx = InStrRev(Vroot, "/")
if idx > 0 then
Vroot = Left(Vroot,idx)
else
' error; assume root web
Vroot = "/"
end if
if FrontPage_FileExists(Server.MapPath(Vroot & strG1)) then exit do
if FrontPage_FileExists(Server.MapPath(Vroot & strG2)) then exit do
if Right(Vroot,1) = "/" then Vroot = Left(Vroot,Len(Vroot)-1)
iCount = iCount + 1
if iCount > 100 then
' error; assume root web
Vroot = "/"
exit do
end if
loop
' map all URL= attributes in _ConnectionString variables
Application.Lock
if Len(Application("FrontPage_VRoot")) = 0 then
Application("FrontPage_VRoot") = Vroot
UrlVarArray = Application("FrontPage_UrlVars")
for i = 0 to UBound(UrlVarArray)
if Len(UrlVarArray(i)) > 0 then FrontPage_MapUrl(UrlVarArray(i))
next
end if
Application.Unlock
End Sub
Sub FrontPage_MapUrl(AppVarName)
' convert URL attribute in conn string to absolute file location
strVal = Application(AppVarName)
strKey = "URL="
idxStart = InStr(strVal, strKey)
If idxStart = 0 Then Exit Sub
strBefore = Left(strVal, idxStart - 1)
idxStart = idxStart + Len(strKey)
idxEnd = InStr(idxStart, strVal, ";")
If idxEnd = 0 Then
strAfter = ""
strURL = Mid(strVal, idxStart)
Else
strAfter = ";" & Mid(strVal, idxEnd + 1)
strURL = Mid(strVal, idxStart, idxEnd - idxStart)
End If
strOut = strBefore & Server.MapPath(Application("FrontPage_VRoot") &
strURL) & strAfter
Application(AppVarName) = strOut
End Sub
Function FrontPage_FileExists(fspath)
On Error Resume Next
FrontPage_FileExists = False
set fs = CreateObject("Scripting.FileSystemObject")
Err.Clear
set istream = fs.OpenTextFile(fspath)
if Err.Number = 0 then
FrontPage_FileExists = True
istream.Close
end if
set istream = Nothing
set fs = Nothing
End Function
</SCRIPT>

Thomas A. Rowe said:
You need to show me you current pages:

1. Submit Page
2. Query/Display Page

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
==============================================
If you feel your current issue is a results of installing
a Service Pack or security update, please contact
Microsoft Product Support Services:
http://support.microsoft.com
If the problem can be shown to have been caused by a
security update, then there is usually no charge for the call.
==============================================

Skc said:

Can you give me a sample "hand coded" page to look up a parameterised search
if I have a form on the previous page passing a parameter called
'organisation' please.

Thanks.

Thomas A. Rowe said:

Only request the specific fields that you actually need, instead of all fields (*)

If using the FP generated ASP code, don't, instead learn to hand code.

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
==============================================
If you feel your current issue is a results of installing
a Service Pack or security update, please contact
Microsoft Product Support Services:
http://support.microsoft.com
If the problem can be shown to have been caused by a
security update, then there is usually no charge for the call.
==============================================

I am using FP2000 and connecting to a SQL2000 database/table on our LAN.
THis table is pretty hefty, it has 7.5m records.

I am connecting to the machine via ODBC, but the performance when doing a
keyword search using the QRY:

SELECT * FROM xxx WHERE
(abc LIKE '%::zzz::%')
OR (xyz LIKE '%::zzz::%')

is appauling. In fact I keep getting timeout messages similar to:

Microsoft OLE DB Provider for ODBC Drivers (0x80004005) [Microsoft][ODBC SQL
Server Driver]Timeout expired

How can I look up records in a large database with quickness with ASP?

THe table is indexed and is quickly retrievable by using Crystal Reports via
ODBC.

skc

Click to expand...

Click to expand...

Bob Lehmann · Sep 17, 2005

In addition to SELECT * adding overhead, the use of a leading wildcard in a
LIKE is a real performance killer.

Your indexes are not used as LIKE '%something" causes a full table scan to
find matches.

Also, the garbage "code" that the DBwhatever-they're-calling-it now wizard
thing doesn't exactly write the most efficient code to begin with.

Bob Lehmann

Skc said:
Can you give me a sample "hand coded" page to look up a parameterised search
if I have a form on the previous page passing a parameter called
'organisation' please.

Thanks.

Thomas A. Rowe said:

Only request the specific fields that you actually need, instead of all fields (*)

If using the FP generated ASP code, don't, instead learn to hand code.

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
==============================================
If you feel your current issue is a results of installing
a Service Pack or security update, please contact
Microsoft Product Support Services:
http://support.microsoft.com
If the problem can be shown to have been caused by a
security update, then there is usually no charge for the call.
==============================================

Skc said:

I am using FP2000 and connecting to a SQL2000 database/table on our LAN.
THis table is pretty hefty, it has 7.5m records.

I am connecting to the machine via ODBC, but the performance when doing a
keyword search using the QRY:

SELECT * FROM xxx WHERE
(abc LIKE '%::zzz::%')
OR (xyz LIKE '%::zzz::%')

is appauling. In fact I keep getting timeout messages similar to:

Microsoft OLE DB Provider for ODBC Drivers (0x80004005) [Microsoft][ODBC SQL
Server Driver]Timeout expired

How can I look up records in a large database with quickness with ASP?

THe table is indexed and is quickly retrievable by using Crystal Reports via
ODBC.

skc

Click to expand...

Click to expand...

Thomas A. Rowe · Sep 18, 2005

New Submit Page:

<html>

<head>
<title>Submit.asp</title>
<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
</head>

<body>
<form method="POST" action="QueryPage.asp">
Keyword: <input type="text" name="Organisation" size="20"><input
type="submit" value="Submit" name="B1">
</form>
</body>
</html>

New Query Page (.asp)
Note: I did not include any code to do record paging.

<html>

<head>
<title>QueryPage.asp</title>
<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
</head>

<body>
<%
Dim Find
Find = Cstr(Replace(Request("Organisation"),"'", "''"))

Dim DSN_Name
DSN_Name = Application("Database2_ConnectionString")
set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open DSN_Name

Set objRS = Server.CreateObject("ADODB.Recordset")
sql = "SELECT NUM, DST, STR, DDL, DLO, TWN, PCD, ORG, CTA, CTP, CTT FROM PAF_7t_VIew_by_sachin WHERE
((ORG LIKE

'%" & Find & "%') OR (NUM LIKE '%" & Find & "%') OR (DST LIKE '%" & Find & "%') OR (STR LIKE '%" &
Find & "%')

OR (DLO LIKE '%" & Find & "%') OR (TWN LIKE '%" & Find & "%')) ORDER BY ORG"
objRS.Open sql, Conn
%>
<table width="100%" border="1">
<tr>
<td>NUM</td>
<td>DST</td>
<td>STR</td>
<td>DDL</td>
<td>DLO</td>
<td>TWN</td>
<td>PCD</td>
<td>ORG</td>
<td>CTA</td>
<td>CTP</td>
<td>CTT</td>
</tr>
<% Do While Not objRS.EOF %>
<tr>
<td><%=objRS("NUM")%></td>
<td><%=objRS("DST")%></td>
<td><%=objRS("STR")%></td>
<td><%=objRS("DDL")%></td>
<td><%=objRS("DLO")%></td>
<td><%=objRS("TWN")%></td>
<td><%=objRS("PCD")%></td>
<td><%=objRS("ORG")%></td>
<td><%=objRS("CTA")%></td>
<td><%=objRS("CTP")%></td>
<td><%=objRS("CTT")%></td>
</tr>
<%
objRS.MoveNext
Loop
%>
</table>
<%
objRS.Close
Set objRS = Nothing
%>
</body>
</html>

No Change Global.asa file:

<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Sub Application_OnStart
'==FrontPage Generated - startspan==
Dim FrontPage_UrlVars(2)
'--Project Data Connection
Application("Database1_ConnectionString") = "DSN=ST2;DRIVER={SQL
Server};UID=user1;PWD=letmein"
Application("Database1_ConnectionTimeout") = 15
Application("Database1_CommandTimeout") = 30
Application("Database1_CursorLocation") = 3
Application("Database1_RuntimeUserName") = "user1"
Application("Database1_RuntimePassword") = "letmein"
'--Project Data Connection
Application("Database2_ConnectionString") = "DSN=ST;DRIVER={SQL
Server};UID=user1;PWD=letmein"
Application("Database2_ConnectionTimeout") = 15
Application("Database2_CommandTimeout") = 30
Application("Database2_CursorLocation") = 3
Application("Database2_RuntimeUserName") = "user1"
Application("Database2_RuntimePassword") = "letmein"
'--
Application("FrontPage_UrlVars") = FrontPage_UrlVars
'==FrontPage Generated - endspan==
End Sub
Sub Session_OnStart
FrontPage_StartSession '==FrontPage Generated==
End Sub
Sub FrontPage_StartSession
On Error Resume Next
if Len(Application("FrontPage_VRoot")) > 0 then Exit Sub
' discover the VRoot for the current page;
' walk back up VPath until we find global.asa
Vroot = Request.ServerVariables("PATH_INFO")
strG1 = "global.asa"
strG2 = "Global.asa"
iCount = 0
do while Len(Vroot) > 1
idx = InStrRev(Vroot, "/")
if idx > 0 then
Vroot = Left(Vroot,idx)
else
' error; assume root web
Vroot = "/"
end if
if FrontPage_FileExists(Server.MapPath(Vroot & strG1)) then exit do
if FrontPage_FileExists(Server.MapPath(Vroot & strG2)) then exit do
if Right(Vroot,1) = "/" then Vroot = Left(Vroot,Len(Vroot)-1)
iCount = iCount + 1
if iCount > 100 then
' error; assume root web
Vroot = "/"
exit do
end if
loop
' map all URL= attributes in _ConnectionString variables
Application.Lock
if Len(Application("FrontPage_VRoot")) = 0 then
Application("FrontPage_VRoot") = Vroot
UrlVarArray = Application("FrontPage_UrlVars")
for i = 0 to UBound(UrlVarArray)
if Len(UrlVarArray(i)) > 0 then FrontPage_MapUrl(UrlVarArray(i))
next
end if
Application.Unlock
End Sub
Sub FrontPage_MapUrl(AppVarName)
' convert URL attribute in conn string to absolute file location
strVal = Application(AppVarName)
strKey = "URL="
idxStart = InStr(strVal, strKey)
If idxStart = 0 Then Exit Sub
strBefore = Left(strVal, idxStart - 1)
idxStart = idxStart + Len(strKey)
idxEnd = InStr(idxStart, strVal, ";")
If idxEnd = 0 Then
strAfter = ""
strURL = Mid(strVal, idxStart)
Else
strAfter = ";" & Mid(strVal, idxEnd + 1)
strURL = Mid(strVal, idxStart, idxEnd - idxStart)
End If
strOut = strBefore & Server.MapPath(Application("FrontPage_VRoot") &
strURL) & strAfter
Application(AppVarName) = strOut
End Sub
Function FrontPage_FileExists(fspath)
On Error Resume Next
FrontPage_FileExists = False
set fs = CreateObject("Scripting.FileSystemObject")
Err.Clear
set istream = fs.OpenTextFile(fspath)
if Err.Number = 0 then
FrontPage_FileExists = True
istream.Close
end if
set istream = Nothing
set fs = Nothing
End Function
</SCRIPT>

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
==============================================
If you feel your current issue is a results of installing
a Service Pack or security update, please contact
Microsoft Product Support Services:
http://support.microsoft.com
If the problem can be shown to have been caused by a
security update, then there is usually no charge for the call.
==============================================

Skc said:
Submit Page:

<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta http-equiv="Content-Language" content="en-us">
<title>Home Page</title>
<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
</head>

<body>
<form method="POST" action="result.asp" onSubmit="">
Keyword: <input type="text" name="organisation" size="20"><input
type="submit" value="Submit" name="B1"><input type="reset" value="Reset"
name="B2">
</form>
</body>

</html>

Query Page (.asp)

<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>New Page 1</title>
</head>

<body>

<table width="100%" border="1">
<thead>
<tr>
<td>NUM</td>
<td>DST</td>
<td>STR</td>
<td>DDL</td>
<td>DLO</td>
<td>TWN</td>
<td>PCD</td>
<td>ORG</td>
<td>CTA</td>
<td>CTP</td>
<td>CTT</td>
</tr>
</thead>
<tbody>

<%
fp_sQry="SELECT * FROM PAF_7t_VIew_by_sachin where (ORG LIKE
'%::Organisation::%') OR (NUM LIKE '%::Organisation::%') OR (DST LIKE
'%::Organisation::%') OR (STR LIKE '%::Organisation::%') OR (DLO LIKE
'%::Organisation::%') OR (TWN LIKE '%::Organisation::%') "
fp_sDefault="Organisation=&Organisation=&Organisation=&Organisation=&Organisation=&Organisation="
fp_sNoRecords="<tr><td colspan=11 align=left width=""100%"">No records
returned.</td></tr>"
fp_sDataConn="Database2"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=50
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice="NUM"
fp_sMenuValue="NUM"
fp_iDisplayCols=11
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>


<tr>
<td><%=FP_FieldVal(fp_rs,"NUM")%>
</td>
<td><%=FP_FieldVal(fp_rs,"DST")%>
</td>
<td><%=FP_FieldVal(fp_rs,"STR")%>
</td>
<td><%=FP_FieldVal(fp_rs,"DDL")%>
</td>
<td><%=FP_FieldVal(fp_rs,"DLO")%>
</td>
<td><%=FP_FieldVal(fp_rs,"TWN")%>
</td>
<td><%=FP_FieldVal(fp_rs,"PCD")%>
</td>
<td><%=FP_FieldVal(fp_rs,"ORG")%>
</td>
<td><%=FP_FieldVal(fp_rs,"CTA")%>
</td>
<td><%=FP_FieldVal(fp_rs,"CTP")%>
</td>
<td><%=FP_FieldVal(fp_rs,"CTT")%>
</td>
</tr>


</tbody>
</table>

</body>

</html>

Global.asa file:

<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Sub Application_OnStart
'==FrontPage Generated - startspan==
Dim FrontPage_UrlVars(2)
'--Project Data Connection
Application("Database1_ConnectionString") = "DSN=ST2;DRIVER={SQL
Server};UID=user1;PWD=letmein"
Application("Database1_ConnectionTimeout") = 15
Application("Database1_CommandTimeout") = 30
Application("Database1_CursorLocation") = 3
Application("Database1_RuntimeUserName") = "user1"
Application("Database1_RuntimePassword") = "letmein"
'--Project Data Connection
Application("Database2_ConnectionString") = "DSN=ST;DRIVER={SQL
Server};UID=user1;PWD=letmein"
Application("Database2_ConnectionTimeout") = 15
Application("Database2_CommandTimeout") = 30
Application("Database2_CursorLocation") = 3
Application("Database2_RuntimeUserName") = "user1"
Application("Database2_RuntimePassword") = "letmein"
'--
Application("FrontPage_UrlVars") = FrontPage_UrlVars
'==FrontPage Generated - endspan==
End Sub
Sub Session_OnStart
FrontPage_StartSession '==FrontPage Generated==
End Sub
Sub FrontPage_StartSession
On Error Resume Next
if Len(Application("FrontPage_VRoot")) > 0 then Exit Sub
' discover the VRoot for the current page;
' walk back up VPath until we find global.asa
Vroot = Request.ServerVariables("PATH_INFO")
strG1 = "global.asa"
strG2 = "Global.asa"
iCount = 0
do while Len(Vroot) > 1
idx = InStrRev(Vroot, "/")
if idx > 0 then
Vroot = Left(Vroot,idx)
else
' error; assume root web
Vroot = "/"
end if
if FrontPage_FileExists(Server.MapPath(Vroot & strG1)) then exit do
if FrontPage_FileExists(Server.MapPath(Vroot & strG2)) then exit do
if Right(Vroot,1) = "/" then Vroot = Left(Vroot,Len(Vroot)-1)
iCount = iCount + 1
if iCount > 100 then
' error; assume root web
Vroot = "/"
exit do
end if
loop
' map all URL= attributes in _ConnectionString variables
Application.Lock
if Len(Application("FrontPage_VRoot")) = 0 then
Application("FrontPage_VRoot") = Vroot
UrlVarArray = Application("FrontPage_UrlVars")
for i = 0 to UBound(UrlVarArray)
if Len(UrlVarArray(i)) > 0 then FrontPage_MapUrl(UrlVarArray(i))
next
end if
Application.Unlock
End Sub
Sub FrontPage_MapUrl(AppVarName)
' convert URL attribute in conn string to absolute file location
strVal = Application(AppVarName)
strKey = "URL="
idxStart = InStr(strVal, strKey)
If idxStart = 0 Then Exit Sub
strBefore = Left(strVal, idxStart - 1)
idxStart = idxStart + Len(strKey)
idxEnd = InStr(idxStart, strVal, ";")
If idxEnd = 0 Then
strAfter = ""
strURL = Mid(strVal, idxStart)
Else
strAfter = ";" & Mid(strVal, idxEnd + 1)
strURL = Mid(strVal, idxStart, idxEnd - idxStart)
End If
strOut = strBefore & Server.MapPath(Application("FrontPage_VRoot") &
strURL) & strAfter
Application(AppVarName) = strOut
End Sub
Function FrontPage_FileExists(fspath)
On Error Resume Next
FrontPage_FileExists = False
set fs = CreateObject("Scripting.FileSystemObject")
Err.Clear
set istream = fs.OpenTextFile(fspath)
if Err.Number = 0 then
FrontPage_FileExists = True
istream.Close
end if
set istream = Nothing
set fs = Nothing
End Function
</SCRIPT>

Thomas A. Rowe said:

You need to show me you current pages:

1. Submit Page
2. Query/Display Page

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
==============================================
If you feel your current issue is a results of installing
a Service Pack or security update, please contact
Microsoft Product Support Services:
http://support.microsoft.com
If the problem can be shown to have been caused by a
security update, then there is usually no charge for the call.
==============================================

Skc said:

Can you give me a sample "hand coded" page to look up a parameterised search
if I have a form on the previous page passing a parameter called
'organisation' please.

Thanks.

:

Only request the specific fields that you actually need, instead of all fields (*)

If using the FP generated ASP code, don't, instead learn to hand code.

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
==============================================
If you feel your current issue is a results of installing
a Service Pack or security update, please contact
Microsoft Product Support Services:
http://support.microsoft.com
If the problem can be shown to have been caused by a
security update, then there is usually no charge for the call.
==============================================

I am using FP2000 and connecting to a SQL2000 database/table on our LAN.
THis table is pretty hefty, it has 7.5m records.

I am connecting to the machine via ODBC, but the performance when doing a
keyword search using the QRY:

SELECT * FROM xxx WHERE
(abc LIKE '%::zzz::%')
OR (xyz LIKE '%::zzz::%')

is appauling. In fact I keep getting timeout messages similar to:

Microsoft OLE DB Provider for ODBC Drivers (0x80004005) [Microsoft][ODBC SQL
Server Driver]Timeout expired

How can I look up records in a large database with quickness with ASP?

THe table is indexed and is quickly retrievable by using Crystal Reports via
ODBC.

skc

Click to expand...

Click to expand...

Guest · Sep 19, 2005

Thomas,

I do not know what you mean by "include any code to do record paging"?

Furthermore, on the querypage.asp I am getting:

Error Type:
Microsoft VBScript compilation (0x800A0409)
Unterminated string constant
/st/QueryPage.asp, line 20, column 100
sql = "SELECT NUM, DST, STR, DDL, DLO, TWN, PCD, ORG, CTA, CTP, CTT FROM
PAF_7t_VIew_by_sachin WHERE
---------------------------------------------------------------------------------------------------^

skc

Guest · Sep 19, 2005

Thomas,

I got it to work - there was a little typo in there.

Anyway, I am now getting:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E31)
[Microsoft][ODBC SQL Server Driver]Timeout expired
/st/QueryPage.asp, line 21

It seems that the lookup takes around 20-30seconds and thus times out. How
can I improve performance?

skc

Guest · Sep 19, 2005

Bob,

What is your suggestion then?

skc

Bob Lehmann said:
In addition to SELECT * adding overhead, the use of a leading wildcard in a
LIKE is a real performance killer.

Your indexes are not used as LIKE '%something" causes a full table scan to
find matches.

Also, the garbage "code" that the DBwhatever-they're-calling-it now wizard
thing doesn't exactly write the most efficient code to begin with.

Bob Lehmann

Skc said:

Can you give me a sample "hand coded" page to look up a parameterised search
if I have a form on the previous page passing a parameter called
'organisation' please.

Thanks.

Thomas A. Rowe said:

Only request the specific fields that you actually need, instead of all fields (*)

If using the FP generated ASP code, don't, instead learn to hand code.

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
==============================================
If you feel your current issue is a results of installing
a Service Pack or security update, please contact
Microsoft Product Support Services:
http://support.microsoft.com
If the problem can be shown to have been caused by a
security update, then there is usually no charge for the call.
==============================================

I am using FP2000 and connecting to a SQL2000 database/table on our LAN.
THis table is pretty hefty, it has 7.5m records.

I am connecting to the machine via ODBC, but the performance when doing a
keyword search using the QRY:

SELECT * FROM xxx WHERE
(abc LIKE '%::zzz::%')
OR (xyz LIKE '%::zzz::%')

is appauling. In fact I keep getting timeout messages similar to:

Microsoft OLE DB Provider for ODBC Drivers (0x80004005) [Microsoft][ODBC SQL
Server Driver]Timeout expired

How can I look up records in a large database with quickness with ASP?

THe table is indexed and is quickly retrievable by using Crystal Reports via
ODBC.

skc

Click to expand...

Click to expand...

Thomas A. Rowe · Sep 19, 2005

Skc said:
Thomas,

I do not know what you mean by "include any code to do record paging"?

Means the results will appear on a single page with no option split the records on multiple pages.

Thomas A. Rowe · Sep 19, 2005

You will need to reduce the number of fields being returned from the query or limit the number of
results.

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
==============================================
If you feel your current issue is a results of installing
a Service Pack or security update, please contact
Microsoft Product Support Services:
http://support.microsoft.com
If the problem can be shown to have been caused by a
security update, then there is usually no charge for the call.
==============================================

Skc said:
Thomas,

I got it to work - there was a little typo in there.

Anyway, I am now getting:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E31)
[Microsoft][ODBC SQL Server Driver]Timeout expired
/st/QueryPage.asp, line 21

It seems that the lookup takes around 20-30seconds and thus times out. How
can I improve performance?

skc

Skc said:

Thomas,

I do not know what you mean by "include any code to do record paging"?

Furthermore, on the querypage.asp I am getting:

Error Type:
Microsoft VBScript compilation (0x800A0409)
Unterminated string constant
/st/QueryPage.asp, line 20, column 100
sql = "SELECT NUM, DST, STR, DDL, DLO, TWN, PCD, ORG, CTA, CTP, CTT FROM
PAF_7t_VIew_by_sachin WHERE
---------------------------------------------------------------------------------------------------^

skc

Click to expand...

Stefan B Rusynko · Sep 19, 2005

Why are you using a Like search on All the fields (ORG, NUM, DST, STR, DLO, TWN) when you are just looking for an Organization
(presumably just in the ORG field, or maybe in 1 other field too)?
- If the Organization name is really in all those multiple fields, the DB is probably designed poorly

If they are all (or mostly) Memo fields that's a "killer" search
- A foolish user search for say "a" will get try to get every "a" in every field

--

| Thomas,
|
| I got it to work - there was a little typo in there.
|
| Anyway, I am now getting:
|
|
| Error Type:
| Microsoft OLE DB Provider for ODBC Drivers (0x80040E31)
| [Microsoft][ODBC SQL Server Driver]Timeout expired
| /st/QueryPage.asp, line 21
|
| It seems that the lookup takes around 20-30seconds and thus times out. How
| can I improve performance?
|
| skc
|
|
| "Skc" wrote:
|
| > Thomas,
| >
| > I do not know what you mean by "include any code to do record paging"?
| >
| > Furthermore, on the querypage.asp I am getting:
| >
| > Error Type:
| > Microsoft VBScript compilation (0x800A0409)
| > Unterminated string constant
| > /st/QueryPage.asp, line 20, column 100
| > sql = "SELECT NUM, DST, STR, DDL, DLO, TWN, PCD, ORG, CTA, CTP, CTT FROM
| > PAF_7t_VIew_by_sachin WHERE
| > ---------------------------------------------------------------------------------------------------^
| >
| > skc
| >
| > "Thomas A. Rowe" wrote:
| >
| > > New Submit Page:
| > >
| > > <html>
| > >
| > > <head>
| > > <title>Submit.asp</title>
| > > <meta name="GENERATOR" content="Microsoft FrontPage 4.0">
| > > <meta name="ProgId" content="FrontPage.Editor.Document">
| > > </head>
| > >
| > > <body>
| > > <form method="POST" action="QueryPage.asp">
| > > Keyword: <input type="text" name="Organisation" size="20"><input
| > > type="submit" value="Submit" name="B1">
| > > </form>
| > > </body>
| > > </html>
| > >
| > >
| > > New Query Page (.asp)
| > > Note: I did not include any code to do record paging.
| > >
| > > <html>
| > >
| > > <head>
| > > <title>QueryPage.asp</title>
| > > <meta name="GENERATOR" content="Microsoft FrontPage 4.0">
| > > <meta name="ProgId" content="FrontPage.Editor.Document">
| > > </head>
| > >
| > > <body>
| > > <%
| > > Dim Find
| > > Find = Cstr(Replace(Request("Organisation"),"'", "''"))
| > >
| > > Dim DSN_Name
| > > DSN_Name = Application("Database2_ConnectionString")
| > > set Conn = Server.CreateObject("ADODB.Connection")
| > > Conn.Open DSN_Name
| > >
| > > Set objRS = Server.CreateObject("ADODB.Recordset")
| > > sql = "SELECT NUM, DST, STR, DDL, DLO, TWN, PCD, ORG, CTA, CTP, CTT FROM PAF_7t_VIew_by_sachin WHERE
| > > ((ORG LIKE
| > >
| > > '%" & Find & "%') OR (NUM LIKE '%" & Find & "%') OR (DST LIKE '%" & Find & "%') OR (STR LIKE '%" &
| > > Find & "%')
| > >
| > > OR (DLO LIKE '%" & Find & "%') OR (TWN LIKE '%" & Find & "%')) ORDER BY ORG"
| > > objRS.Open sql, Conn
| > > %>
| > > <table width="100%" border="1">
| > > <tr>
| > > <td>NUM</td>
| > > <td>DST</td>
| > > <td>STR</td>
| > > <td>DDL</td>
| > > <td>DLO</td>
| > > <td>TWN</td>
| > > <td>PCD</td>
| > > <td>ORG</td>
| > > <td>CTA</td>
| > > <td>CTP</td>
| > > <td>CTT</td>
| > > </tr>
| > > <% Do While Not objRS.EOF %>
| > > <tr>
| > > <td><%=objRS("NUM")%></td>
| > > <td><%=objRS("DST")%></td>
| > > <td><%=objRS("STR")%></td>
| > > <td><%=objRS("DDL")%></td>
| > > <td><%=objRS("DLO")%></td>
| > > <td><%=objRS("TWN")%></td>
| > > <td><%=objRS("PCD")%></td>
| > > <td><%=objRS("ORG")%></td>
| > > <td><%=objRS("CTA")%></td>
| > > <td><%=objRS("CTP")%></td>
| > > <td><%=objRS("CTT")%></td>
| > > </tr>
| > > <%
| > > objRS.MoveNext
| > > Loop
| > > %>
| > > </table>
| > > <%
| > > objRS.Close
| > > Set objRS = Nothing
| > > %>
| > > </body>
| > > </html>
| > >
| > >
| > > No Change Global.asa file:
| > >
| > > <SCRIPT LANGUAGE=VBScript RUNAT=Server>
| > > Sub Application_OnStart
| > > '==FrontPage Generated - startspan==
| > > Dim FrontPage_UrlVars(2)
| > > '--Project Data Connection
| > > Application("Database1_ConnectionString") = "DSN=ST2;DRIVER={SQL
| > > Server};UID=user1;PWD=letmein"
| > > Application("Database1_ConnectionTimeout") = 15
| > > Application("Database1_CommandTimeout") = 30
| > > Application("Database1_CursorLocation") = 3
| > > Application("Database1_RuntimeUserName") = "user1"
| > > Application("Database1_RuntimePassword") = "letmein"
| > > '--Project Data Connection
| > > Application("Database2_ConnectionString") = "DSN=ST;DRIVER={SQL
| > > Server};UID=user1;PWD=letmein"
| > > Application("Database2_ConnectionTimeout") = 15
| > > Application("Database2_CommandTimeout") = 30
| > > Application("Database2_CursorLocation") = 3
| > > Application("Database2_RuntimeUserName") = "user1"
| > > Application("Database2_RuntimePassword") = "letmein"
| > > '--
| > > Application("FrontPage_UrlVars") = FrontPage_UrlVars
| > > '==FrontPage Generated - endspan==
| > > End Sub
| > > Sub Session_OnStart
| > > FrontPage_StartSession '==FrontPage Generated==
| > > End Sub
| > > Sub FrontPage_StartSession
| > > On Error Resume Next
| > > if Len(Application("FrontPage_VRoot")) > 0 then Exit Sub
| > > ' discover the VRoot for the current page;
| > > ' walk back up VPath until we find global.asa
| > > Vroot = Request.ServerVariables("PATH_INFO")
| > > strG1 = "global.asa"
| > > strG2 = "Global.asa"
| > > iCount = 0
| > > do while Len(Vroot) > 1
| > > idx = InStrRev(Vroot, "/")
| > > if idx > 0 then
| > > Vroot = Left(Vroot,idx)
| > > else
| > > ' error; assume root web
| > > Vroot = "/"
| > > end if
| > > if FrontPage_FileExists(Server.MapPath(Vroot & strG1)) then exit do
| > > if FrontPage_FileExists(Server.MapPath(Vroot & strG2)) then exit do
| > > if Right(Vroot,1) = "/" then Vroot = Left(Vroot,Len(Vroot)-1)
| > > iCount = iCount + 1
| > > if iCount > 100 then
| > > ' error; assume root web
| > > Vroot = "/"
| > > exit do
| > > end if
| > > loop
| > > ' map all URL= attributes in _ConnectionString variables
| > > Application.Lock
| > > if Len(Application("FrontPage_VRoot")) = 0 then
| > > Application("FrontPage_VRoot") = Vroot
| > > UrlVarArray = Application("FrontPage_UrlVars")
| > > for i = 0 to UBound(UrlVarArray)
| > > if Len(UrlVarArray(i)) > 0 then FrontPage_MapUrl(UrlVarArray(i))
| > > next
| > > end if
| > > Application.Unlock
| > > End Sub
| > > Sub FrontPage_MapUrl(AppVarName)
| > > ' convert URL attribute in conn string to absolute file location
| > > strVal = Application(AppVarName)
| > > strKey = "URL="
| > > idxStart = InStr(strVal, strKey)
| > > If idxStart = 0 Then Exit Sub
| > > strBefore = Left(strVal, idxStart - 1)
| > > idxStart = idxStart + Len(strKey)
| > > idxEnd = InStr(idxStart, strVal, ";")
| > > If idxEnd = 0 Then
| > > strAfter = ""
| > > strURL = Mid(strVal, idxStart)
| > > Else
| > > strAfter = ";" & Mid(strVal, idxEnd + 1)
| > > strURL = Mid(strVal, idxStart, idxEnd - idxStart)
| > > End If
| > > strOut = strBefore & Server.MapPath(Application("FrontPage_VRoot") &
| > > strURL) & strAfter
| > > Application(AppVarName) = strOut
| > > End Sub
| > > Function FrontPage_FileExists(fspath)
| > > On Error Resume Next
| > > FrontPage_FileExists = False
| > > set fs = CreateObject("Scripting.FileSystemObject")
| > > Err.Clear
| > > set istream = fs.OpenTextFile(fspath)
| > > if Err.Number = 0 then
| > > FrontPage_FileExists = True
| > > istream.Close
| > > end if
| > > set istream = Nothing
| > > set fs = Nothing
| > > End Function
| > > </SCRIPT>
| > >
| > > --
| > > ==============================================
| > > Thomas A. Rowe (Microsoft MVP - FrontPage)
| > > ==============================================
| > > If you feel your current issue is a results of installing
| > > a Service Pack or security update, please contact
| > > Microsoft Product Support Services:
| > > http://support.microsoft.com
| > > If the problem can be shown to have been caused by a
| > > security update, then there is usually no charge for the call.
| > > ==============================================
| > >
| > > | > > > Submit Page:
| > > >
| > > > <html>
| > > >
| > > > <head>
| > > > <meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
| > > > <meta http-equiv="Content-Language" content="en-us">
| > > > <title>Home Page</title>
| > > > <meta name="GENERATOR" content="Microsoft FrontPage 4.0">
| > > > <meta name="ProgId" content="FrontPage.Editor.Document">
| > > > </head>
| > > >
| > > > <body>
| > > > <form method="POST" action="result.asp" onSubmit="">
| > > > Keyword: <input type="text" name="organisation" size="20"><input
| > > > type="submit" value="Submit" name="B1"><input type="reset" value="Reset"
| > > > name="B2">
| > > > </form>
| > > > </body>
| > > >
| > > > </html>
| > > >
| > > >
| > > > Query Page (.asp)
| > > >
| > > > <html>
| > > >
| > > > <head>
| > > > <meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
| > > > <meta name="GENERATOR" content="Microsoft FrontPage 4.0">
| > > > <meta name="ProgId" content="FrontPage.Editor.Document">
| > > > <title>New Page 1</title>
| > > > </head>
| > > >
| > > > <body>
| > > >
| > > > <table width="100%" border="1">
| > > > <thead>
| > > > <tr>
| > > > <td>NUM</td>
| > > > <td>DST</td>
| > > > <td>STR</td>
| > > > <td>DDL</td>
| > > > <td>DLO</td>
| > > > <td>TWN</td>
| > > > <td>PCD</td>
| > > > <td>ORG</td>
| > > > <td>CTA</td>
| > > > <td>CTP</td>
| > > > <td>CTT</td>
| > > > </tr>
| > > > </thead>
| > > > <tbody>
| > > > <!--webbot bot="DatabaseRegionStart" startspan
| > > > s-columnnames="NUM,DST,STR,DDL,DLO,TWN,PCD,ORG,CTA,CTP,CTT"
| > > > s-columntypes="200,200,200,200,200,200,200,200,200,200,200"
| > > > s-dataconnection="Database2" b-tableformat="TRUE" b-menuformat="FALSE"
| > > > s-menuchoice="NUM" s-menuvalue="NUM" b-tableborder="TRUE"
| > > > b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="TRUE"
| > > > b-listseparator="TRUE" i-ListFormat="0" b-makeform="FALSE" s-recordsource
| > > > s-displaycolumns="NUM,DST,STR,DDL,DLO,TWN,PCD,ORG,CTA,CTP,CTT" s-criteria
| > > > s-order
| > > > s-sql="SELECT * FROM PAF_7t_VIew_by_sachin where (ORG LIKE
| > > > '%::Organisation::%') OR (NUM LIKE '%::Organisation::%') OR (DST
| > > > LIKE '%::Organisation::%') OR (STR LIKE '%::Organisation::%') OR (DLO
| > > > LIKE '%::Organisation::%') OR (TWN LIKE '%::Organisation::%') "
| > > > b-procedure="FALSE" clientside SuggestedExt="asp"
| > > >
| > > > s-DefaultFields="Organisation=&Organisation=&Organisation=&Organisation=&Organisation=&Organisation="
| > > > s-NoRecordsFound="No records returned." i-MaxRecords="0" i-GroupSize="50"
| > > > BOTID="0" u-dblib="_fpclass/fpdblib.inc" u-dbrgn1="_fpclass/fpdbrgn1.inc"
| > > > u-dbrgn2="_fpclass/fpdbrgn2.inc" tag="TBODY"
| > > > local_preview="<tr><td colspan=64 bgcolor="#FFFF00" align="left"
| > > > width="100%">Database Results regions will not preview
| > > > unless this page is fetched from a Web server with a web browser. The
| > > > following table row will repeat once for every record returned by the

Thomas A. Rowe · Sep 19, 2005

The input field is name organization, but a user could enter the org name, or the org number, etc.
the way SKC has this written.

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
==============================================
If you feel your current issue is a results of installing
a Service Pack or security update, please contact
Microsoft Product Support Services:
http://support.microsoft.com
If the problem can be shown to have been caused by a
security update, then there is usually no charge for the call.
==============================================

Stefan B Rusynko said:
Why are you using a Like search on All the fields (ORG, NUM, DST, STR, DLO, TWN) when you are just
looking for an Organization
(presumably just in the ORG field, or maybe in 1 other field too)?
- If the Organization name is really in all those multiple fields, the DB is probably designed
poorly

If they are all (or mostly) Memo fields that's a "killer" search
- A foolish user search for say "a" will get try to get every "a" in every field

--

| Thomas,
|
| I got it to work - there was a little typo in there.
|
| Anyway, I am now getting:
|
|
| Error Type:
| Microsoft OLE DB Provider for ODBC Drivers (0x80040E31)
| [Microsoft][ODBC SQL Server Driver]Timeout expired
| /st/QueryPage.asp, line 21
|
| It seems that the lookup takes around 20-30seconds and thus times out. How
| can I improve performance?
|
| skc
|
|
| "Skc" wrote:
|
| > Thomas,
| >
| > I do not know what you mean by "include any code to do record paging"?
| >
| > Furthermore, on the querypage.asp I am getting:
| >
| > Error Type:
| > Microsoft VBScript compilation (0x800A0409)
| > Unterminated string constant
| > /st/QueryPage.asp, line 20, column 100
| > sql = "SELECT NUM, DST, STR, DDL, DLO, TWN, PCD, ORG, CTA, CTP, CTT FROM
| > PAF_7t_VIew_by_sachin WHERE
|

---------------------------------------------------------------------------------------------------^

Click to expand...

| >
| > skc
| >
| > "Thomas A. Rowe" wrote:
| >
| > > New Submit Page:
| > >
| > > <html>
| > >
| > > <head>
| > > <title>Submit.asp</title>
| > > <meta name="GENERATOR" content="Microsoft FrontPage 4.0">
| > > <meta name="ProgId" content="FrontPage.Editor.Document">
| > > </head>
| > >
| > > <body>
| > > <form method="POST" action="QueryPage.asp">
| > > Keyword: <input type="text" name="Organisation" size="20"><input
| > > type="submit" value="Submit" name="B1">
| > > </form>
| > > </body>
| > > </html>
| > >
| > >
| > > New Query Page (.asp)
| > > Note: I did not include any code to do record paging.
| > >
| > > <html>
| > >
| > > <head>
| > > <title>QueryPage.asp</title>
| > > <meta name="GENERATOR" content="Microsoft FrontPage 4.0">
| > > <meta name="ProgId" content="FrontPage.Editor.Document">
| > > </head>
| > >
| > > <body>
| > > <%
| > > Dim Find
| > > Find = Cstr(Replace(Request("Organisation"),"'", "''"))
| > >
| > > Dim DSN_Name
| > > DSN_Name = Application("Database2_ConnectionString")
| > > set Conn = Server.CreateObject("ADODB.Connection")
| > > Conn.Open DSN_Name
| > >
| > > Set objRS = Server.CreateObject("ADODB.Recordset")
| > > sql = "SELECT NUM, DST, STR, DDL, DLO, TWN, PCD, ORG, CTA, CTP, CTT FROM
PAF_7t_VIew_by_sachin WHERE
| > > ((ORG LIKE
| > >
| > > '%" & Find & "%') OR (NUM LIKE '%" & Find & "%') OR (DST LIKE '%" & Find & "%') OR (STR LIKE
'%" &
| > > Find & "%')
| > >
| > > OR (DLO LIKE '%" & Find & "%') OR (TWN LIKE '%" & Find & "%')) ORDER BY ORG"
| > > objRS.Open sql, Conn
| > > %>
| > > <table width="100%" border="1">
| > > <tr>
| > > <td>NUM</td>
| > > <td>DST</td>
| > > <td>STR</td>
| > > <td>DDL</td>
| > > <td>DLO</td>
| > > <td>TWN</td>
| > > <td>PCD</td>
| > > <td>ORG</td>
| > > <td>CTA</td>
| > > <td>CTP</td>
| > > <td>CTT</td>
| > > </tr>
| > > <% Do While Not objRS.EOF %>
| > > <tr>
| > > <td><%=objRS("NUM")%></td>
| > > <td><%=objRS("DST")%></td>
| > > <td><%=objRS("STR")%></td>
| > > <td><%=objRS("DDL")%></td>
| > > <td><%=objRS("DLO")%></td>
| > > <td><%=objRS("TWN")%></td>
| > > <td><%=objRS("PCD")%></td>
| > > <td><%=objRS("ORG")%></td>
| > > <td><%=objRS("CTA")%></td>
| > > <td><%=objRS("CTP")%></td>
| > > <td><%=objRS("CTT")%></td>
| > > </tr>
| > > <%
| > > objRS.MoveNext
| > > Loop
| > > %>
| > > </table>
| > > <%
| > > objRS.Close
| > > Set objRS = Nothing
| > > %>
| > > </body>
| > > </html>
| > >
| > >
| > > No Change Global.asa file:
| > >
| > > <SCRIPT LANGUAGE=VBScript RUNAT=Server>
| > > Sub Application_OnStart
| > > '==FrontPage Generated - startspan==
| > > Dim FrontPage_UrlVars(2)
| > > '--Project Data Connection
| > > Application("Database1_ConnectionString") = "DSN=ST2;DRIVER={SQL
| > > Server};UID=user1;PWD=letmein"
| > > Application("Database1_ConnectionTimeout") = 15
| > > Application("Database1_CommandTimeout") = 30
| > > Application("Database1_CursorLocation") = 3
| > > Application("Database1_RuntimeUserName") = "user1"
| > > Application("Database1_RuntimePassword") = "letmein"
| > > '--Project Data Connection
| > > Application("Database2_ConnectionString") = "DSN=ST;DRIVER={SQL
| > > Server};UID=user1;PWD=letmein"
| > > Application("Database2_ConnectionTimeout") = 15
| > > Application("Database2_CommandTimeout") = 30
| > > Application("Database2_CursorLocation") = 3
| > > Application("Database2_RuntimeUserName") = "user1"
| > > Application("Database2_RuntimePassword") = "letmein"
| > > '--
| > > Application("FrontPage_UrlVars") = FrontPage_UrlVars
| > > '==FrontPage Generated - endspan==
| > > End Sub
| > > Sub Session_OnStart
| > > FrontPage_StartSession '==FrontPage Generated==
| > > End Sub
| > > Sub FrontPage_StartSession
| > > On Error Resume Next
| > > if Len(Application("FrontPage_VRoot")) > 0 then Exit Sub
| > > ' discover the VRoot for the current page;
| > > ' walk back up VPath until we find global.asa
| > > Vroot = Request.ServerVariables("PATH_INFO")
| > > strG1 = "global.asa"
| > > strG2 = "Global.asa"
| > > iCount = 0
| > > do while Len(Vroot) > 1
| > > idx = InStrRev(Vroot, "/")
| > > if idx > 0 then
| > > Vroot = Left(Vroot,idx)
| > > else
| > > ' error; assume root web
| > > Vroot = "/"
| > > end if
| > > if FrontPage_FileExists(Server.MapPath(Vroot & strG1)) then exit do
| > > if FrontPage_FileExists(Server.MapPath(Vroot & strG2)) then exit do
| > > if Right(Vroot,1) = "/" then Vroot = Left(Vroot,Len(Vroot)-1)
| > > iCount = iCount + 1
| > > if iCount > 100 then
| > > ' error; assume root web
| > > Vroot = "/"
| > > exit do
| > > end if
| > > loop
| > > ' map all URL= attributes in _ConnectionString variables
| > > Application.Lock
| > > if Len(Application("FrontPage_VRoot")) = 0 then
| > > Application("FrontPage_VRoot") = Vroot
| > > UrlVarArray = Application("FrontPage_UrlVars")
| > > for i = 0 to UBound(UrlVarArray)
| > > if Len(UrlVarArray(i)) > 0 then FrontPage_MapUrl(UrlVarArray(i))
| > > next
| > > end if
| > > Application.Unlock
| > > End Sub
| > > Sub FrontPage_MapUrl(AppVarName)
| > > ' convert URL attribute in conn string to absolute file location
| > > strVal = Application(AppVarName)
| > > strKey = "URL="
| > > idxStart = InStr(strVal, strKey)
| > > If idxStart = 0 Then Exit Sub
| > > strBefore = Left(strVal, idxStart - 1)
| > > idxStart = idxStart + Len(strKey)
| > > idxEnd = InStr(idxStart, strVal, ";")
| > > If idxEnd = 0 Then
| > > strAfter = ""
| > > strURL = Mid(strVal, idxStart)
| > > Else
| > > strAfter = ";" & Mid(strVal, idxEnd + 1)
| > > strURL = Mid(strVal, idxStart, idxEnd - idxStart)
| > > End If
| > > strOut = strBefore & Server.MapPath(Application("FrontPage_VRoot") &
| > > strURL) & strAfter
| > > Application(AppVarName) = strOut
| > > End Sub
| > > Function FrontPage_FileExists(fspath)
| > > On Error Resume Next
| > > FrontPage_FileExists = False
| > > set fs = CreateObject("Scripting.FileSystemObject")
| > > Err.Clear
| > > set istream = fs.OpenTextFile(fspath)
| > > if Err.Number = 0 then
| > > FrontPage_FileExists = True
| > > istream.Close
| > > end if
| > > set istream = Nothing
| > > set fs = Nothing
| > > End Function
| > > </SCRIPT>
| > >
| > > --
| > > ==============================================
| > > Thomas A. Rowe (Microsoft MVP - FrontPage)
| > > ==============================================
| > > If you feel your current issue is a results of installing
| > > a Service Pack or security update, please contact
| > > Microsoft Product Support Services:
| > > http://support.microsoft.com
| > > If the problem can be shown to have been caused by a
| > > security update, then there is usually no charge for the call.
| > > ==============================================
| > >
| > > | > > > Submit Page:
| > > >
| > > > <html>
| > > >
| > > > <head>
| > > > <meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
| > > > <meta http-equiv="Content-Language" content="en-us">
| > > > <title>Home Page</title>
| > > > <meta name="GENERATOR" content="Microsoft FrontPage 4.0">
| > > > <meta name="ProgId" content="FrontPage.Editor.Document">
| > > > </head>
| > > >
| > > > <body>
| > > > <form method="POST" action="result.asp" onSubmit="">
| > > > Keyword: <input type="text" name="organisation" size="20"><input
| > > > type="submit" value="Submit" name="B1"><input type="reset" value="Reset"
| > > > name="B2">
| > > > </form>
| > > > </body>
| > > >
| > > > </html>
| > > >
| > > >
| > > > Query Page (.asp)
| > > >
| > > > <html>
| > > >
| > > > <head>
| > > > <meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
| > > > <meta name="GENERATOR" content="Microsoft FrontPage 4.0">
| > > > <meta name="ProgId" content="FrontPage.Editor.Document">
| > > > <title>New Page 1</title>
| > > > </head>
| > > >
| > > > <body>
| > > >
| > > > <table width="100%" border="1">
| > > > <thead>
| > > > <tr>
| > > > <td>NUM</td>
| > > > <td>DST</td>
| > > > <td>STR</td>
| > > > <td>DDL</td>
| > > > <td>DLO</td>
| > > > <td>TWN</td>
| > > > <td>PCD</td>
| > > > <td>ORG</td>
| > > > <td>CTA</td>
| > > > <td>CTP</td>
| > > > <td>CTT</td>
| > > > </tr>
| > > > </thead>
| > > > <tbody>
| > > > <!--webbot bot="DatabaseRegionStart" startspan
| > > > s-columnnames="NUM,DST,STR,DDL,DLO,TWN,PCD,ORG,CTA,CTP,CTT"
| > > > s-columntypes="200,200,200,200,200,200,200,200,200,200,200"
| > > > s-dataconnection="Database2" b-tableformat="TRUE" b-menuformat="FALSE"
| > > > s-menuchoice="NUM" s-menuvalue="NUM" b-tableborder="TRUE"
| > > > b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="TRUE"
| > > > b-listseparator="TRUE" i-ListFormat="0" b-makeform="FALSE" s-recordsource
| > > > s-displaycolumns="NUM,DST,STR,DDL,DLO,TWN,PCD,ORG,CTA,CTP,CTT" s-criteria
| > > > s-order
| > > > s-sql="SELECT * FROM PAF_7t_VIew_by_sachin where (ORG LIKE
| > > > '%::Organisation::%') OR (NUM LIKE '%::Organisation::%') OR (DST
| > > > LIKE '%::Organisation::%') OR (STR LIKE '%::Organisation::%') OR (DLO
| > > > LIKE '%::Organisation::%') OR (TWN LIKE '%::Organisation::%') "
| > > > b-procedure="FALSE" clientside SuggestedExt="asp"
| > > >
| > > >
s-DefaultFields="Organisation=&Organisation=&Organisation=&Organisation=&Organisation=&Organisation="
| > > > s-NoRecordsFound="No records returned." i-MaxRecords="0" i-GroupSize="50"
| > > > BOTID="0" u-dblib="_fpclass/fpdblib.inc" u-dbrgn1="_fpclass/fpdbrgn1.inc"
| > > > u-dbrgn2="_fpclass/fpdbrgn2.inc" tag="TBODY"
| > > > local_preview="<tr><td colspan=64 bgcolor="#FFFF00" align="left"
| > > > width="100%">Database Results regions will not preview
| > > > unless this page is fetched from a Web server with a web browser. The
| > > > following table row will repeat once for every record returned by the

Creating a connection to MSSQL 2000 on MS Server 2003	2	Mar 15, 2006
SQL Connection Error with MS Access	7	Apr 1, 2009
troubles with dns-less connection to sql	0	May 2, 2013
Save to database help!!	2	Jan 15, 2004
Connection timout 0	5	May 23, 2007
Database Results Error Description: [Microsoft][ODBC SQL Server Driver]Timeout expired	2	Jul 7, 2003
Database Results Error Description: [Microsoft][ODBC SQL Server Driver]Timeout expired	1	Nov 20, 2003
Database Results Error Description: [Microsoft][ODBC SQL Server Driver]Timeout expired	4	Nov 12, 2003

FP and connection to a SQL database

Guest

Thomas A. Rowe

Guest

Thomas A. Rowe

Guest

Bob Lehmann

Thomas A. Rowe

Guest

Guest

Guest

Thomas A. Rowe

Thomas A. Rowe

Stefan B Rusynko

Thomas A. Rowe

Ask a Question

Similar Threads