This is my type Table
CREATE TYPE [dbo].[ThreeWayreconstatus] AS TABLE(
[RowNo] [int] NOT NULL,
[Status] [nvarchar](10) NOT NULL,
[ReferenceId] [nvarchar](30) NOT NULL,
[RequestID] [nvarchar](100) NOT NULL,
[BankTransactionID] [nvarchar](30) NOT NULL,
[TransactionDate] [nvarchar](255) NULL,
[APIStatusCode] [nvarchar](10) NULL,
[ServiceType] [nvarchar](155) NULL
)
here im trying to update my transactions column 3wayreconstatus to success but im unable to do it
ALTER PROCEDURE [dbo].[usp_UpdateThreeWayReconStatus]
-- Add the parameters for the stored procedure here
(
@ThreeWayreconstatus as ThreeWayreconstatus ReadOnly
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
BEGIN TRY
BEGIN TRANSACTION
SET NOCOUNT ON;
declare @RequestID NVARCHAR(100)
,@STATUS NVARCHAR(10)
,@ReferenceId NVARCHAR(100)
,@RequestAmount decimal(18,2)
,@APIStatusCode nvarchar(10)
set @RequestID = (select RequestID from @ThreeWayreconstatus where RowNo =1)
SET @STATUS = (SELECT Status FROM @ThreeWayreconstatus)
DECLARE @LoopCount int
select @LoopCount =count(*) from @ThreeWayreconstatus
DECLARE @n int = 1
IF (@LoopCount > 0)
BEGIN
while(@n<=@LoopCount)
BEGIN
select
@RequestID= RequestID
,@STATUS =Status
,@ReferenceId = ReferenceId
,@RequestID = RequestID
,@APIStatusCode = APIStatusCode
from @ThreeWayreconstatus TS where RowNo=@n
IF (SELECT count(*) FROM Transfers
WHERE TransactionID = convert(NVARCHAR(100), @RequestID)) = 2
BEGIN
if(@STATUS='00')
BEGIN
Update Transactions set 3WayReconStatus='SUCCESS' where RequestID=@RequestID
End
else
BEGIN
select @RequestAmount = RequestAmount from Transactions where RequestID=@RequestID
PRINT @STATUS;
-- Refund Logic Need to write
-- exec usp_InsertRazorPayWebhooks @BankTransactionID, @Status, @PayoutID,@RequestID,@ResponseObject
END
SET @n = @n + 1
END
END
SELECT 'You have been Updated ThreeWarycone Status Successfully' AS AlertMessage
END
ELSE
BEGIN
SELECT 'Your ThreeWarycone Status Update Failed' AS AlertMessage
end
END TRY
BEGIN CATCH
IF @@TRANCOUNT>0
BEGIN
ROLLBACK
INSERT INTO Application_Error(ErrorMessage,FromPage,Createdon) VALUES(ERROR_MESSAGE(),'usp_UpdateThreeWayReconStatus',GETDATE())
END
END CATCH
END
from code behind im sending datatable first i've converted json object into datatable object
this how im doing
my json
response = "{\"apiStatus\":true,\"apiStatusMessage\":\"RequestCompleted\",\"data\":[{\"merchantTransactionId\":\"BB3AEUAT3\",\"TransactionId\":\"fing1\",\"transactionRrn\":\"123rrn\",\"responseCode\":\"00\",\"referenceId\":\"35050520181634\",\"transactionDate\":\"28-04-2020\",\"serviceType\":\"CW\"},{\"merchantTransactionId\":\"2\",\"TransactionId\":\"fing2\",\"transactionRrn\":\"133rrn\",\"responseCode\":\"00\",\"referenceId\":\"35050520181634\",\"transactionDate\":\"28-04-2020\",\"serviceType\":\"AP\"}],\"apiStatusCode\":0}";
this is how im doing
DataTable dataTable = Tabulate(response);
public static DataTable Tabulate(string json)
{
JObject jsonObj = JObject.Parse(json);
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn("RowNo", typeof(int)));
dt.Columns.Add(new DataColumn("Status", typeof(string)));
dt.Columns.Add(new DataColumn("ReferenceId", typeof(string)));
dt.Columns.Add(new DataColumn("RequestID", typeof(string)));
dt.Columns.Add(new DataColumn("BankTransactionID", typeof(string)));
dt.Columns.Add(new DataColumn("TransactionDate", typeof(string)));
dt.Columns.Add(new DataColumn("APIStatusCode", typeof(int)));
dt.Columns.Add(new DataColumn("ServiceType", typeof(string)));
JArray dataArray = (JArray)jsonObj["data"];
int iRowCounter = 1;
foreach (JObject item in dataArray)
{
DataRow dr = dt.NewRow();
dr["RowNo"] = iRowCounter++;
dr["Status"] = (string)item["responseCode"];
dr["ReferenceId"] = (string)item["referenceId"];
dr["RequestID"] = (string)item["merchantTransactionId"];
dr["BankTransactionID"] = (string)item["transactionRrn"];
dr["TransactionDate"] = (string)item["transactionDate"];
dr["APIStatusCode"] = (string)jsonObj["apiStatusCode"];
dr["ServiceType"] = (string)item["serviceType"];
dt.Rows.Add(dr);
}
return dt;
}
after im getting like this
.png)
then im using my above procedure to update my table
im getting error like
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
im literally helpless pls help me asap...