Click here to Skip to main content
15,886,799 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hi everybody.
i made a stored procedure named "INSERTMARKS" which is like below


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


C#
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
Updated 20-Dec-12 23:44pm
v2

Perhaps, if you removed the comment markers:
C#
// cmd.Parameters.Add("@i", SqlDbType.SmallInt);
//cmd.Parameters.Add("@s", SqlDbType.SmallInt);

so it read:
C#
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?
 
Share this answer
 
v2
You have declared parameters in your stored procedure
SQL
@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
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900