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 Trailing ''\''

Hi,

I need to delete trailing slashes ('\') from values in a specified column. Something like what TRIM does for spaces, I want to do it for trailing slashes. I have column values such as Rajat\, Rajneesh, Ankush\, Sudheer ... etc. As a result, I need to have the column values as Rajat, Rajneesh, Ankush, Sudheer ...

Hope the question is clear. Please help me at the earliest. Thanks a lot in advance.

If you know that you only have 1 trailing slash then you could use:

Code Snippet

SELECT LEFT(NameCol, LEN(NameCol)-1)

or if you know you want to trim everything after/including the slash then:

Code Snippet

SELECT LEFT(NameCol, PATINDEX('%/', NameCol)-1)

HTH!

|||

To achieve this, you can use reverse to reverse the value and check the first value for a \

create table test
(
value varchar(20)
)
insert into test
select 'Rajat\'
union all
select 'Rajneesh'
union all
select 'Ankush\'
union all
select 'Sudheer'
go
select case when left(reverse(value),1) = '\'
then left(value,len(value) - 1) else value end
from test

Returns:

--
Rajat
Rajneesh
Ankush
Sudheer

Delete Trailing ''\''

Hi,

I need to delete trailing slashes ('\') from values in a specified column. Something like what TRIM does for spaces, I want to do it for trailing slashes. I have column values such as Rajat\, Rajneesh, Ankush\, Sudheer ... etc. As a result, I need to have the column values as Rajat, Rajneesh, Ankush, Sudheer ...

Hope the question is clear. Please help me at the earliest. Thanks a lot in advance.

If you know that you only have 1 trailing slash then you could use:

Code Snippet

SELECT LEFT(NameCol, LEN(NameCol)-1)

or if you know you want to trim everything after/including the slash then:

Code Snippet

SELECT LEFT(NameCol, PATINDEX('%/', NameCol)-1)

HTH!

|||

To achieve this, you can use reverse to reverse the value and check the first value for a \

create table test
(
value varchar(20)
)
insert into test
select 'Rajat\'
union all
select 'Rajneesh'
union all
select 'Ankush\'
union all
select 'Sudheer'
go
select case when left(reverse(value),1) = '\'
then left(value,len(value) - 1) else value end
from test

Returns:

--
Rajat
Rajneesh
Ankush
Sudheer

Delete Trailing ''\''

Hi,

I need to delete trailing slashes ('\') from values in a specified column. Something like what TRIM does for spaces, I want to do it for trailing slashes. I have column values such as Rajat\, Rajneesh, Ankush\, Sudheer ... etc. As a result, I need to have the column values as Rajat, Rajneesh, Ankush, Sudheer ...

Hope the question is clear. Please help me at the earliest. Thanks a lot in advance.

If you know that you only have 1 trailing slash then you could use:

Code Snippet

SELECT LEFT(NameCol, LEN(NameCol)-1)

or if you know you want to trim everything after/including the slash then:

Code Snippet

SELECT LEFT(NameCol, PATINDEX('%/', NameCol)-1)

HTH!

|||

To achieve this, you can use reverse to reverse the value and check the first value for a \

create table test
(
value varchar(20)
)
insert into test
select 'Rajat\'
union all
select 'Rajneesh'
union all
select 'Ankush\'
union all
select 'Sudheer'
go
select case when left(reverse(value),1) = '\'
then left(value,len(value) - 1) else value end
from test

Returns:

--
Rajat
Rajneesh
Ankush
Sudheer

Delete Records!

Hi,
I am trying to delete records from a table when a specified column encounters null values in them. Could you please let me know how!
ThanksRefer to your other post (http://www.dbforums.com/t926583.html) in this forum.

You can use DELETE FROM tablename WHERE Columnname=null and refer to books online for more information.|||columnname is null

instead of columnname = null|||Oh yeah, thanks for the make-up.