I am trying to execute a stored procedure through .NET but the stored procedure doesn’t seem to work. I know that my code in .NET works without any errors and I am almost sure my stored procedure code is correct but it is not executing what I want it to do.
In the code if I want to change the date in the drop down box and then hit the Save button it should execute the stored procedure in the Save button click event to update the record in the database. Here is the code for the Save button:
Protected Sub SaveButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)
' Using onyxConnection
Try
dim @iReturnCode
Dim cmd As New SqlCommand
onyxConnection.Open()
cmd.Connection = onyxConnection
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "cnpcuStudentSessionDate"
cmd.Parameters.AddWithValue("@iProductId", lblProductID.Text)
cmd.Parameters.AddWithValue("@chUserId", lblUpdateBy.Text)
cmd.Parameters.AddWithValue("@dtPurchaseDate", ddlSession.SelectedValue)
cmd.Parameters.AddWithValue("@dtSchEnrDate", ddlSession.SelectedValue)
'cmd.Parameters.AddWithValue("@iProductId", txtAppProjNum.Text)
'cmd.Parameters.AddWithValue("@iProductId", txtAppProjNum.Text)
cmd.ExecuteNonQuery()
Catch ex As Exception
Dim a, b
a = ex.ToString
b = a.ToString
Finally
End Try
End Sub
In the stored procedure I am selecting an existing record from the database and updating it with a new date. Here is the code for the stored procedure:
use DB
if exists (select * from sysobjects
where name = 'cnpcuStudentSessionDate' and type = 'P')
drop proc cnpcuStudentSessionDate
go
create procedure cnpcuStudentSessionDate
@iProductId int,
@chUserId nchar(10),
@dtPurchaseDate datetime,
@dtSchEnrDate datetime,
@iReturnCode int = Null output
With Encryption
as
set nocount on
/* declare variables */
declare @dtNow datetime
/* M003D Begin*/
declare @iIndividualId int,
@dtReceivedDate datetime,
@dtEnrollmentDate datetime,
@vchScheduleNumber varchar(255),
@vchCreditScore smallint,
@siGuidesRequired smallint,
@dcDeposit decimal(9, 2),
@dcPendingDeposit decimal(9, 2),
@dcDiscount decimal(9, 2),
@dcShareDeposit decimal(9, 2),
@dcNetAdjustment decimal(9, 2),
@siTerm smallint,
@dcPaymentAmount decimal(9, 2),
@dtFirstDue datetime,
@dtNextDue datetime,
@dcInterestRate decimal(8, 5),
@dcGrossSale decimal(9, 2),
@dcInterest decimal(9, 2),
@dcContract decimal(9, 2),
@dcCurrentBalance decimal(9, 2),
@dcContractPaid decimal(9, 2),
@dcInterestPaid decimal(9, 2),
@dcAccruedInterestBalance decimal(9, 2),
@dcInterestPerDiem decimal(9, 2),
@dcEstimatedPayoff decimal(9, 2),
@siTotalPayments decimal(9, 2),
@dcTotalPaid decimal(9, 2),
@dtDisbursementDate datetime,
@dcChargeOff decimal(9, 2),
@dcInvoiceBalance decimal(9, 2),
@dtInvoiceDate datetime,
@iInvoiceStatus int,
@dcAdjDiscount decimal(9, 2),
@dcSalesTax decimal(9, 2),
@dcPendingSalesTax decimal(9, 2),
@iOwnerId int,
@dcFinancedSalesTax decimal(9,2) --M001A
/*M003D End */
--M003A Begin
declare @iSiteId int,
@chLanguageCode nchar(4),
@iContactId int,
@chProductNumber nchar(20),
@vchSerialNumber nvarchar(50),
@flQuantity onyxfloat,
@iTrackingId int,
@iSourceId int,
@iStatusId int,
@iAccessCode int,
@vchUser1 nvarchar(30),
@vchUser2 nvarchar(30),
@vchUser3 nvarchar(30),
@vchUser4 nvarchar(30),
@vchUser5 nvarchar(30),
@vchUser6 nvarchar(30),
@vchUser7 nvarchar(30),
@vchUser8 nvarchar(30),
@vchUser9 nvarchar(30),
@vchUser10 nvarchar(30),
@dtUpdateDate datetime
--Variables needed for incident Update.
declare
@iIncidentId int,
@tiLCMSAccount tinyint,
@iIncidentCategory int,
@iIncidentTypeId int,
@vchAssignedId nvarchar(255),
@vchProductId nvarchar(255),
@vchDesc1 nvarchar(255),
@vchDesc2 nvarchar(255),
@vchKeyWords nvarchar(255),
@iPriorityId int,
@iCode1 int,
@iCode2 int,
@iCode3 int,
@iCode4 int,
@iTime int,
@iLabor int,
@tiImage tinyint,
@chAssignedTo nchar(10),
@chInsertBy nchar(10),
@dtInsertDate datetime,
@tiRecordStatus tinyint,
@iReminderId int
--M003A End
/* initialize variables */
set @dtNow = getdate()
/* set default values */
set @iReturnCode = 0
if isnull(@iProductId,0) > 0 and isnull(@vchUser7,0) > 0
begin
--Get the current values of the customer product record.
SELECT
@chLanguageCode = [chLanguageCode]
,@iContactId = [iContactId]
,@chProductNumber = [chProductNumber]
,@vchSerialNumber = [vchSerialNumber]
,@flQuantity = [flQuantity]
,@dtPurchaseDate = [dtPurchaseDate]
,@iTrackingId = [iTrackingId]
,@iSourceId = [iSourceId]
,@iStatusId = [iStatusId]
,@iAccessCode = [iAccessCode]
,@vchUser1 = [vchUser1]
,@vchUser2 = [vchUser2]
,@vchUser3 = [vchUser3]
,@vchUser4 = [vchUser4]
,@vchUser5 = [vchUser5]
,@vchUser6 = [vchUser6]
,@vchUser7 = [vchUser7]
,@vchUser8 = [vchUser8]
,@vchUser9 = [vchUser9]
,@vchUser10 = [vchUser10]
,@dtUpdateDate = [dtUpdateDate]
FROM [OnyxTCN].[dbo].[CustomerProduct]
WHERE [iProductId] = @iProductId
--If we have successfully registered this course in the LMS, proceed.
if @tiLCMSAccount = 1
begin
--Set the tracking ID for the product to the Course Registered tracking id.
--Sandbox: 5465 Live: 7743
Set @iTrackingId = 7743
end
else
begin
--Set the tracking id for the product to the Course Not Registered tracking id.
--Sandbox: 5474 Live: 7771
Set @iTrackingId = 7771
end
if (select iTrackingId from customerProduct with (nolock) where iProductId = @iProductId) <> @iTrackingId
begin
--Update the customer product table.
exec @iReturnCode = wbospsuCustomerProduct
@iSiteId, --iSiteId
@iProductId, --iProductId
@iIndividualId, --iOwnerId
@chLanguageCode, --Language Code
@iContactId, --Contact ID
@chProductNumber, --Product Number
@vchSerialNumber, --Serial NUmber
@flQuantity, --Quantity
@dtPurchaseDate, --Purchase Date
@iTrackingId, --Tracking Id
@iSourceId, --Source Id
@iStatusId, --Status Id
@iAccessCode, --Access Code
@vchUser1, --UDF1
@vchUser2, --UDF2
@vchUser3, --UDF3
@vchUser4, --UDF4
@vchUser5, --UDF5
@vchUser6, --UDF6
@vchUser7, --UDF7
@vchUser8, --UDF8
@vchUser9, --UDF9
@vchUser10, --UDF10
@chUserId, --chUpdateBy
@dtUpdateDate, --dtUpdateDate
null, --tiRecordUnlock
null --tireturnType
end
return @iReturnCode
end
go
grant execute on cnpcuStudentSessionDate to public
go