Solved

expects parameter '@ind_id', which was not supplied.

Posted on 2006-11-20
6
7,860 Views
Last Modified: 2011-08-18
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.Connection")
                                 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_id", 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_agpesticides_dynamic.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!
0
Comment
Question by:bsowards
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17980872
this should work better:
dim p as adodb.param
set p = cmd.CreateParameter("ind_id", adInteger, adParamInput)
cmd.Parameters.Append p
p.value = 1

cmd.Execute
0
 

Author Comment

by:bsowards
ID: 17980881
how do i get the field?
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 17980883
also, replace this:
 cmd.Execute
 rp_year_end = cmd("rp_year_end")

by this:

 dim r as adodb.recordset
 set r = cmd.excute
 if r.eof and r.bof then
   'no records:
 else
   rp_year_end = r.fields("rp_year_end").value

 end if  
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17980885
or... by using an output parameter...
0
 

Author Comment

by:bsowards
ID: 17980899
Got an error:

Microsoft VBScript compilation  error '800a0401'

Expected end of statement

/reports/reports_agpesticides_dynamic.asp, line 58

dim p as adodb.param
0
 

Author Comment

by:bsowards
ID: 17980956
The Final, ASP friendly version is:

Set cn = Server.CreateObject("ADODB.Connection")
                                 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_id", 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").value
                                    
                                     end if  
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article covers the basics of the Sass, which is a CSS extension language. You will learn about variables, mixins, and nesting.
I found this questions asking how to do this in many different forums, so I will describe here how to implement a solution using PHP and AJAX. The logical flow for the problem should be: Write an event handler for the first drop down box to get …
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question