Add Stored Procedure Parameter into sqlDataSource, msSQL

Question

I want to insert a value by using stored procedure in mssql. This is my stored procedure :

ALTER PROCEDURE dbo.insertNewMember 
(@name varchar(30), 
@age int )
AS
BEGIN 
    INSERT INTO member(memId, name, age) VALUES ('', @name, @age);
END

My C# code, to use this procedure:

addStaffSql.InsertCommand = "insertNewMember";
addStaffSql.InsertCommandType = SqlDataSourceCommandType.StoredProcedure;
addStaffSql.InsertParameters.Add("@name", name);
addStaffSql.InsertParameters.Add("@age", age);
addStaffSql.Insert();

But I got the following error when I run the program:

Procedure or function 'insertNewMember' expects parameter '@name', which was not supplied.

If you have got any idea, I will appreciate.

score 3 · Accepted Answer · answered Apr 25 '12 at 21:02

3

Try losing the "@" symbol in the Add() methods.

addStaffSql.InsertCommand = "insertNewMember";
addStaffSql.InsertCommandType = SqlDataSourceCommandType.StoredProcedure;
addStaffSql.InsertParameters.Add("name", name);
addStaffSql.InsertParameters.Add("age", age);
addStaffSql.Insert();

answered Apr 25 '12 at 21:02

Jason

1,325
6
14

Due to this solution, this error message is given : "String or binary data would be truncated. The statement has been terminated." – Shnkc Apr 27 '12 at 13:57
@Shnkc Your SP shows that the data type for "name" is varchar(30), which could be what's causing the problem. Check the length of the string that you're passing to the procedure. – Jason Apr 27 '12 at 14:07

score 0 · Answer 2 · answered Apr 25 '12 at 19:58

0

I guess you are passing null in the variable called name. You may want to change your code for that.

if(name!=null)
{
   addStaffSql.InsertCommand = "insertNewMember";
   addStaffSql.InsertCommandType = SqlDataSourceCommandType.StoredProcedure;
   addStaffSql.InsertParameters.Add("@name", name);
   addStaffSql.InsertParameters.Add("@age", age);
   addStaffSql.Insert();
}
else
{
  //Send message back to user that name is not filled properly !
}

answered Apr 25 '12 at 19:58

Shyju

214,206
104
411
497

I check all the variables. If I use this query : "addStaffSql.InsertCommand = "INSERT INTO member(name, age) VALUES('" + name + "' , '" + age + "')";", it is successfully evaluated. But I need to do it with stored procedure which I couldn't have succeeded yet – Shnkc Apr 25 '12 at 20:08

Add Stored Procedure Parameter into sqlDataSource, msSQL

2 Answers2