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