Using stored procedure with parameters gives 'parameter not supplied'

Hi all,

I'm using an SqlDataSource to execute a stored procedure in a sybase database, which takes two arguements:

create procedure pGetCodes
@i_code varchar(32),
@i_code_scheme char(8)
as
begin
select
ECM2.code, ECM2.code_scheme, ECM2.source_id, ECM2.stock_exchange_id
from refdb_rep..EXTERNAL_CODE_MARKET ECM1
join refdb_rep..EXTERNAL_CODE_MARKET ECM2
on ECM1.instrument_id = ECM2.instrument_id
where ECM1.code = @i_code and ECM1.code_scheme = @i_code_scheme
order by ECM2.code_scheme
end
These arguements are supplied by a textbox and a dropdown as ControlParameters:
<asp:SqlDataSource ID="sqlCodes" runat="server" ConnectionString="<%$ ConnectionStrings:RefScapeConnectionString %>" ProviderName="<%$ ConnectionStrings:RefScapeConnectionString.ProviderName %>" SelectCommand="pGetCodes" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:ControlParameter ControlID="txtCode" Name="i_code" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="drpSchemes" Name="i_code_scheme" PropertyName="SelectedValue" Type="String" />
</SelectParameters>
</asp:SqlDataSource>

 I can't see anything wrong with the way I'm doing this, but I always get the same error:

 

ERROR [HY000] [DataDirect][ODBC Sybase Wire Protocol driver][SQL Server]Procedure pGetCodes expects parameter @i_code, which was not supplied.
ERROR [HY000] [DataDirect][ODBC Sybase Wire Protocol driver][SQL Server]Procedure pGetCodes expects parameter @i_code_scheme, which was not supplied.

Manually specifying the command seems to work perfectly:

sqlCodes.SelectCommand = String.Format("exec pGetCodes '{0}', '{1}'", txtCode.Text, drpSchemes.SelectedValue); 
Any ideas?! Thanks for any help, much appreciated :)

 
Chris 

0
andersoc_uk
11/28/2007 2:26:06 PM
asp.net.presentation-controls 72751 articles. 1 followers. Follow

7 Replies
419 Views

Similar Articles

[PageSpeed] 27

andersoc_uk:
<asp:ControlParameter ControlID="txtCode" Name="i_code" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="drpSchemes" Name="i_code_scheme" PropertyName="SelectedValue" Type="String" />

 

Try adding the @ infront of the parameter names

e.g.

            <asp:ControlParameter ControlID="txtCode" Name="@i_code" PropertyName="Text" Type="String" />
            <asp:ControlParameter ControlID="drpSchemes" Name="@i_code_scheme" PropertyName="SelectedValue"
Type="String" />

0
jimmy
11/28/2007 9:02:31 PM

 Thanks for your reply jimmyg. Had a go and I still get the same error...

 I tried starting again using the 'Configure data source wizard', and I get the same error when pressing 'Test Query'. Executing sprocs without parameters seem fine tho...any other ideas?

Thanks again,

Chris
 

0
andersoc_uk
11/29/2007 9:56:08 AM

Can the input parameters at any stage be null?

You may need to declare you input parameters = NULL in your stored procedures if they can be.

 

0
jimmy
11/29/2007 7:37:48 PM

Hi,

They should never be null but I may have found something interesting. I set a breakpoint on Page_Load after pressing my submit button, and added sqlCodes.SelectParameters to my watch list. It looks like the values aren't what I was expecting:

i_code_scheme is set to the first item in drpSchemes, which is not what I selected

i_code is null, when I definately had input in txtCode

I thought these parameters would have been set correctly by the time of Page_Load?

Thanks again,

 Chris

 

0
andersoc_uk
11/30/2007 9:41:54 AM

This is looking more likely to be an issue with the underlying (probably my ODBC driver), since the Test Query button didn't even work and it doesn't look like the parameters are even being passed. Just googling around now, but anyone know if sybase ODBC 4.2 even supports named parameters??!!

Thanks

 Chris
 

0
andersoc_uk
11/30/2007 10:05:05 AM

Well

sqlCodes.SelectCommand = String.Format("exec pGetCodes @i_code = '{0}', @i_code_scheme = '{1}'", txtCode.Text, drpScheme.Value);

works fine, maybe it's something to do with the SqlDataSource.
 

0
andersoc_uk
12/3/2007 11:57:35 AM

Found the answer: It is because I'm using ODBC, which doesn't support named parameters. The select query should read: {call pGetCodes(?,?)}, and the parameters must be passed in the correct order.

It would have saved a LOT of time if the wizard realised I was using an ODBC connetion, and had formatted it correctly for me!

0
andersoc_uk
2/19/2008 10:11:29 AM
Reply:

Similar Artilces:

Procedure or Function 'stored procedure name' expects parameter which was not supplied
Has anyone encountered this before? Procedure or Function 'stored procedure name' expects parameter '@parameter', which was not supplied. It seems that my code is not passing the parameter to the stored procedure. When I click this hyperlink: <asp:HyperLink ID="HyperLink1" Runat="server" NavigateUrl='<%# "../Division.aspx?CountryID=" + Eval("CountryID")%>' Text='<%# Eval("Name") %>' ToolTip='<%# Eval("Description") %>' CssClass='<%# Eval("CountryID").ToString() == Request.QueryString["CountryID"] ? "CountrySelected" : "CountryUnselected" %>'> </...

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...

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 '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"...

help! why i can not use ' out parameter' in 'oracle procedure'
i compile and execute the procedure in sqlplus(oracle tools) but i can not use it in powerbuilder. foe example: the procedure is : CREATE or replace PROCEDURE spm1 (dept varchar2 ,mgr_name out varchar2) IS lutype varchar2(10); BEGIN mgr_name:=dept||'out end'; END; the powerbuilder scipt is : DECLARE gg PROCEDURE FOR SPM1 (:dept1); execute gg; messagebox("",string(sqlca.sqlcode)+sqlca.sqlerrtext) i get error,and the error is : pls:00306. and oracle document explain the error is : PLS-00306: wrong number or t...

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 '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 '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...

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&...

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(...

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...

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 '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...

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, SubC...

Web resources about - Using stored procedure with parameters gives 'parameter not supplied' - asp.net.presentation-controls

Resources last updated: 12/12/2015 12:42:01 AM