| :: store procedure :: (6) | Post a New Message | Post a Reply |
 store procedure | Post a Reply |
from: Myrtle Creek, OR
AspWebSolution<br> Bind Line: 2<br> Display Line: 7 error '800a0009'
Subscript out of range
/ASPReport/gridqienr.asp, line 70
Line 70 is: objGrid.Display
Please help. Thanks.
 Re: store procedure | Post a Reply |
from: Washington, DC
make sure you are set the stored procedure and add the parameters as follows:
With objGrid
.SetProcedure(strProcedureName)
Call .AddParameter(strParamName, strParamValue)
Call .AddParameter(strParamName, strParamValue)
End With
-------------------------
Ghost
| Re: store procedure | Post a Reply |
from: Myrtle Creek, OR
.SetProcedure("qaur")
Call .AddParameter("param1", "200401")
Call .AddParameter("param2", "200406")
I got this err msg:
The connection cannot be used to perform this operation. It is either closed or invalid in this context.
How do I tell the grid to use command object? My original asp page would say:
Set conn = Server.CreateObject("ADODB.Connection")
conn.open strConn
Set cm = Server.CreateObject("ADODB.Command")
cm.CommandText = "qaur"
cm.CommandType = adCmdStoredProc
cm.ActiveConnection = conn
Set R = cm.Execute (RecordsAffected, Array(200401,200406), adCmdStoredProc)
Thanks for your help.
 Re: store procedure | Post a Reply |
from: Washington, DC
-------------------------
Ghost
| Re: store procedure | Post a Reply |
from: Myrtle Creek, OR
CREATE PROCEDURE qaur
@var1 VARCHAR(6),
@var2 VARCHAR(6)
AS
DECLARE @SQL NVARCHAR(200)
select P.* into #ztmp
from (SELECT yyyymm,
SUM(CASE DCO WHEN 'A' THEN Days ELSE 0 END) AS A,
SUM(CASE DCO WHEN 'B' THEN Days ELSE 0 END) AS B,
SUM(CASE DCO WHEN 'C' THEN Days ELSE 0 END) AS C,
SUM(CASE DCO WHEN 'D' THEN Days ELSE 0 END) AS D,
SUM(CASE DCO WHEN 'E' THEN Days ELSE 0 END) AS E,
SUM(CASE DCO WHEN 'F' THEN Days ELSE 0 END) AS F,
SUM(CASE DCO WHEN 'G' THEN Days ELSE 0 END) AS G,
SUM(CASE DCO WHEN 'H' THEN Days ELSE 0 END) AS H,
SUM(CASE DCO WHEN 'I' THEN Days ELSE 0 END) AS I,
SUM(CASE DCO WHEN 'J' THEN Days ELSE 0 END) AS J
FROM zQAUR
GROUP BY yyyymm
HAVING yyyymm BETWEEN @var1 AND @var2) as P
SET @SQL= 'SET ANSI_WARNINGS OFF;SELECT * FROM #ztmp;SET ANSI_WARNINGS ON;'
EXECUTE sp_executesql @SQL
GO
Thanks for your help.
| Re: store procedure | Post a Reply |
from: Laurel, MD
I couldn't re-create this error but it seems that the datagrid is either miscalculating the number of fields returned by your procedure or the number of records returned.
Can you please let me know at exactly what stage this happens, does the anything even show up? Does this procedure work from the ReportWizard?
thanx!
I think it might have something to do with trying to make a temp table and cross tab record set.
This current version of the datagrid doesn't build create temp tables, actually, it doesn't create anything (apart from the ReportsFile & Local files).
I created the table zQAUR with similar fields as the ones in your stored procedure and added sample data. - It seems to be working fine - my page script is shown below:
<% 'declare variables Dim objGrid Dim strConn 'Build a connection string to the Northwind database strConn
= "Provider=MSDASQL;Driver={SQL Server};Server=THETMAN;Database=Test;User ID=sa;Password=sa;" ' Create an instance of DataGrid Set objGrid = Server.CreateObject("AspWebSolution.DataGrid") With objGrid .SetProcedure(
"qaur") Call .AddParameter(
"var1", 200312) Call .AddParameter(
"var2", 200408) 'Create a databse connection .CreateConnection (strConn)
'add some style .GridStyle=
"background-color:gray;" .HeaderStyle=
"background-color:#666699;" .HeaderTextStyle=
"color:white;font-family:verdana;font-size:8pt;" .ItemStyle
= "background-color:#d2d1de;color:000000;font-family:verdana;font-size:8pt;" .AltItemStyle
= "background-color:#f5f5f5;color:000000;font-family:verdana;font-size:8pt;" .FooterStyle=
"font-family:verdana;font-size:8pt;" End With %> <html>
<head><title>Qaur Data</title></head>
<body scroll=auto><table><tr><td>
<% 'Display the data grid objGrid.Display
'distroy objects Set objGrid = Nothing %> </td></tr></table></body>
</html>
You can click on the link below to see the screen shot
http://www.cityjams.com/tmp/qaur.gif
-------------------------
Master of the Game