Truncation of subsequent queries

[Steven Overson]

I have a case where it looks like ASP-db is causing subsequent database queries to be truncated in the number of records they return. For example, suppose I have two tables and two queries. The Project table has 4 records in it, and the Test table has 50 records in it. If I execute the first query which uses ASP-db:

sSQL = "SELECT * FROM [Project]"

Set MyDB = Server.CreateObject("AspDB.View&#34
MyDB.Dbdsn = "DSN=my_dsn;UID=my_uid;PWD=my_pass"
MyDB.dbDBType="SQL"
MyDB.dbMode = "Grid"
MyDB.dbGridInc=16
MyDB.DbSQL=sSQL
MyDB.dbUnit = 52
MyDB.aspDBView

It correctly displays the 4 records in the Project table. But now if I load another page that includes the following query:

Set pf_Conn=Server.CreateObject("ADODB.Connection& #34
pf_Conn.Open "DSN=my_dsn;UID=my_uid;PWD=my_pass"
pf_sSQL = "SELECT * FROM [Test] ORDER BY Column1"
Set pf_rs=pf_Conn.Execute(pf_sSQL)
do until (pf_rs.EOF)
sOption = pf_rs(&#34;Column1&#34 & &#34; &#34; & pf_rs(&#34;Column2&#34 & &#34;<br>&#34;
Response.Write(sOption)
pf_rs.MoveNext
loop

It only displays the first 16 records, not the 50 that it should. If I open a new browser window and run the second query without running the first query, it correctly displays the 50 records in the Test table. By varying the dbGridInc number in the first query, I can vary the number of records displayed in the second query. In fact, if I comment-out the dbGridInc line and change dbMode to Form (which has a default of 5 for dbGridInc), the second query is truncated to 5 records.

For some reason the second query has to be in a separate .asp file. Just putting the second query after the first in the same file does not seem to demonstrate the same problem.

I have tried the ‘SELECT ALL...” and “SELECT TOP n...” and “SELECT TOP n PERCENT...” queries with no success. How can I avoid this interaction?

Thanks,
Steven

[John]

Hi Steven,

Couple of things...

First, make sure you&#39;re using the latest version of the software. You can simply download the Pro eval version from our web site and install that and see if it clears up the problem. Make sure if you do, to change the &#34;View&#34; calls to &#34;Pro&#34;

Second, it seems like you&#39;re mixing apples and oranges. You&#39;re using ASP-db to display your first recordset, and then doing the whole thing manually right below that. Is there a reason you&#39;re not letting ASP-db do the reading a second time? We open, read and close the data set so there&#39;s no reason we should affect the number of records returned by your own code.

Third, if you&#39;re using multiple ASP-db objects on a page, or displaying multiple tables, you MUST use a unique dbUnit number. If you don&#39;t all kinds of strange things will happen. Make sure this isn&#39;t a problem.

Thanks,
John



------------
Steven Overson at 3/24/99 2:48:10 AM

I have a case where it looks like ASP-db is causing subsequent database queries to be truncated in the number of records they return. For example, suppose I have two tables and two queries. The Project table has 4 records in it, and the Test table has 50 records in it. If I execute the first query which uses ASP-db:

sSQL = &#34;SELECT * FROM [Project]&#34;

Set MyDB = Server.CreateObject(&#34;AspDB.View&#34
MyDB.Dbdsn = &#34;DSN=my_dsn;UID=my_uid;PWD=my_pass&#34;
MyDB.dbDBType=&#34;SQL&#34;
MyDB.dbMode = &#34;Grid&#34;
MyDB.dbGridInc=16
MyDB.DbSQL=sSQL
MyDB.dbUnit = 52
MyDB.aspDBView

It correctly displays the 4 records in the Project table. But now if I load another page that includes the following query:

Set pf_Conn=Server.CreateObject(&#34;ADODB.Connection& #34
pf_Conn.Open &#34;DSN=my_dsn;UID=my_uid;PWD=my_pass&#34;
pf_sSQL = &#34;SELECT * FROM [Test] ORDER BY Column1&#34;
Set pf_rs=pf_Conn.Execute(pf_sSQL)
do until (pf_rs.EOF)
sOption = pf_rs(&#34;Column1&#34 & &#34; &#34; & pf_rs(&#34;Column2&#34 & &#34;<br>&#34;
Response.Write(sOption)
pf_rs.MoveNext
loop

It only displays the first 16 records, not the 50 that it should. If I open a new browser window and run the second query without running the first query, it correctly displays the 50 records in the Test table. By varying the dbGridInc number in the first query, I can vary the number of records displayed in the second query. In fact, if I comment-out the dbGridInc line and change dbMode to Form (which has a default of 5 for dbGridInc), the second query is truncated to 5 records.

For some reason the second query has to be in a separate .asp file. Just putting the second query after the first in the same file does not seem to demonstrate the same problem.

I have tried the ‘SELECT ALL...” and “SELECT TOP n...” and “SELECT TOP n PERCENT...” queries with no success. How can I avoid this interaction?

Thanks,
Steven

[Steven Overson]

The problem only seems to happen only on my remote server, the local version works fine. So to test the newest version, I am going to need the latest update so I can test it on the server. How can I arrange to get it?

For your second and third comments, the non-ASP-db query is not on the same page, but on a separate page that just happens to be loaded after the ASP-db-based page. And I am using a manual query there to fill a listbox control. The symptoms are the same whether I write the list to a listbox or the page, so I left the listbox stuff out to simplify my example.

Thanks,
Steven

------------
John at 3/24/99 1:13:41 PM

Hi Steven,

Couple of things...

First, make sure you&#39;re using the latest version of the software. You can simply download the Pro eval version from our web site and install that and see if it clears up the problem. Make sure if you do, to change the &#34;View&#34; calls to &#34;Pro&#34;

Second, it seems like you&#39;re mixing apples and oranges. You&#39;re using ASP-db to display your first recordset, and then doing the whole thing manually right below that. Is there a reason you&#39;re not letting ASP-db do the reading a second time? We open, read and close the data set so there&#39;s no reason we should affect the number of records returned by your own code.

Third, if you&#39;re using multiple ASP-db objects on a page, or displaying multiple tables, you MUST use a unique dbUnit number. If you don&#39;t all kinds of strange things will happen. Make sure this isn&#39;t a problem.

Thanks,
John



------------
Steven Overson at 3/24/99 2:48:10 AM

I have a case where it looks like ASP-db is causing subsequent database queries to be truncated in the number of records they return. For example, suppose I have two tables and two queries. The Project table has 4 records in it, and the Test table has 50 records in it. If I execute the first query which uses ASP-db:

sSQL = &#34;SELECT * FROM [Project]&#34;

Set MyDB = Server.CreateObject(&#34;AspDB.View&#34
MyDB.Dbdsn = &#34;DSN=my_dsn;UID=my_uid;PWD=my_pass&#34;
MyDB.dbDBType=&#34;SQL&#34;
MyDB.dbMode = &#34;Grid&#34;
MyDB.dbGridInc=16
MyDB.DbSQL=sSQL
MyDB.dbUnit = 52
MyDB.aspDBView

It correctly displays the 4 records in the Project table. But now if I load another page that includes the following query:

Set pf_Conn=Server.CreateObject(&#34;ADODB.Connection& #34
pf_Conn.Open &#34;DSN=my_dsn;UID=my_uid;PWD=my_pass&#34;
pf_sSQL = &#34;SELECT * FROM [Test] ORDER BY Column1&#34;
Set pf_rs=pf_Conn.Execute(pf_sSQL)
do until (pf_rs.EOF)
sOption = pf_rs(&#34;Column1&#34 & &#34; &#34; & pf_rs(&#34;Column2&#34 & &#34;<br>&#34;
Response.Write(sOption)
pf_rs.MoveNext
loop

It only displays the first 16 records, not the 50 that it should. If I open a new browser window and run the second query without running the first query, it correctly displays the 50 records in the Test table. By varying the dbGridInc number in the first query, I can vary the number of records displayed in the second query. In fact, if I comment-out the dbGridInc line and change dbMode to Form (which has a default of 5 for dbGridInc), the second query is truncated to 5 records.

For some reason the second query has to be in a separate .asp file. Just putting the second query after the first in the same file does not seem to demonstrate the same problem.

I have tried the ‘SELECT ALL...” and “SELECT TOP n...” and “SELECT TOP n PERCENT...” queries with no success. How can I avoid this interaction?

Thanks,
Steven

[John]

Steven,

Call our office at 248 350 9177 and we&#39;ll get it to you.

Thanks!
John


------------
Steven Overson at 3/24/99 3:49:41 PM

The problem only seems to happen only on my remote server, the local version works fine. So to test the newest version, I am going to need the latest update so I can test it on the server. How can I arrange to get it?

For your second and third comments, the non-ASP-db query is not on the same page, but on a separate page that just happens to be loaded after the ASP-db-based page. And I am using a manual query there to fill a listbox control. The symptoms are the same whether I write the list to a listbox or the page, so I left the listbox stuff out to simplify my example.

Thanks,
Steven

------------
John at 3/24/99 1:13:41 PM

Hi Steven,

Couple of things...

First, make sure you&#39;re using the latest version of the software. You can simply download the Pro eval version from our web site and install that and see if it clears up the problem. Make sure if you do, to change the &#34;View&#34; calls to &#34;Pro&#34;

Second, it seems like you&#39;re mixing apples and oranges. You&#39;re using ASP-db to display your first recordset, and then doing the whole thing manually right below that. Is there a reason you&#39;re not letting ASP-db do the reading a second time? We open, read and close the data set so there&#39;s no reason we should affect the number of records returned by your own code.

Third, if you&#39;re using multiple ASP-db objects on a page, or displaying multiple tables, you MUST use a unique dbUnit number. If you don&#39;t all kinds of strange things will happen. Make sure this isn&#39;t a problem.

Thanks,
John



------------
Steven Overson at 3/24/99 2:48:10 AM

I have a case where it looks like ASP-db is causing subsequent database queries to be truncated in the number of records they return. For example, suppose I have two tables and two queries. The Project table has 4 records in it, and the Test table has 50 records in it. If I execute the first query which uses ASP-db:

sSQL = &#34;SELECT * FROM [Project]&#34;

Set MyDB = Server.CreateObject(&#34;AspDB.View&#34
MyDB.Dbdsn = &#34;DSN=my_dsn;UID=my_uid;PWD=my_pass&#34;
MyDB.dbDBType=&#34;SQL&#34;
MyDB.dbMode = &#34;Grid&#34;
MyDB.dbGridInc=16
MyDB.DbSQL=sSQL
MyDB.dbUnit = 52
MyDB.aspDBView

It correctly displays the 4 records in the Project table. But now if I load another page that includes the following query:

Set pf_Conn=Server.CreateObject(&#34;ADODB.Connection& #34
pf_Conn.Open &#34;DSN=my_dsn;UID=my_uid;PWD=my_pass&#34;
pf_sSQL = &#34;SELECT * FROM [Test] ORDER BY Column1&#34;
Set pf_rs=pf_Conn.Execute(pf_sSQL)
do until (pf_rs.EOF)
sOption = pf_rs(&#34;Column1&#34 & &#34; &#34; & pf_rs(&#34;Column2&#34 & &#34;<br>&#34;
Response.Write(sOption)
pf_rs.MoveNext
loop

It only displays the first 16 records, not the 50 that it should. If I open a new browser window and run the second query without running the first query, it correctly displays the 50 records in the Test table. By varying the dbGridInc number in the first query, I can vary the number of records displayed in the second query. In fact, if I comment-out the dbGridInc line and change dbMode to Form (which has a default of 5 for dbGridInc), the second query is truncated to 5 records.

For some reason the second query has to be in a separate .asp file. Just putting the second query after the first in the same file does not seem to demonstrate the same problem.

I have tried the ‘SELECT ALL...” and “SELECT TOP n...” and “SELECT TOP n PERCENT...” queries with no success. How can I avoid this interaction?

Thanks,
Steven