Only getting first record in grid

[Larry G]

Hi,
I have a page that displays a grid and when the index is clicked through to the form page, there will be a second grid which relies on an Export field from grid 1. For some reason, grid 2 will only show the first record from the recordset and show a 1 - 1: 1 in the Status Bar while I'm certain there are more than one record. I've response.write the SQL and pasted it into a query in Access and it always returns all of the records. Why would this happen?

Thanks,
Larry

<%

ASQL = &#34;SELECT tblContacts.ContactID, tblContacts.LastName, tblContacts.FirstName, Count(tblPubs.PubID) AS PubCount &#34; & _
&#34;FROM (tblAuthors INNER JOIN tblContacts ON tblAuthors.ContactID = tblContacts.ContactID) INNER JOIN tblPubs ON tblAuthors.PubID = tblPubs.PubID &#34; & _
&#34;GROUP BY tblContacts.ContactID, tblContacts.LastName, tblContacts.FirstName&#34;

Set A = Server.CreateObject(&#34;ASP.db&#34
A.dbUnit = 1007
A.dbDSN = &#34;Epubs&#34;
A.dbMode = &#34;Both&#34;
A.dbSQL = ASQL
A.dbGridTableTag = &#34;Border=0&#34;
A.dbGridIndex = False
A.dbGridCol = &#34;3&#34;
A.dbGridInc = &#34;6&#34;
A.dbFormDisplayFlds = &#34;2,3&#34;
A.dbNavigation = &#34;None&#34;
A.dbOptions = &#34;HeaderFontTag=Face=Verdana Size=2, CellFontTag=Face=Verdana Size=2&#34;
A.dbNavigation = &#34;Top&#34;
A.dbNavigationItem=&#34;grid, form, top, prev, next, bottom, row+, row-, grid+, grid-, form+, form-, filter, resetfilter, download, color, reload&#34;
A.dbNavigationIcon=&#34;std&#34;
A.dbExportFlds = &#34;ContactID&#34;
A.dbImageDir=&#34;images/&#34;
A.dbNameMap = &#34;FirstName,Author;PubCount, Pub Count&#34;
A.dbMagicCell = &#34;FirstName,,#FirstName# #LastName#, index;PubCount,align=center&#34;
A.dbGridDisplayFlds = &#34;FirstName, PubCount&#34;
A.dbSuppressMsg = True
A.ASPdb

If A.dbLastScreen = &#34;form&#34; Then
response.write Session( &#34;ASPDB_1007_ContactID&#34; )
DSQL = &#34;SELECT tblPubs.PubID, tblPubs.Title, tblPubs.PType, tblTargets.Target, tblPubs.Status &#34; & _
&#34;FROM (tblContacts INNER JOIN (tblPubs INNER JOIN tblAuthors ON tblPubs.PubID = tblAuthors.PubID) &#34; & _
&#34;ON tblContacts.ContactID = tblAuthors.ContactID) INNER JOIN tblTargets ON tblPubs.TargetID = tblTargets.targetID &#34; & _
&#34;WHERE (((tblContacts.ContactID)=&#34; & Session( &#34;ASPDB_1007_ContactID&#34; ) & &#34)&#34;
Set D = Server.CreateObject(&#34;ASP.db&#34
D.dbUnit = 1008
D.dbDSN = &#34;Epubs&#34;
D.dbMode = &#34;Grid&#34;
D.dbSQL = DSQL
D.dbGridTableTag = &#34;Border=1&#34;
D.dbGridIndex = False
D.dbGridInc = &#34;6&#34;
D.dbGridDisplayFlds = &#34;0,1,2,3,4&#34;
D.dbOptions = &#34;HeaderFontTag=Face=Verdana Size=2, CellFontTag=Face=Verdana Size=2&#34;
D.dbNavigation = &#34;Bottom&#34;
D.dbNavigationItem=&#34;grid, top, prev, next, bottom, row+, row-, grid+, grid-, form+, form-, filter, resetfilter, download, color, reload&#34;
D.dbNavigationIcon=&#34;std&#34;
D.dbExportFlds = &#34;PubID&#34;
D.dbImageDir=&#34;images/&#34;
D.dbNameMap = &#34;PType, Pub Type&#34;
&#39;D.dbMagicCell = &#34;2,,<A HREF=pubs.asp?PUB=#(1)#>#(2)#</A>&#34;
D.dbSuppressMsg = True
D.dbstatusBar = False
D.ASPdb
response.write DSQL
End If


%>

[Mark]

Larry,
You must add a D.dbReset(1008) after the
If D.dbUnit = 1008
because your Where clause has a variable in it.
Also, for testing, I suggest you set SuppressMsg=False and StatusBar=True.
Try that and see if it helps. Let me know, thanks,
Mark.


------------
Larry G at 2/20/01 12:58:37 PM

Hi,
I have a page that displays a grid and when the index is clicked through to the form page, there will be a second grid which relies on an Export field from grid 1. For some reason, grid 2 will only show the first record from the recordset and show a 1 - 1: 1 in the Status Bar while I&#39;m certain there are more than one record. I&#39;ve response.write the SQL and pasted it into a query in Access and it always returns all of the records. Why would this happen?

Thanks,
Larry

<%

ASQL = &#34;SELECT tblContacts.ContactID, tblContacts.LastName, tblContacts.FirstName, Count(tblPubs.PubID) AS PubCount &#34; & _
&#34;FROM (tblAuthors INNER JOIN tblContacts ON tblAuthors.ContactID = tblContacts.ContactID) INNER JOIN tblPubs ON tblAuthors.PubID = tblPubs.PubID &#34; & _
&#34;GROUP BY tblContacts.ContactID, tblContacts.LastName, tblContacts.FirstName&#34;

Set A = Server.CreateObject(&#34;ASP.db&#34
A.dbUnit = 1007
A.dbDSN = &#34;Epubs&#34;
A.dbMode = &#34;Both&#34;
A.dbSQL = ASQL
A.dbGridTableTag = &#34;Border=0&#34;
A.dbGridIndex = False
A.dbGridCol = &#34;3&#34;
A.dbGridInc = &#34;6&#34;
A.dbFormDisplayFlds = &#34;2,3&#34;
A.dbNavigation = &#34;None&#34;
A.dbOptions = &#34;HeaderFontTag=Face=Verdana Size=2, CellFontTag=Face=Verdana Size=2&#34;
A.dbNavigation = &#34;Top&#34;
A.dbNavigationItem=&#34;grid, form, top, prev, next, bottom, row+, row-, grid+, grid-, form+, form-, filter, resetfilter, download, color, reload&#34;
A.dbNavigationIcon=&#34;std&#34;
A.dbExportFlds = &#34;ContactID&#34;
A.dbImageDir=&#34;images/&#34;
A.dbNameMap = &#34;FirstName,Author;PubCount, Pub Count&#34;
A.dbMagicCell = &#34;FirstName,,#FirstName# #LastName#, index;PubCount,align=center&#34;
A.dbGridDisplayFlds = &#34;FirstName, PubCount&#34;
A.dbSuppressMsg = True
A.ASPdb

If A.dbLastScreen = &#34;form&#34; Then
response.write Session( &#34;ASPDB_1007_ContactID&#34; )
DSQL = &#34;SELECT tblPubs.PubID, tblPubs.Title, tblPubs.PType, tblTargets.Target, tblPubs.Status &#34; & _
&#34;FROM (tblContacts INNER JOIN (tblPubs INNER JOIN tblAuthors ON tblPubs.PubID = tblAuthors.PubID) &#34; & _
&#34;ON tblContacts.ContactID = tblAuthors.ContactID) INNER JOIN tblTargets ON tblPubs.TargetID = tblTargets.targetID &#34; & _
&#34;WHERE (((tblContacts.ContactID)=&#34; & Session( &#34;ASPDB_1007_ContactID&#34; ) & &#34)&#34;
Set D = Server.CreateObject(&#34;ASP.db&#34
D.dbUnit = 1008
D.dbDSN = &#34;Epubs&#34;
D.dbMode = &#34;Grid&#34;
D.dbSQL = DSQL
D.dbGridTableTag = &#34;Border=1&#34;
D.dbGridIndex = False
D.dbGridInc = &#34;6&#34;
D.dbGridDisplayFlds = &#34;0,1,2,3,4&#34;
D.dbOptions = &#34;HeaderFontTag=Face=Verdana Size=2, CellFontTag=Face=Verdana Size=2&#34;
D.dbNavigation = &#34;Bottom&#34;
D.dbNavigationItem=&#34;grid, top, prev, next, bottom, row+, row-, grid+, grid-, form+, form-, filter, resetfilter, download, color, reload&#34;
D.dbNavigationIcon=&#34;std&#34;
D.dbExportFlds = &#34;PubID&#34;
D.dbImageDir=&#34;images/&#34;
D.dbNameMap = &#34;PType, Pub Type&#34;
&#39;D.dbMagicCell = &#34;2,,<A HREF=pubs.asp?PUB=#(1)#>#(2)#</A>&#34;
D.dbSuppressMsg = True
D.dbstatusBar = False
D.ASPdb
response.write DSQL
End If


%>

[Larry G]

Thanks Mark, that did the trick. I appreciate the quick response.

Regards,
Larry


------------
Mark at 2/20/01 1:13:29 PM

Larry,
You must add a D.dbReset(1008) after the
If D.dbUnit = 1008
because your Where clause has a variable in it.
Also, for testing, I suggest you set SuppressMsg=False and StatusBar=True.
Try that and see if it helps. Let me know, thanks,
Mark.


------------
Larry G at 2/20/01 12:58:37 PM

Hi,
I have a page that displays a grid and when the index is clicked through to the form page, there will be a second grid which relies on an Export field from grid 1. For some reason, grid 2 will only show the first record from the recordset and show a 1 - 1: 1 in the Status Bar while I&#39;m certain there are more than one record. I&#39;ve response.write the SQL and pasted it into a query in Access and it always returns all of the records. Why would this happen?

Thanks,
Larry

<%

ASQL = &#34;SELECT tblContacts.ContactID, tblContacts.LastName, tblContacts.FirstName, Count(tblPubs.PubID) AS PubCount &#34; & _
&#34;FROM (tblAuthors INNER JOIN tblContacts ON tblAuthors.ContactID = tblContacts.ContactID) INNER JOIN tblPubs ON tblAuthors.PubID = tblPubs.PubID &#34; & _
&#34;GROUP BY tblContacts.ContactID, tblContacts.LastName, tblContacts.FirstName&#34;

Set A = Server.CreateObject(&#34;ASP.db&#34
A.dbUnit = 1007
A.dbDSN = &#34;Epubs&#34;
A.dbMode = &#34;Both&#34;
A.dbSQL = ASQL
A.dbGridTableTag = &#34;Border=0&#34;
A.dbGridIndex = False
A.dbGridCol = &#34;3&#34;
A.dbGridInc = &#34;6&#34;
A.dbFormDisplayFlds = &#34;2,3&#34;
A.dbNavigation = &#34;None&#34;
A.dbOptions = &#34;HeaderFontTag=Face=Verdana Size=2, CellFontTag=Face=Verdana Size=2&#34;
A.dbNavigation = &#34;Top&#34;
A.dbNavigationItem=&#34;grid, form, top, prev, next, bottom, row+, row-, grid+, grid-, form+, form-, filter, resetfilter, download, color, reload&#34;
A.dbNavigationIcon=&#34;std&#34;
A.dbExportFlds = &#34;ContactID&#34;
A.dbImageDir=&#34;images/&#34;
A.dbNameMap = &#34;FirstName,Author;PubCount, Pub Count&#34;
A.dbMagicCell = &#34;FirstName,,#FirstName# #LastName#, index;PubCount,align=center&#34;
A.dbGridDisplayFlds = &#34;FirstName, PubCount&#34;
A.dbSuppressMsg = True
A.ASPdb

If A.dbLastScreen = &#34;form&#34; Then
response.write Session( &#34;ASPDB_1007_ContactID&#34; )
DSQL = &#34;SELECT tblPubs.PubID, tblPubs.Title, tblPubs.PType, tblTargets.Target, tblPubs.Status &#34; & _
&#34;FROM (tblContacts INNER JOIN (tblPubs INNER JOIN tblAuthors ON tblPubs.PubID = tblAuthors.PubID) &#34; & _
&#34;ON tblContacts.ContactID = tblAuthors.ContactID) INNER JOIN tblTargets ON tblPubs.TargetID = tblTargets.targetID &#34; & _
&#34;WHERE (((tblContacts.ContactID)=&#34; & Session( &#34;ASPDB_1007_ContactID&#34; ) & &#34)&#34;
Set D = Server.CreateObject(&#34;ASP.db&#34
D.dbUnit = 1008
D.dbDSN = &#34;Epubs&#34;
D.dbMode = &#34;Grid&#34;
D.dbSQL = DSQL
D.dbGridTableTag = &#34;Border=1&#34;
D.dbGridIndex = False
D.dbGridInc = &#34;6&#34;
D.dbGridDisplayFlds = &#34;0,1,2,3,4&#34;
D.dbOptions = &#34;HeaderFontTag=Face=Verdana Size=2, CellFontTag=Face=Verdana Size=2&#34;
D.dbNavigation = &#34;Bottom&#34;
D.dbNavigationItem=&#34;grid, top, prev, next, bottom, row+, row-, grid+, grid-, form+, form-, filter, resetfilter, download, color, reload&#34;
D.dbNavigationIcon=&#34;std&#34;
D.dbExportFlds = &#34;PubID&#34;
D.dbImageDir=&#34;images/&#34;
D.dbNameMap = &#34;PType, Pub Type&#34;
&#39;D.dbMagicCell = &#34;2,,<A HREF=pubs.asp?PUB=#(1)#>#(2)#</A>&#34;
D.dbSuppressMsg = True
D.dbstatusBar = False
D.ASPdb
response.write DSQL
End If


%>

[Larry G]

Mark,
One problem. The addition of D.dbReset(1008) looks like it has broken the grids paging. If the SQL returns 30 records and I&#39;m displaying in incriments of 5, clicking the Next button doesn&#39;t do anything. Any thoughts?

Thanks again,
Larry


------------
Mark at 2/20/01 1:13:29 PM

Larry,
You must add a D.dbReset(1008) after the
If D.dbUnit = 1008
because your Where clause has a variable in it.
Also, for testing, I suggest you set SuppressMsg=False and StatusBar=True.
Try that and see if it helps. Let me know, thanks,
Mark.


------------
Larry G at 2/20/01 12:58:37 PM

Hi,
I have a page that displays a grid and when the index is clicked through to the form page, there will be a second grid which relies on an Export field from grid 1. For some reason, grid 2 will only show the first record from the recordset and show a 1 - 1: 1 in the Status Bar while I&#39;m certain there are more than one record. I&#39;ve response.write the SQL and pasted it into a query in Access and it always returns all of the records. Why would this happen?

Thanks,
Larry

<%

ASQL = &#34;SELECT tblContacts.ContactID, tblContacts.LastName, tblContacts.FirstName, Count(tblPubs.PubID) AS PubCount &#34; & _
&#34;FROM (tblAuthors INNER JOIN tblContacts ON tblAuthors.ContactID = tblContacts.ContactID) INNER JOIN tblPubs ON tblAuthors.PubID = tblPubs.PubID &#34; & _
&#34;GROUP BY tblContacts.ContactID, tblContacts.LastName, tblContacts.FirstName&#34;

Set A = Server.CreateObject(&#34;ASP.db&#34
A.dbUnit = 1007
A.dbDSN = &#34;Epubs&#34;
A.dbMode = &#34;Both&#34;
A.dbSQL = ASQL
A.dbGridTableTag = &#34;Border=0&#34;
A.dbGridIndex = False
A.dbGridCol = &#34;3&#34;
A.dbGridInc = &#34;6&#34;
A.dbFormDisplayFlds = &#34;2,3&#34;
A.dbNavigation = &#34;None&#34;
A.dbOptions = &#34;HeaderFontTag=Face=Verdana Size=2, CellFontTag=Face=Verdana Size=2&#34;
A.dbNavigation = &#34;Top&#34;
A.dbNavigationItem=&#34;grid, form, top, prev, next, bottom, row+, row-, grid+, grid-, form+, form-, filter, resetfilter, download, color, reload&#34;
A.dbNavigationIcon=&#34;std&#34;
A.dbExportFlds = &#34;ContactID&#34;
A.dbImageDir=&#34;images/&#34;
A.dbNameMap = &#34;FirstName,Author;PubCount, Pub Count&#34;
A.dbMagicCell = &#34;FirstName,,#FirstName# #LastName#, index;PubCount,align=center&#34;
A.dbGridDisplayFlds = &#34;FirstName, PubCount&#34;
A.dbSuppressMsg = True
A.ASPdb

If A.dbLastScreen = &#34;form&#34; Then
response.write Session( &#34;ASPDB_1007_ContactID&#34; )
DSQL = &#34;SELECT tblPubs.PubID, tblPubs.Title, tblPubs.PType, tblTargets.Target, tblPubs.Status &#34; & _
&#34;FROM (tblContacts INNER JOIN (tblPubs INNER JOIN tblAuthors ON tblPubs.PubID = tblAuthors.PubID) &#34; & _
&#34;ON tblContacts.ContactID = tblAuthors.ContactID) INNER JOIN tblTargets ON tblPubs.TargetID = tblTargets.targetID &#34; & _
&#34;WHERE (((tblContacts.ContactID)=&#34; & Session( &#34;ASPDB_1007_ContactID&#34; ) & &#34)&#34;
Set D = Server.CreateObject(&#34;ASP.db&#34
D.dbUnit = 1008
D.dbDSN = &#34;Epubs&#34;
D.dbMode = &#34;Grid&#34;
D.dbSQL = DSQL
D.dbGridTableTag = &#34;Border=1&#34;
D.dbGridIndex = False
D.dbGridInc = &#34;6&#34;
D.dbGridDisplayFlds = &#34;0,1,2,3,4&#34;
D.dbOptions = &#34;HeaderFontTag=Face=Verdana Size=2, CellFontTag=Face=Verdana Size=2&#34;
D.dbNavigation = &#34;Bottom&#34;
D.dbNavigationItem=&#34;grid, top, prev, next, bottom, row+, row-, grid+, grid-, form+, form-, filter, resetfilter, download, color, reload&#34;
D.dbNavigationIcon=&#34;std&#34;
D.dbExportFlds = &#34;PubID&#34;
D.dbImageDir=&#34;images/&#34;
D.dbNameMap = &#34;PType, Pub Type&#34;
&#39;D.dbMagicCell = &#34;2,,<A HREF=pubs.asp?PUB=#(1)#>#(2)#</A>&#34;
D.dbSuppressMsg = True
D.dbstatusBar = False
D.ASPdb
response.write DSQL
End If


%>

[Mark]

Larry,
Yes, only do the dbReset IF and WHEN the Where clause changes.
In other words ... whenever Session(&#34;ASPDB_1007_ContactID&#34 changes.
Mark.


------------
Larry G at 2/20/01 2:04:23 PM

Mark,
One problem. The addition of D.dbReset(1008) looks like it has broken the grids paging. If the SQL returns 30 records and I&#39;m displaying in incriments of 5, clicking the Next button doesn&#39;t do anything. Any thoughts?

Thanks again,
Larry


------------
Mark at 2/20/01 1:13:29 PM

Larry,
You must add a D.dbReset(1008) after the
If D.dbUnit = 1008
because your Where clause has a variable in it.
Also, for testing, I suggest you set SuppressMsg=False and StatusBar=True.
Try that and see if it helps. Let me know, thanks,
Mark.


------------
Larry G at 2/20/01 12:58:37 PM

Hi,
I have a page that displays a grid and when the index is clicked through to the form page, there will be a second grid which relies on an Export field from grid 1. For some reason, grid 2 will only show the first record from the recordset and show a 1 - 1: 1 in the Status Bar while I&#39;m certain there are more than one record. I&#39;ve response.write the SQL and pasted it into a query in Access and it always returns all of the records. Why would this happen?

Thanks,
Larry

<%

ASQL = &#34;SELECT tblContacts.ContactID, tblContacts.LastName, tblContacts.FirstName, Count(tblPubs.PubID) AS PubCount &#34; & _
&#34;FROM (tblAuthors INNER JOIN tblContacts ON tblAuthors.ContactID = tblContacts.ContactID) INNER JOIN tblPubs ON tblAuthors.PubID = tblPubs.PubID &#34; & _
&#34;GROUP BY tblContacts.ContactID, tblContacts.LastName, tblContacts.FirstName&#34;

Set A = Server.CreateObject(&#34;ASP.db&#34
A.dbUnit = 1007
A.dbDSN = &#34;Epubs&#34;
A.dbMode = &#34;Both&#34;
A.dbSQL = ASQL
A.dbGridTableTag = &#34;Border=0&#34;
A.dbGridIndex = False
A.dbGridCol = &#34;3&#34;
A.dbGridInc = &#34;6&#34;
A.dbFormDisplayFlds = &#34;2,3&#34;
A.dbNavigation = &#34;None&#34;
A.dbOptions = &#34;HeaderFontTag=Face=Verdana Size=2, CellFontTag=Face=Verdana Size=2&#34;
A.dbNavigation = &#34;Top&#34;
A.dbNavigationItem=&#34;grid, form, top, prev, next, bottom, row+, row-, grid+, grid-, form+, form-, filter, resetfilter, download, color, reload&#34;
A.dbNavigationIcon=&#34;std&#34;
A.dbExportFlds = &#34;ContactID&#34;
A.dbImageDir=&#34;images/&#34;
A.dbNameMap = &#34;FirstName,Author;PubCount, Pub Count&#34;
A.dbMagicCell = &#34;FirstName,,#FirstName# #LastName#, index;PubCount,align=center&#34;
A.dbGridDisplayFlds = &#34;FirstName, PubCount&#34;
A.dbSuppressMsg = True
A.ASPdb

If A.dbLastScreen = &#34;form&#34; Then
response.write Session( &#34;ASPDB_1007_ContactID&#34; )
DSQL = &#34;SELECT tblPubs.PubID, tblPubs.Title, tblPubs.PType, tblTargets.Target, tblPubs.Status &#34; & _
&#34;FROM (tblContacts INNER JOIN (tblPubs INNER JOIN tblAuthors ON tblPubs.PubID = tblAuthors.PubID) &#34; & _
&#34;ON tblContacts.ContactID = tblAuthors.ContactID) INNER JOIN tblTargets ON tblPubs.TargetID = tblTargets.targetID &#34; & _
&#34;WHERE (((tblContacts.ContactID)=&#34; & Session( &#34;ASPDB_1007_ContactID&#34; ) & &#34)&#34;
Set D = Server.CreateObject(&#34;ASP.db&#34
D.dbUnit = 1008
D.dbDSN = &#34;Epubs&#34;
D.dbMode = &#34;Grid&#34;
D.dbSQL = DSQL
D.dbGridTableTag = &#34;Border=1&#34;
D.dbGridIndex = False
D.dbGridInc = &#34;6&#34;
D.dbGridDisplayFlds = &#34;0,1,2,3,4&#34;
D.dbOptions = &#34;HeaderFontTag=Face=Verdana Size=2, CellFontTag=Face=Verdana Size=2&#34;
D.dbNavigation = &#34;Bottom&#34;
D.dbNavigationItem=&#34;grid, top, prev, next, bottom, row+, row-, grid+, grid-, form+, form-, filter, resetfilter, download, color, reload&#34;
D.dbNavigationIcon=&#34;std&#34;
D.dbExportFlds = &#34;PubID&#34;
D.dbImageDir=&#34;images/&#34;
D.dbNameMap = &#34;PType, Pub Type&#34;
&#39;D.dbMagicCell = &#34;2,,<A HREF=pubs.asp?PUB=#(1)#>#(2)#</A>&#34;
D.dbSuppressMsg = True
D.dbstatusBar = False
D.ASPdb
response.write DSQL
End If


%>