DeleteCommand, Stored Procedures, and ReturnValue Parameters = cant be done?

I've a SqlDataSource control that has stored procedures specified for each of its commands: SelectCommand, InsertCommand, UpdateCommand, DeleteCommand . And for Insert, Update and Delete, I've specified asp:parameters for each stored procedure's parameters. Now, my stored procedures all have return values, and I've successfully accessed the return values for Insert and Update, but for some reason, I'm getting very wrong results for Delete.

<DeleteParameters>
<asp:Parameter Name="result" Type="Int32" Direction="ReturnValue" />
<asp:Parameter Name="myID" Type="Int32" />
</DeleteParameters>

The moment I add my "result" with the direction ReturnValue, I instantly get a"Procedure or function <storedprocedurename> has too many arguments specified." error. I checked my SQL Profiler, and it seems that the page is passing result as an Input parameter, instead of keeping it as a ReturnValue! e.g.

exec spName @.myID=1,@.result=NULL

when it should be

exec spName @.myID=1

I get the correct behavior with Update and Insert, so I'm wondering whether if this is a bug or by-design behavior or something very screwy with my computer?

Help? Thoughts?

Hi there,

I hope you are using the "out" keyword for the parameter that needs to be passed out.

Why dont you specify the default value for the out parameter in the asp:Parameter list?

thanks,

Murthy here

|||

Do you mean the OUTPUT keyword for the stored procedures? I'm not using OUTPUT parameters, I'm looking for the ReturnValue. What I'm actually doing is trying to access the ReturnValue of the stored procedure, like below

create procedure spTest
@.myID int = 0As
'some sql statements herereturn 1GO

The value I am interested in is1.The problem, however, is that my parameter, even though it's specified as a ReturnValue, it seems to act as an Input value regardless. And it end up passing the parameter into the stored procedure when it's not supposed to at all.

However, if I'm misunderstanding your solution: where exactly should I be using this keyword?

|||

Ok are you running the stored procedure manually?

If yes, you can check the number of rows affected as:

int status=command1.ExecuteNonQuery().

and then if the status is -1 then no rows have been affected else count is greater than 0.

You do not have to explicitly add a return value to check the status of the stored procedure. Hope I am clear.\

thanks,

Murthy here

|||

No, I'm not running the stored procedure manually. Yes, I know I am able to get the status (although your example returns rows affected, right?) if I were. [The reason why I'm checking the returnvalue is because there are various reasons why the process may fail, and in each instance, I return a different value to depict it.]

My question/problem is that setting theReturnValue parameters for theSQLDataSource control'sDELETE command does NOT seem to work (although I feel it should); instead it seems to ignore the direction and sets it to Input. I'm half-convinced this is a bug-- so I've moved on to using ObjectDataSource instead.

|||

Whatever works for you,

Murthy here

|||

Hi Jnghh,

You may visit the link http://forums.asp.net/thread/1670367.aspx which has the solution to these similar problems.

Hope it helps.

Thanks.

Delete problem-too many parameters

I have a dataview control with the delete method pointing to a logical delete stored procedure in SQL SERVER Express. I am getting an error message saying too many parameters provided. I've check and there is one parameter expected and one passed in. This is my SP, the html, and the debug infor I'm looking at. Any ideas?

PROCEDUREdbo.usp_Drivers_Delete

@.mintDriver_IDint

AS

UPDATEtblDrivers

SETActive= 0

WHEREDriver_ID=@.mintDriver_ID

html:

<DeleteParameters>

<asp:ControlParameterControlID="GridView1"Name="mintDriver_ID"PropertyName="SelectedValue"

Type="Int32"/>

</DeleteParameters>

Debug:

?SqlDataSourceDrivers.DeleteParameters(0)

{System.Web.UI.WebControls.ControlParameter}

System.Web.UI.WebControls.ControlParameter: {System.Web.UI.WebControls.ControlParameter}

ConvertEmptyStringToNull: True

DefaultValue: Nothing

Direction: Input {1}

Name: "mintDriver_ID"

Size: 0

Type: Int32 {9}

?SqlDataSourceDrivers.DeleteParameters.Count

1

Error:

Exception Details:System.Data.SqlClient.SqlException: Procedure or function usp_Drivers_Delete has too many arguments specified.

Perhaps I'm missing something, but you don't really have a delete. You have an update that sets a status column (Active) to 0. I think if you try changing the <DeleteParameters> to <UpdateParameters> you should be fine.

Deb