bsowards
asked on
expects parameter '@ind_id', which was not supplied.
Hi All,
Trying to get my first stored procedure to work. I attempted to use microsoft's "best practices" for it, but no success so far.
Here's the stored procedure:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROC [dbo].[rp_ind_year_end]
@ind_id integer
AS
SELECT report.rp_year_end
FROM report INNER JOIN
category ON report.rp_category = category.ct_id
WHERE (category.ct_ind = @ind_id)
GROUP BY report.rp_year_end
ORDER BY report.rp_year_end DESC
Here's the code:
Set cn = Server.CreateObject("ADODB .Connectio n")
cn.Open "Driver={SQL Server};" & _
"Server=(local);" & _
"Database=KlineGroup;" & _
"Trusted_Connection=yes"
Set cmd = Server.CreateObject("ADODB .Command")
Set cmd.ActiveConnection = cn
cmd.CommandText = "rp_ind_year_end"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter("ind_i d", adInteger, adParamReturnValue)
' Set value of Param0 of the default collection to 1
cmd("Param0") = 1
cmd.Execute
rp_year_end = cmd("rp_year_end")
Here's the error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Procedure or Function 'rp_ind_year_end' expects parameter '@ind_id', which was not supplied.
/reports/reports_agpestici des_dynami c.asp, line 66
Line 66 being: rp_year_end = cmd("rp_year_end")
I'm just not sure how to get the value out I guess. Thanks!
Trying to get my first stored procedure to work. I attempted to use microsoft's "best practices" for it, but no success so far.
Here's the stored procedure:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROC [dbo].[rp_ind_year_end]
@ind_id integer
AS
SELECT report.rp_year_end
FROM report INNER JOIN
category ON report.rp_category = category.ct_id
WHERE (category.ct_ind = @ind_id)
GROUP BY report.rp_year_end
ORDER BY report.rp_year_end DESC
Here's the code:
Set cn = Server.CreateObject("ADODB
cn.Open "Driver={SQL Server};" & _
"Server=(local);" & _
"Database=KlineGroup;" & _
"Trusted_Connection=yes"
Set cmd = Server.CreateObject("ADODB
Set cmd.ActiveConnection = cn
cmd.CommandText = "rp_ind_year_end"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter("ind_i
' Set value of Param0 of the default collection to 1
cmd("Param0") = 1
cmd.Execute
rp_year_end = cmd("rp_year_end")
Here's the error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Procedure or Function 'rp_ind_year_end' expects parameter '@ind_id', which was not supplied.
/reports/reports_agpestici
Line 66 being: rp_year_end = cmd("rp_year_end")
I'm just not sure how to get the value out I guess. Thanks!
ASKER
how do i get the field?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
or... by using an output parameter...
ASKER
Got an error:
Microsoft VBScript compilation error '800a0401'
Expected end of statement
/reports/reports_agpestici des_dynami c.asp, line 58
dim p as adodb.param
Microsoft VBScript compilation error '800a0401'
Expected end of statement
/reports/reports_agpestici
dim p as adodb.param
ASKER
The Final, ASP friendly version is:
Set cn = Server.CreateObject("ADODB .Connectio n")
cn.Open "Driver={SQL Server};" & _
"Server=(local);" & _
"Database=KlineGroup;" & _
"Trusted_Connection=yes"
Set cmd = Server.CreateObject("ADODB .Command")
Set cmd.ActiveConnection = cn
cmd.CommandText = "rp_ind_year_end"
cmd.CommandType = adCmdStoredProc
dim p, rr
set p = cmd.CreateParameter("ind_i d", adInteger, adParamInput)
cmd.Parameters.Append p
p.value = 1
set rr = cmd.execute
if rr.eof and rr.bof then
'no records:
else
rp_year_end = rr.fields("rp_year_end").v alue
end if
Set cn = Server.CreateObject("ADODB
cn.Open "Driver={SQL Server};" & _
"Server=(local);" & _
"Database=KlineGroup;" & _
"Trusted_Connection=yes"
Set cmd = Server.CreateObject("ADODB
Set cmd.ActiveConnection = cn
cmd.CommandText = "rp_ind_year_end"
cmd.CommandType = adCmdStoredProc
dim p, rr
set p = cmd.CreateParameter("ind_i
cmd.Parameters.Append p
p.value = 1
set rr = cmd.execute
if rr.eof and rr.bof then
'no records:
else
rp_year_end = rr.fields("rp_year_end").v
end if
dim p as adodb.param
set p = cmd.CreateParameter("ind_i
cmd.Parameters.Append p
p.value = 1
cmd.Execute