Problem with 'where clause' in select statement

[Andre Larabie]

I have a problem with the simple code below.

I am using a where clause to restrict the display of only the individuals that start with the characters entered in the form field.

This works fine on the first attempt but does not on any other attempt.

If for example I want to display all employees starting with the letter "A", It displays 7 employees, which is correct. If I then want to display all employees staring with the letter "B" it will display again only 7 employees which is incorrect, in other words it will never display more than the previous query returned. If I request employees starting with "ANK" and it display only 1 employee named "Anka" any further request for all employees starting with "A" will now only display 1 employee. The only way I can get it to work properly again is to execute an ASP that does not have the 'where clause' in, even other queries with a where clause will remember the last number of entries retrieved and will continue the same trend and only retrieve the last number of employees retrieved.

Can you see any problem with the code, or explain why it behaves like this.

The platform I am using is Win 2000 with IE 5 and Enterprise 2002.





<% Response.Buffer=True %>
<!-- #include Virtual=/aspdb/ASPdb.Inc -->
<%
Set X= Server.CreateObject(&#34;ASP.DB&#34
namex = request.form(&#34;ddaname&#34
X.dbUnit = &#34;999&#34;
X.dbMode = &#34;grid&#34;
X.dbDSN = &#34;Driver={Microsoft Access Driver (*.mdb)}; DBQ=\dacrimsupportadmindbEmployeeDBEmployeedb.mdb& #34;
X.dbNameMap = &#34;Empid,Employee Number;first name,First Name;last name,Last Name;position,Position;HomeAdd1,Home address;HomeCity,City;Homestate,State;phone number,Office Number;HomePhone,Home Phone Number;CellPhone#,Cell Phone Number;Office,Office;Pager,Pager&#34;
X.dbSQL = &#34;SELECT Empid,[first name],[last name],position,Office,HomeAdd1,HomeCity,Homestate,[phone number],HomePhone,[CellPhone#],Pager FROM tblWEBDisplay where ([last name]) like &#39;&#34; & namex& &#34;%&#34; & &#34;&#39; order by [last name],[first name]&#34;

[Jeff B]

Sounds like it is keeping the dbRecordCount value from the first query. I think you need to issue dbReset(<Unit>) so it will pull all the rows.


------------
Andre Larabie at 1/23/2002 11:27:10 AM

I have a problem with the simple code below.

I am using a where clause to restrict the display of only the individuals that start with the characters entered in the form field.

This works fine on the first attempt but does not on any other attempt.

If for example I want to display all employees starting with the letter &#34;A&#34;, It displays 7 employees, which is correct. If I then want to display all employees staring with the letter &#34;B&#34; it will display again only 7 employees which is incorrect, in other words it will never display more than the previous query returned. If I request employees starting with &#34;ANK&#34; and it display only 1 employee named &#34;Anka&#34; any further request for all employees starting with &#34;A&#34; will now only display 1 employee. The only way I can get it to work properly again is to execute an ASP that does not have the &#39;where clause&#39; in, even other queries with a where clause will remember the last number of entries retrieved and will continue the same trend and only retrieve the last number of employees retrieved.

Can you see any problem with the code, or explain why it behaves like this.

The platform I am using is Win 2000 with IE 5 and Enterprise 2002.





<% Response.Buffer=True %>
<!-- #include Virtual=/aspdb/ASPdb.Inc -->
<%
Set X= Server.CreateObject(&#34;ASP.DB&#34
namex = request.form(&#34;ddaname&#34
X.dbUnit = &#34;999&#34;
X.dbMode = &#34;grid&#34;
X.dbDSN = &#34;Driver={Microsoft Access Driver (*.mdb)}; DBQ=\dacrimsupportadmindbEmployeeDBEmployeedb.mdb& #34;
X.dbNameMap = &#34;Empid,Employee Number;first name,First Name;last name,Last Name;position,Position;HomeAdd1,Home address;HomeCity,City;Homestate,State;phone number,Office Number;HomePhone,Home Phone Number;CellPhone#,Cell Phone Number;Office,Office;Pager,Pager&#34;
X.dbSQL = &#34;SELECT Empid,[first name],[last name],position,Office,HomeAdd1,HomeCity,Homestate,[phone number],HomePhone,[CellPhone#],Pager FROM tblWEBDisplay where ([last name]) like &#39;&#34; & namex& &#34;%&#34; & &#34;&#39; order by [last name],[first name]&#34;

[Frank Kwong]

I would like to give you an example to perform drill downs. There are probably more &#39;drills&#39; than you need. But it shows a good way to do it. The good way to do that is to produce the drill key with one module and then do the drill in the next module. Define a DSN as &#34;NWIND&#34; pointing to the Northwind DB and that is all you need to do. There are many similar question about this kind of drill down. Therefor I made it a standard mode in ASPdb.Net and not have to the user to customize it every time. I&#39;ll port that back to classic ASPdb if I have time. Cut an dpaste the code below ->

<CENTER><TABLE border=0><TR>
<TD> </TD><TD>
<%
Set X=Server.CreateObject(&#34;ASP.db&#34
X.dbQuickProps = &#34;10271;NWIND;Employees;Grid;;;;SQL;100&#34;
X.dbNavigationItem=&#34;none&#34;
X.dbstatusbar=false
X.dbSQL=&#34;SELECT employeeID, FirstName, LastName, ReportsTo FROM employees&#34;
X.dbSelectBox=&#34;5,<B>Employee (ID) Name</B>, Select Employee;(#employeeID#) #FirstName# #LastName#&#34;
X.dbGridTableTag = &#34;Border=1&#34;
X.dbExportFlds=&#34;0,1,2,3&#34;
X.ASPdb
%>
</TD>
<TH>

<%
if Session(&#34;ASPDB_10271_ReportsTo&#34 <> &#34;&#34; then
Set X=Server.CreateObject(&#34;ASP.db&#34
X.dbQuickProps = &#34;10272;NWIND;Employees;Form;;;;SQL&#34;
X.dbSQL=&#34;SELECT FirstName, LastName FROM employees where employeeID=&#34; & session(&#34;ASPDB_10271_ReportsTo&#34
&#39;X.dbOptions=&#34;Display=false&#34;
X.dbFormDisplayFlds = &#34;-1&#34;
X.dbStatusBar=false
X.dbNavigation=&#34;none&#34;
X.dbExportFlds=&#34;0,1&#34;
X.ASPdb
ThisPerson = Session(&#34;ASPDB_10271_FirstName&#34 & &#34;&nbsp;&#34; & Session(&#34;ASPDB_10271_LastName&#34
ReportsTo = Session(&#34;ASPDB_10272_FirstName&#34 & &#34;&nbsp;&#34; & session(&#34;ASPDB_10272_LastName&#34
response.write(ThisPerson & &#34; reports to: &#34; & ReportsTo)
end if
%>

</TH></TR><TR><TD colspan=4><HR></TD></TR><TR valign=top><TD>

<%
Set X=Server.CreateObject(&#34;ASP.db&#34
X.dbDSN = &#34;NWIND&#34;
X.dbDBType=&#34;SQL&#34;
X.dbUnit=10273
X.dbMode=&#34;grid&#34;
X.dbSQL=&#34;SELECT employeeID, OrderID FROM Orders where employeeID=&#34; & session(&#34;ASPDB_10271_employeeID&#34
X.dbSelectBox=&#34;5,<B>Orders</B>; #OrderID#&#34;
X.dbGridInc=999999
X.dbGridTableTag=&#34;Border=1&#34;
X.dbNavigationItem=&#34;none&#34;
X.dbExportFlds=&#34;OrderID&#34;
X.dbstatusbar=false
X.dbFormTableTag = &#34;Border=1&#34;
X.ASPdb
%>
</TD>

<TD>
<%
Set X=Server.CreateObject(&#34;ASP.db&#34
s = zHead & &#34;,<CENTER><b>Order Details</b></CENTER><BR>; &#34;
&#39;X.dbUserLocalText=s
X.dbDSN = &#34;NWIND&#34;
X.dbDBType=&#34;SQL&#34;
X.dbUnit=10274
X.dbMode=&#34;form&#34;
X.dbSQL=&#34;SELECT * FROM [Order details] where orderID=&#34; & session(&#34;ASPDB_10273_orderID&#34
X.dbNavigationItem=&#34;none&#34;
X.dbstatusbar=false
X.dbExportFlds=&#34;ProductID&#34;
X.dbFormTableTag = &#34;Border=1&#34;
X.ASPdb
%>
</TD>

<TD>
<%
Set X=Server.CreateObject(&#34;ASP.db&#34
s = zHead & &#34;,<CENTER><b>Product Details</b></CENTER><BR>; &#34;
&#39;X.dbUserLocalText=s
X.dbDSN = &#34;NWIND&#34;
X.dbDBType=&#34;SQL&#34;
X.dbUnit=10275
X.dbMode=&#34;form&#34;
X.dbSQL=&#34;SELECT * FROM Products where productID=&#34; & session(&#34;ASPDB_10274_productID&#34
X.dbNavigationItem=&#34;none&#34;
X.dbstatusbar=false
X.dbExportFlds=&#34;SupplierID&#34;
X.dbFormTableTag = &#34;Border=1&#34;
X.ASPdb
%>
</TD>

<TD>
<%
Set X=Server.CreateObject(&#34;ASP.db&#34
s = zHead & &#34;,<CENTER><b>Supplier Details</b></CENTER><BR>; &#34;
&#39;X.dbUserLocalText=s
X.dbDSN = &#34;NWIND&#34;
X.dbDBType=&#34;SQL&#34;
X.dbUnit=10276
X.dbMode=&#34;form&#34;
X.dbSQL=&#34;SELECT * FROM Suppliers where supplierID=&#34; & session(&#34;ASPDB_10275_supplierID&#34
X.dbNavigationItem=&#34;none&#34;
X.dbstatusbar=false
X.dbFormTableTag = &#34;Border=1&#34;
X.ASPdb
%>
</TD>
</TR></TABLE>










------------
Andre Larabie at 1/23/2002 11:27:10 AM

I have a problem with the simple code below.

I am using a where clause to restrict the display of only the individuals that start with the characters entered in the form field.

This works fine on the first attempt but does not on any other attempt.

If for example I want to display all employees starting with the letter &#34;A&#34;, It displays 7 employees, which is correct. If I then want to display all employees staring with the letter &#34;B&#34; it will display again only 7 employees which is incorrect, in other words it will never display more than the previous query returned. If I request employees starting with &#34;ANK&#34; and it display only 1 employee named &#34;Anka&#34; any further request for all employees starting with &#34;A&#34; will now only display 1 employee. The only way I can get it to work properly again is to execute an ASP that does not have the &#39;where clause&#39; in, even other queries with a where clause will remember the last number of entries retrieved and will continue the same trend and only retrieve the last number of employees retrieved.

Can you see any problem with the code, or explain why it behaves like this.

The platform I am using is Win 2000 with IE 5 and Enterprise 2002.





<% Response.Buffer=True %>
<!-- #include Virtual=/aspdb/ASPdb.Inc -->
<%
Set X= Server.CreateObject(&#34;ASP.DB&#34
namex = request.form(&#34;ddaname&#34
X.dbUnit = &#34;999&#34;
X.dbMode = &#34;grid&#34;
X.dbDSN = &#34;Driver={Microsoft Access Driver (*.mdb)}; DBQ=\dacrimsupportadmindbEmployeeDBEmployeedb.mdb& #34;
X.dbNameMap = &#34;Empid,Employee Number;first name,First Name;last name,Last Name;position,Position;HomeAdd1,Home address;HomeCity,City;Homestate,State;phone number,Office Number;HomePhone,Home Phone Number;CellPhone#,Cell Phone Number;Office,Office;Pager,Pager&#34;
X.dbSQL = &#34;SELECT Empid,[first name],[last name],position,Office,HomeAdd1,HomeCity,Homestate,[phone number],HomePhone,[CellPhone#],Pager FROM tblWEBDisplay where ([last name]) like &#39;&#34; & namex& &#34;%&#34; & &#34;&#39; order by [last name],[first name]&#34;