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.

Jason · Accepted Answer · 2012-04-25 21:02:42Z

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, 2012 at 21:02

Jason

1,3256 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Shnkc Over a year ago

Due to this solution, this error message is given : "String or binary data would be truncated. The statement has been terminated."

Jason Over a year ago

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

Shyju · Accepted Answer · 2012-04-25 19:58:56Z

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, 2012 at 19:58

Shyju

220k106 gold badges420 silver badges499 bronze badges

1 Comment

Shnkc Over a year ago

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

Collectives™ on Stack Overflow

Add Stored Procedure Parameter into sqlDataSource, msSQL

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related