Phil
|
| Posted: 07/03/2003, 1:35 PM |
|
Using an SQL 2000 stored proc as the data source for a grid, the ccs record
navigator goes wacky...the page references are not all there, and if you
click "next", the page references change.
If I the exact same SQL statements in an SQL 2000 View, it works fine. No
parameters on either datasource. Is this a known issue with ccs ver2?
|
|
|
 |
DonB
|
| Posted: 07/03/2003, 2:07 PM |
|
How did you get the datasource to be a sp? CCS does not offer them as
datasources in the wizard. So I assume you created the grid based off a
table and changed to the sp afterwards? Or you built the grid without the
wizard.
Provide some details and I'll try to duplicate the steps here to see what it
does for me. Offhand, though since the sp is not scrollable
("cursor-able"?) like a table or view, that is probably the basic reason for
your observation.
DonB
"Phil" <padams@nascar.com> wrote in message
news:be241t$92i$1@news.codecharge.com...
> Using an SQL 2000 stored proc as the data source for a grid, the ccs
record
> navigator goes wacky...the page references are not all there, and if you
> click "next", the page references change.
>
> If I the exact same SQL statements in an SQL 2000 View, it works fine. No
> parameters on either datasource. Is this a known issue with ccs ver2?
>
>
|
|
|
|
DonB
|
| Posted: 07/07/2003, 3:14 PM |
|
I have found a way to do this, if I can convince Yes to take my suggestion.
Here's what I have filed with them:
I have been looking closely at the reason that StoredProcedures are not
allowed as the datasource for a Grid control (I haven't check how the Record
or Editable Grid controls would handle a SP).
It appears that you may have not allowed a SP as the datasource for a couple
of key reasons:
Reason 1. The need for "clientside cursor" (that is, adUseClient is the
..CursorLocation property. It is not possible with the existing design of the
clsDatasource Class (in Classes.Asp) to modify the cursor location. This is
because the Recordset is created in DoOpen by "direct" execution of an ADO
Command object.
I was able to overcome this in my testing by changing one line of code:
Set DataSource.Recordset = Connection.Execute(Command)
To the following three lines of code:
Set DataSource.Recordset = CreateObject("ADODB.RecordSet")
DataSource.Recordset.CursorLocation = adUseClient
DataSource.Recordset.Open Command,, adOpenStatic
which are otherwise equivalent, only the cursor location then becomes
user-definable.
I realize that clientside cursors are frowned upon as they do not scale up
well.
Reason 2. The difficulty with implementing a workable ExecuteCount method
on the CCS Datasource class. Your design approach is to form a "SELECT
COUNT(*) FROM table" query to obtain the number of rows in the result set.
This does not work for a stored procedure.
I found that there is a RecordsCount variable in the Datasource class that
never gets set to a value. I propose that this be loaded with the
recordcount (since ADO does return it) which does two things: It makes it
unnecessary to execute the "count" SQL separately and it avoids the issue
with a stored procedure recordcount. As long as the stored procedure is
declared with the SQL Server directive "SET NOCOUNT ON", the stored
procedure will return a scrollable recordset, which means you DO get a
recordcount from it.
These observations, if used to revise CCS, would allow you to support Stored
Procedures (at least with SQL Server)
regards,
Don
|
|
|
|
|
