Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: ASP.NET C#4.0
hi everybody.
i made a stored procedure named "INSERTMARKS" which is like below
 

USE [School]
GO
/****** Object:  StoredProcedure [dbo].[INSERTMARKS]    Script Date: 12/21/2012 13:57:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		VARUN
-- Create date: 21/12/2012
-- Description:	
-- =============================================
ALTER PROCEDURE [dbo].[INSERTMARKS] 
	-- Add the parameters for the stored procedure here
	@name varchar(50),
	@sub varchar (50),
	@m tinyint,
	@i smallint output,
	@s smallint output
	
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 @i=Roll_no from student where Name=@name;
	select @s=Subject_ID from subject where subject_name=@sub;
	insert into marks values(@i,@s,@m);
END

The procedure is working fine in sql server.but when i am trying to execute it via c# code.Its showing an error.The code is below:
 
error:::Procedure or function 'INSERTMARKS' expects parameter '@i', which was not supplied.
 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
 

public partial class marks : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
 
    }
    protected void Button2_Click(object sender, EventArgs e)
    {
        Response.Redirect("Default.aspx");
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        string conn = "Data Source=.;Initial Catalog=School;Integrated Security=True";
        SqlConnection con = new SqlConnection(conn);
        con.Open();
        SqlCommand cmd = new SqlCommand("INSERTMARKS",con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "INSERTMARKS";
        cmd.Parameters.Add("@name",SqlDbType.VarChar).Value=DropDownList1.SelectedValue.Text;
       cmd.Parameters.Add("@sub",SqlDbType.VarChar).Value=DropDownList2.SelectedValue;
        cmd.Parameters.Add("@m",SqlDbType.TinyInt).Value=TextBox3.Text;
       // cmd.Parameters.Add("@i", SqlDbType.SmallInt);
        //cmd.Parameters.Add("@s", SqlDbType.SmallInt);
        cmd.ExecuteNonQuery();
        con.Close();
 
        Response.Redirect("Default.aspx");
        
    }
}
Posted 20-Dec-12 22:07pm
Edited 20-Dec-12 23:44pm
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Perhaps, if you removed the comment markers:
// cmd.Parameters.Add("@i", SqlDbType.SmallInt);
//cmd.Parameters.Add("@s", SqlDbType.SmallInt);
so it read:
cmd.Parameters.Add("@i", SqlDbType.SmallInt).VAlue = myIParam;
cmd.Parameters.Add("@s", SqlDbType.SmallInt).Value = mySParam;
Perhaps it wouldn't complain that you aren't providing the parameters you told it it needed?
  Permalink  
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

You have declared parameters in your stored procedure
@i smallint output,
@s smallint output
 
and you are not passing values for both parameters and the SQL is expecting both parameters. so, pass values for both parameter.
 
Thanks
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web03 | 2.8.140926.1 | Last Updated 21 Dec 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100