Procedure or Function 'retrieve_product' expects parameter '@productPrice', which was not supplied.

Hi i am getting this error whenever i try to execute a stored procedure, below is my stored procedure;

 

ALTER PROCEDURE [dbo].[retrieve_product]

-- Add the parameters for the stored procedure here

@productPrice decimal (10,2),

@productInfoURL varchar (255),

@categoryName varchar (255),

@companyName varchar (255),

@subCategoryName varchar (255),

@companyWebsiteURL varchar (255)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

SELECT Categories.categoryName, SubCategories.subCategoryName,

Companies.companyName, Products.productPrice,

Products.productInfoURL FROM Categories

INNER JOIN Products ON Categories.categoryID = Products.categoryID

INNER JOIN Companies ON Products.companyID = Companies.companyID

INNER JOIN SubCategories ON Categories.categoryID = SubCategories.categoryID AND Products.subcategoryID = SubCategories.subCategoryID

END

 

0
PeterNwan
11/11/2007 6:32:43 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

8 Replies
678 Views

Similar Articles

[PageSpeed] 15

Hi there. there. Try enclosing the whole parameters declaration inside a ()

It should be like

USE dbname - - i normally add this line JUST IN CASE

ALTER PROCEDURE [dbo].[retrieve_product]

  ( -- the missing element  

                   @productPrice decimal (10,2),

                   @productInfoURL varchar (255),

                   @categoryName varchar (255),

                   @companyName varchar (255),

                   @subCategoryName varchar (255),

                  @companyWebsiteURL varchar (255)

      ) - - The ther missing element

AS

 - - - - - - - - - - - - - - - - - -


Do not forget to mark as answer the post(s) that(really)answered your question !!
0
status_quo
11/11/2007 7:43:30 PM

You've declared a whole bunch of parameters at the beginning of the procedure, but you don't use them anywhere in the SQL.  I suspect that you are not passing any parameter values either.  If you create parameters, you have to use them. Since this procedure doesn't need them, delete them.

 


Regards Mike
[MVP - ASP/ASP.NET]
My site
0
Mikesdotnetting
11/11/2007 7:46:25 PM

Hi thanks for the responses, this stored procedure should take values from a check box, how should i structure it?

0
PeterNwan
11/11/2007 8:10:58 PM

That depends on what the values are supposed to do. Are they part of some kind of search function, where users can select what they want the results to be filtered by?  Are you expecting all of the parameters to contain some kind of value?  Might some of the values be NULL (ie not selected in the checkbox)?

First thing you need is a WHERE clause so that you can tell Sql Server which fields are to be used as filter criteria.  If you can describe exactly what you want to achieve, I'm sure we can move forward from there.

 


Regards Mike
[MVP - ASP/ASP.NET]
My site
0
Mikesdotnetting
11/11/2007 8:33:05 PM

Hi thanks for taking the time out to respond i appreciate it, I have a page which has a category of product listed, on this page there are 3 checkboxes and a search button, these checkboxes can be clicked but its upto the user if they want to search by that criteria, however if it is i want the checkbox to influence the stored procedure if you know what i mean. Thank you

0
PeterNwan
11/11/2007 8:50:42 PM

You need to pass the parameter values into the stored procedure, and you should only include parameters that will be used in it.  Since you have parameters that are optional, they will all need to be passed, but some of them will be NULL.  This is one approach that works in terms of the SQL: http://steve.emxsoftware.com/Optional+Parameters+in+SQL+Server+Search+Queries

I don't know how you are performing data access, but if you are using a SqlDataSource control, you will need to go to its properties and change CancelSelectOnNullParameter to false.  It is true by default. 

If you are still stuck, show the relevant aspx code.  Just the bit that pertains to the search function.  You can safely omit 253 lines of GridView code. Big Smile

 


Regards Mike
[MVP - ASP/ASP.NET]
My site
0
Mikesdotnetting
11/11/2007 9:02:46 PM

Ok i understand the logic in it  now, thanks im a newbie so still learning, thank you very much,  as it stands right now i will work on trying to get the stored procedure righ. Is it ok if i leave this unresolved for a couple more hours in order for me to get the stored procedure and Gridview sorted out, thank you

0
PeterNwan
11/11/2007 10:12:59 PM

PeterNwan:

Is it ok if i leave this unresolved for a couple more hours in order for me to get the stored procedure and Gridview sorted out, thank you

 

It's your thread. You put it at any status you want to whenever you like. Big Smile

PS.  It's 10.30pm here, and I'm off to get some beauty sleep shortly, so if you post back in the next few hours, all you will get back from me is Zzzzzzzzz.

 


Regards Mike
[MVP - ASP/ASP.NET]
My site
0
Mikesdotnetting
11/11/2007 10:31:15 PM
Reply:

Similar Artilces:

''''''''''''''''''''
Name: haznen Email: haznenatyahoodotcom Product: Gran Paradiso Alpha 8 Summary: '''''''''''''''''''' Comments: '''''''''''''''''''''''''''''''''''' Browser Details: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9a1) Gecko/20061204 UGES/1.7.2.0 GranParadiso/3.0a1 From URL: http://www.mozilla.org/projects/granparadiso/ Note to readers: Hendrix gives...

Procedure or Function 'SubscribeToNewsletters' expects parameter '@emailAddress', which was not supplied.
Hi i have been trying to insert some values selected in a checkbox list into the database, however it is giving me the following error "Procedure or Function 'SubscribeToNewsletters' expects parameter '@emailAddress', which was not supplied. "public void Page_Load(object sender, EventArgs e) -- on page load the checkbox list is populated through stored procedure (stream_NewsletterTypes) { try {SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["streamConnectionString"].ConnectionString); SqlCommand command = new SqlCommand(...

Procedure or function 'selectFromView' expects parameter '@Year', which was not supplied. ???
 hay all, i'm trying to pass parameters to a stored procedure ..and i keep getting this error "Procedure or function 'selectFromView' expects parameter '@Year', which was not supplied." this is the procedure implementation : ALTER PROCEDURE dbo.selectFromView @Year as varchar(10), @Country as varchar(10), @Family as varchar(10), @Manu as varchar(10), @Status as varchar(10), @Type as varchar(10), @Operator as varchar(10) AS SELECT * FROM ViewofAll WHERE ProductionYear = @Year...

Procedure or Function 'UpdateContactInfoProc' expects parameter '@SUBURB', which was not supplied.
I tried to use this update stored procedure from a objectdatasource which calls the method "UpdateCompanyInfo", however, once I leave an item to be empty I got the error as title.My stored procedure:ALTER PROCEDURE dbo.UpdateContactInfoProc@Contact_ID int,@Contact_Type_ID int,@Name nvarchar(100),@TEL nvarchar(50),@EMAIL nvarchar(50),@ADDRESS nvarchar(200),@SUBURB nvarchar(50),@FAX nvarchar(50),@STATE nvarchar(50),@POSTCODE nvarchar(50),@COUNTRY nvarchar(50),@Description textASUpdate Contactset Contact_Type_ID = @Contact_Type_ID, [Name] = @Name, TEL = @TEL, EMAIL = @EMAIL, ADDRESS=@...

Procedure or Function 'syl_EmailQueueInsert' expects parameter '@EmailCC', which was not supplied.
We have a email table in the database (SQL Server 2005). When we try to execute the Insert Stored Proc in the database to add a record and leave the EmailCC and EmailBCC fields blank we are getting the following error: Msg 201, Level 16, State 4, Procedure syl_EmailQueueInsert, Line 0Procedure or Function 'syl_EmailQueueInsert' expects parameter '@EmailCC', which was not supplied. The EmailCC and EmailBCC are set to allow nulls. Shouldn’t the system automatically insert nulls into those colums if no value is supplied??? What am I doing wrong? Newbie I a...

'''''
Name: mario Email: ramar17atfastwebnetdotit Product: Gran Paradiso Alpha 2 Summary: ''''' Comments: K: Browser Details: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9a2) Gecko/20070206 GranParadiso/3.0a2 ...

Procedure 'x' expects parameter '@y' which was not supplied.
I'm trying to update a member's record via stored procedure. The procedure is thus: ********************************* CREATE PROCEDURE upd_MemberProfile( @MemberID VarChar(10), @FirstName VarChar(20), @LastName VarChar(20), @Address VarChar(50), @City VarChar(40), @StateID Char(2), @ZipCode Char(5), @Email VarChar(30), @PayPalSubscrID VarChar(22) ) AS UPDATE Members SET FirstName = @FirstName, LastName = @LastName, Address = @Address, City = @City, StateID = @StateID, ZipCode = @ZipCode, Email = @Email, PayPalSubscrID = @PayPalSubscrID WHERE Me...

Procedure 'CRMC_ProductDetails' expects parameter '@Color', which was not supplied
I keep getting this error when I browse the productsdetail.aspx page... I am using the C# SDK version of the Commerce kit... I have added a new col in th eDB called "Color", and *thought* that I had fixed the strored proc, and the product.cs file... I can get the color from the db to display on the productslist.aspx page fine... Any ideas? Did you modify the ProductDetails GetProductDetails function in productsdb.cs?...

Procedure 'JaiDeleteUser' expects parameter '@user_name', which was not supplied
<html xmlns="http://www.w3.org/1999/xhtml"> <head id="Head1" runat="server"><title>Untitled Page</title> </head> <body>   <form id="form1" runat="server"> <asp:ScriptManager ID="ScriptManager1" runat="server"> </asp:ScriptManager>   <div>   <br /> <asp:Label ID="lblUserInfo" runat="server" Text="Label"></asp:Label><asp:SqlDataSource ID="SqlDataSource1" runat="server"...

Procedure 'Create_User' expects parameter '@Password', which was not supplied.
Hi AllGetting Procedure 'Create_User' expects parameter '@Password', which was not supplied errorI can't register new user in role based system app, the following code is from register.aspx: private void Submit_Click(object sender, System.EventArgs e) { User user; if (Context.User.Identity.IsAuthenticated) user = new User(((SiteIdentity)User.Identity).UserID); else user = new User(); user.FullName = FullName.Text; user.Email = Email.Text; user.Password = Password.Text; user.Biography = Biography.Text; if (Context.User.Identity.IsAuthenticated) { user.Update(); Response.Redirec...

Procedure 'sp03_Delete_Usuario' expects parameter '@AN001_CDUSUARIO', which was not supplied.
I´m having problem to delete/update using stored procedure with gridview. Actually I made an update and delete work but just by itself not togheter. I made update ONLY works If I dont pass any fields from gridview to datakeynames and the delete show the following error: Procedure 'sp03_Delete_Usuario' expects parameter '@AN001_CDUSUARIO', which was not supplied. But the delete ONLY works if I pass all the fields form gridview to datakeynames. So the update stops to work. The code is:<%@ Page Language="VB" AutoEventWireup="false" CodeF...

Procedure 'sp_GetEEByDept' expects parameter '@tkdept', which was not supplied.
I have a basic vb.net form with a few text fields and dropdown boxes.  In one of the dropdown boxes, I ask the user to select a department.  After the user selects the department, I want to fill another dropdown box with employees that belong to the selected dept.  I keep getting an error for my procedure that is to get the employees, because I can't figure out how to pass in the department selected in the first drop down box.  ARGH... Also, do I need to set the employee dropdown box visible to false until the first dropdown selection is made, otherwise, it'd be b...

Error--Procedure 'SP_Insert_NewPipeLine' expects parameter '@CompanyId', which was not supplied.
Can any body help me in solving this problem. First I use to get Error which reads "Object Must Implement Iconvertible" After using the overloaded Sp.paramerers.add() function It started giving this problem. I am giving the sample code. ConObject = new SqlConnection(ConString); ConObject.Open(); string SpString ="dbo.SP_Insert_NewPipeLine"; SqlCommand CmdObject = new SqlCommand(SpString,ConObject); CmdObject.CommandType = CommandType.StoredProcedure; CmdObject.Parameters.Add("@RequestTypeId",SqlDbType.Int,4,"0"); //CmdObject.Parameters["@RequestTypeId"].Value= 0; CmdObject.Parame...

SQL_Error Procedure 'DelectHostName' expects parameter '@Host', which was not supplied.
How do I supply the parameter @Host?  Below is the part of my code that has the call to the stored procedure  public void DeleteHostName() { // start of the method using (SqlConnection cn = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]))            {                 cn.Open();                SqlCommand cmd = new SqlCommand("hnDeleteHost&...

Web resources about - Procedure or Function 'retrieve_product' expects parameter '@productPrice', which was not supplied. - asp.net.sql-datasource

Resources last updated: 12/8/2015 5:33:36 AM