Help with SP - what exactly is RETURN_VALUE?

D

D. Shane Fowlkes

I'm not new to ASP or MSSQL but fairly new to using Stored Procedures. I've
got a T-SQL book on order from Amazon but it's still not here.

I have a SP which updates a record. As far as I can tell, the SP is fine.
It checks OK for syntax and when I recreate the update statement in Query
Analyzer, it seems to do fine. However, when I debug the SP in QA and enter
in all the params, it returns "@RETURN_VALUE = 0". I assume this means 0
rows updated? If so, why? I can't seem to find anything wrong with my SP.

Below is my code from MSSQL and my .NET page. Any pointers? Advice? Thanks
guys!!!



MY SP from QA:

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO


ALTER PROCEDURE spOLGA3_UpdatePerfData
(@intID Int,
@strSystemName Nvarchar(100),
@strFYEnding Nvarchar(15),
@intPopulation Int,
@mnyTotOpExp Money,
@mnyTotOpRev Money,
@intDriversPT Int,
@intDriversFT Int,
@intMaintPT Int,
@intMaintFT Int,
@intAdminPT Int,
@intAdminFT Int,
@intOtherPT Int,
@intOtherFT Int)

AS

UPDATE OLGA3_PerformanceData SET
TransitSystemName = @strSystemName,
FiscalYearEnding = @strFYEnding,
Population = @intPopulation,
TotalExpense = @mnyTotOpExp,
TotalRevenue = @mnyTotOpRev,
DriversPT = @intDriversPT,
DriversFT = @intDriversFT,
MaintPT = @intMaintPT,
MaintFT = @intMaintFT,
AdminPT = @intAdminPT,
AdminFT = @intAdminFT,
OthersPT = @intOtherPT,
OthersFT = @intOtherFT
WHERE ID = @intID

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO



My .asp.net page:


cmdInsert = New SqlCommand("spOLGA3_InsertPerfModeData", objConnection)

cmdInsert.CommandType = CommandType.StoredProcedure

cmdInsert.Parameters.AddWithValue("@intPerfDataID", intHeaderID)

cmdInsert.Parameters.AddWithValue("@intTransitMode", intModeID)

cmdInsert.Parameters.AddWithValue("@strOtherDesc", strOtherDescription)

cmdInsert.Parameters.AddWithValue("@mnyExpense", decExpense)

cmdInsert.Parameters.AddWithValue("@mnyRevenue", decRevenue)

cmdInsert.Parameters.AddWithValue("@intRevMiles", intRevMiles)

cmdInsert.Parameters.AddWithValue("@intRevHours", intRevHours)

cmdInsert.Parameters.AddWithValue("@intPassTrips", intPassTrips)

cmdInsert.Parameters.AddWithValue("@intPassMiles", intPassMiles)

cmdInsert.Parameters.AddWithValue("@intVehicles", intVehicles)

cmdInsert.Parameters.AddWithValue("@intAccidents", intAccidents)

cmdInsert.Parameters.AddWithValue("@intInjuries", intInjuries)

cmdInsert.Parameters.AddWithValue("@intFatalities", intFatalities)

cmdInsert.Parameters.AddWithValue("@mnyAdultFare", decAdultFare)

cmdInsert.Parameters.AddWithValue("@mnyAdultFareNP", decAdultFareNP)

cmdInsert.Parameters.AddWithValue("@mnySeniorFare", decSeniorFare)

cmdInsert.Parameters.AddWithValue("@mnySeniorFareNP", decSeniorFareNP)

cmdInsert.Parameters.AddWithValue("@mnyStudentFare", decStudentFare)

cmdInsert.Parameters.AddWithValue("@mnyStudentFareNP", decStudentFareNP)

cmdInsert.Parameters.AddWithValue("@mnySpecialFare", decSpecialFare)

cmdInsert.Parameters.AddWithValue("@mnySpecialFareNP", decSpecialFareNP)



Try

objConnection.Open()

strResponse = cmdInsert.ExecuteNonQuery()

strResponse = "OK"

.....etc.......
 
D

D. Shane Fowlkes

My apologies. I posted the wrong code from my asp page. Here is the actual
call to the SP:

cmdUpdate = New SqlCommand("spOLGA3_UpdatePerfData", objConnection)

cmdUpdate.CommandType = CommandType.StoredProcedure

cmdUpdate.Parameters.AddWithValue("@intID", intHeaderID)

cmdUpdate.Parameters.AddWithValue("@strSystemName", strSystemName)

cmdUpdate.Parameters.AddWithValue("@strFYEnding", strFYEnding)

cmdUpdate.Parameters.AddWithValue("@intPopulation", intPopulation)

cmdUpdate.Parameters.AddWithValue("@mnyTotOpExp", decTotalOpExp)

cmdUpdate.Parameters.AddWithValue("@mnyTotOpRev", decTotalOpRev)

cmdUpdate.Parameters.AddWithValue("@intDriversPT", intDriversPT)

cmdUpdate.Parameters.AddWithValue("@intDriversFT", intDriversFT)

cmdUpdate.Parameters.AddWithValue("@intMaintPT", intMaintPT)

cmdUpdate.Parameters.AddWithValue("@intMaintFT", intMaintFT)

cmdUpdate.Parameters.AddWithValue("@intAdminPT", intAdminPT)

cmdUpdate.Parameters.AddWithValue("@intAdminFT", intAdminFT)

cmdUpdate.Parameters.AddWithValue("@intOtherPT", intOtherFT)

cmdUpdate.Parameters.AddWithValue("@intOtherFT", intOtherFT)

Try

objConnection.Open()

cmdUpdate.ExecuteNonQuery()

strResponse = "OK"
 
M

Mark Rae

My apologies. I posted the wrong code from my asp page. Here is the
actual call to the SP:

A couple of things:

1) Forget about the return value for a minute - does the data actually get
updated...?

2) ExecuteNonQuery() returns an integer, but it looks like you're expecting
it to return a string...
 
R

Rod Barrand

No error returned

D. Shane Fowlkes said:
I'm not new to ASP or MSSQL but fairly new to using Stored Procedures. I've
got a T-SQL book on order from Amazon but it's still not here.

I have a SP which updates a record. As far as I can tell, the SP is fine.
It checks OK for syntax and when I recreate the update statement in Query
Analyzer, it seems to do fine. However, when I debug the SP in QA and enter
in all the params, it returns "@RETURN_VALUE = 0". I assume this means 0
rows updated? If so, why? I can't seem to find anything wrong with my SP.

Below is my code from MSSQL and my .NET page. Any pointers? Advice? Thanks
guys!!!



MY SP from QA:

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO


ALTER PROCEDURE spOLGA3_UpdatePerfData
(@intID Int,
@strSystemName Nvarchar(100),
@strFYEnding Nvarchar(15),
@intPopulation Int,
@mnyTotOpExp Money,
@mnyTotOpRev Money,
@intDriversPT Int,
@intDriversFT Int,
@intMaintPT Int,
@intMaintFT Int,
@intAdminPT Int,
@intAdminFT Int,
@intOtherPT Int,
@intOtherFT Int)

AS

UPDATE OLGA3_PerformanceData SET
TransitSystemName = @strSystemName,
FiscalYearEnding = @strFYEnding,
Population = @intPopulation,
TotalExpense = @mnyTotOpExp,
TotalRevenue = @mnyTotOpRev,
DriversPT = @intDriversPT,
DriversFT = @intDriversFT,
MaintPT = @intMaintPT,
MaintFT = @intMaintFT,
AdminPT = @intAdminPT,
AdminFT = @intAdminFT,
OthersPT = @intOtherPT,
OthersFT = @intOtherFT
WHERE ID = @intID

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO



My .asp.net page:


cmdInsert = New SqlCommand("spOLGA3_InsertPerfModeData", objConnection)

cmdInsert.CommandType = CommandType.StoredProcedure

cmdInsert.Parameters.AddWithValue("@intPerfDataID", intHeaderID)

cmdInsert.Parameters.AddWithValue("@intTransitMode", intModeID)

cmdInsert.Parameters.AddWithValue("@strOtherDesc", strOtherDescription)

cmdInsert.Parameters.AddWithValue("@mnyExpense", decExpense)

cmdInsert.Parameters.AddWithValue("@mnyRevenue", decRevenue)

cmdInsert.Parameters.AddWithValue("@intRevMiles", intRevMiles)

cmdInsert.Parameters.AddWithValue("@intRevHours", intRevHours)

cmdInsert.Parameters.AddWithValue("@intPassTrips", intPassTrips)

cmdInsert.Parameters.AddWithValue("@intPassMiles", intPassMiles)

cmdInsert.Parameters.AddWithValue("@intVehicles", intVehicles)

cmdInsert.Parameters.AddWithValue("@intAccidents", intAccidents)

cmdInsert.Parameters.AddWithValue("@intInjuries", intInjuries)

cmdInsert.Parameters.AddWithValue("@intFatalities", intFatalities)

cmdInsert.Parameters.AddWithValue("@mnyAdultFare", decAdultFare)

cmdInsert.Parameters.AddWithValue("@mnyAdultFareNP", decAdultFareNP)

cmdInsert.Parameters.AddWithValue("@mnySeniorFare", decSeniorFare)

cmdInsert.Parameters.AddWithValue("@mnySeniorFareNP", decSeniorFareNP)

cmdInsert.Parameters.AddWithValue("@mnyStudentFare", decStudentFare)

cmdInsert.Parameters.AddWithValue("@mnyStudentFareNP", decStudentFareNP)

cmdInsert.Parameters.AddWithValue("@mnySpecialFare", decSpecialFare)

cmdInsert.Parameters.AddWithValue("@mnySpecialFareNP", decSpecialFareNP)



Try

objConnection.Open()

strResponse = cmdInsert.ExecuteNonQuery()

strResponse = "OK"

....etc.......
 
B

Bruce Barker

all sqlserver stored procs are integer functions. take the following proc

create procedure myProc @a int
as
select @a as "foo"
return 1


and can be executed the following way

declare @return_value int
exec @return_value = myproc

@return_value will be one, and the value of @a will be returned in the
result set. note, the name @return_value is arbitrary. to get from ado.net,
declare a parameter of Diection ReturnValue. also note, that the return
value is not avaiable until all result sets have been processed
(ExecuteNonQuery will do this.)


-- bruce (sqlwork.com)
 
D

D. Shane Fowlkes

Nope. Nothing gets updated.

The function itself which calls the SP returns a string to another sub. The
function called "strResponse" returns either "OK" or the actual error. It's
just how I do it....
 
D

D. Shane Fowlkes

I just double checked. Nothing is getting updated yet I'm positive the PK
is correct. When I recreate the same UPDATE statement in QA, it works fine.
Is there something wrong with this SP?


CREATE PROCEDURE spOLGA3_UpdatePerfData
(@intID Int,
@strSystemName Nvarchar(100),
@strFYEnding Nvarchar(15),
@intPopulation Int,
@mnyTotOpExp Money,
@mnyTotOpRev Money,
@intDriversPT Int,
@intDriversFT Int,
@intMaintPT Int,
@intMaintFT Int,
@intAdminPT Int,
@intAdminFT Int,
@intOtherPT Int,
@intOtherFT Int)

AS

UPDATE OLGA3_PerformanceData SET
TransitSystemName = @strSystemName,
FiscalYearEnding = @strFYEnding,
Population = @intPopulation,
TotalExpense = @mnyTotOpExp,
TotalRevenue = @mnyTotOpRev,
DriversPT = @intDriversPT,
DriversFT = @intDriversFT,
MaintPT = @intMaintPT,
MaintFT = @intMaintFT,
AdminPT = @intAdminPT,
AdminFT = @intAdminFT,
OthersPT = @intOtherPT,
OthersFT = @intOtherFT
WHERE ID = @intID


GO
 
M

Mark Rae

I just double checked. Nothing is getting updated yet I'm positive the PK
is correct. When I recreate the same UPDATE statement in QA, it works
fine. Is there something wrong with this SP?

Not as far as I can see. However, and please don't think I'm being
patronising here, is there the slightest possibility that your connection
string might not be pointing at the right server / database ...? I've seen
that so many times, it's almost always the first thing I check...
 
D

D. Shane Fowlkes

Yep. That's all OK. However, I think I've got some conflicting issues going
on with Page_Load and the buttons On_Click subs. I think one is canceling
the other out. Still researching.....

Thanks!
 
P

Prashant

look over the statement and execute in Query analyser and check...
actually in SP ... update will never return (1/0)

try adding your logic like this

create sp samp()
update table set col = 2 where colo2 = x
if (select a from table where colo2 = x and col = 2 )
i = 1
then
i = 0
end if
return i
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,904
Latest member
HealthyVisionsCBDPrice

Latest Threads

Top