User-1988524439 posted
ALTER PROCEDURE [dbo].[ms_insPartReturns]
(@LineID int,
@ReturnedBy varchar(8),
@DamageDescription varchar(1000) = null,
@PartNumber varchar(30) = null,
@PartCost smallmoney = 0,
@VendorInvoiceNo varchar(20) = null,
@VendorListPrice smallmoney = 0,
@VendorID int = 0,
@ProfitCenterID int = 0,
@InvoiceDate smalldatetime = null,
@RecordID int = 0,
@ReasonID int = 0,
@Reason varchar(50) = null,
@ReasonNotes varchar(250) = null,
@ApprovalPerson varchar(100) = null,
@newReturnID int output
)
AS
BEGIN
DECLARE @RepairOrderID int;
DECLARE @InvoiceNumber varchar(20);
DECLARE @WhoPays char(1);
DECLARE @VendorName varchar(35);
BEGIN TRAN T1;
IF @LineID = 0
BEGIN
SELECT @WhoPays = PayorIs
FROM dbo.RepairOrder
WHERE (RecordID = @RecordID);
SELECT @VendorName = VendorName,
@ProfitCenterID = CASE WHEN @ProfitCenterID > 0 THEN @ProfitCenterID
ELSE ProfitCenterID
END
FROM dbo.Vendors
WHERE (VendorID = @VendorID);
INSERT INTO dbo.RepairOrderLines
(RecordID
,DamageDescription
,PartNumber
,PriceProfitCenterID
,SellingPrice
,VendorListPrice
,PartCost
,VendorID
,VendorName
,VendorInvoiceNo
,PartReceiveDate
,PartStatus
,PartInvoiced
,WhoPays
,FinalBill
)
VALUES
(@RecordID
,@DamageDescription
,@PartNumber
,@ProfitCenterID
,CASE WHEN @VendorListPrice IS NULL THEN 0
ELSE @VendorListPrice
END
,CASE WHEN @VendorListPrice IS NULL THEN 0
ELSE @VendorListPrice
END
,@PartCost
,@VendorID
,@VendorName
,@VendorInvoiceNo
,@InvoiceDate
,5
,NULL
,@WhoPays
,CASE WHEN @VendorListPrice IS NULL THEN 0
ELSE 1
END
)
SET @LineID = SCOPE_IDENTITY();
END
SELECT @RecordID = R.RecordID,
@RepairOrderID = R.RepairOrderID,
@VendorID = L.VendorID,
@InvoiceNumber = L.VendorInvoiceNo
FROM dbo.RepairOrder AS R INNER JOIN
dbo.RepairOrderLines AS L ON R.RecordID = L.RecordID
WHERE (L.LineID = @LineID);
INSERT INTO dbo.PartReturns
([LineID]
,[ReasonID]
,[ReasonNotes]
,[DamageDescription]
,[PartNumber]
,[PartCost]
,[VendorListPrice]
,[VendorID]
,[VendorInvoiceNo]
,[RepairOrderID]
,[InvoiceID]
,[ReturnedBy])
SELECT [LineID]
,@ReasonID
,@ReasonNotes
,[DamageDescription]
,[PartNumber]
,[PartCost]
,[VendorListPrice]
,[VendorID]
,[VendorInvoiceNo]
,@RepairOrderID
,0
,@ReturnedBy
FROM dbo.RepairOrderLines
WHERE (LineID = @LineID);
SET @newReturnID = SCOPE_IDENTITY();
--create RepairOrderInvoices row for this credit and return new InvoiceID
DECLARE @newInvoiceID int;
SET @newInvoiceID = 0;
SET @PartCost = @PartCost * -1.0;
EXEC dbo.ms_insRepairOrderInvoicesCr @RepairOrderID, @InvoiceNumber, @InvoiceDate, @VendorID, @VendorName, @PartCost, @VendorListPrice, 1, @RecordID, 1, @ProfitCenterID, @newInvoiceID OUTPUT;
--Update the PartReturns row with InvoiceID created
UPDATE dbo.PartReturns
SET InvoiceID = @newInvoiceID
WHERE (ReturnID = @newReturnID);
COMMIT TRAN T1;
SELECT @newReturnID = SCOPE_IDENTITY();
Below is the SqlDataSource parameters
<InsertParameters>
<asp:ControlParameter Name="LineID" ControlID="txtLineID" PropertyName="Text" Type="Int32" />
<asp:CookieParameter Name="ReturnedBy" CookieName="myeecode" Type="String" />
<asp:Parameter Name="DamageDescription" Type="String" />
<asp:Parameter Name="PartNumber" Type="String" />
<asp:Parameter Name="PartCost" Type="Decimal" />
<asp:Parameter Name="VendorInvoiceNo" Type="String" />
<asp:Parameter Name="VendorListPrice" Type="Decimal" />
<asp:Parameter Name="VendorID" Type="Int32" />
<asp:Parameter Name="ProfitCenterID" Type="Int32" />
<asp:Parameter Name="InvoiceDate" Type="DateTime" />
<asp:ControlParameter Name="RecordID" ControlID="txtRecordID" PropertyName="Text" Type="Int32" />
<asp:Parameter Name="ReasonID" Type="Int32" />
<asp:Parameter Name="Reason" Type="String" />
<asp:Parameter Name="ReasonNotes" Type="String" />
<asp:Parameter Name="ApprovalPerson" Type="String" />
<asp:Parameter Name="newReturnID" Type="Int32" Direction="Output" />
</InsertParameters>
Below is the inserted event
Protected Sub SqlPartReturn_Inserted(sender As Object, e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlPartReturn.Inserted
Dim intnewReturnID As Int32 = Convert.ToInt32(e.Command.Parameters("newReturnID").Value)
txtReturnID.Text = intnewReturnID.ToString
End Sub
The error happens on the Dim line
