Hi,
I got this problem in my program when trying to execute stor procedure and xml file..
below is my code and script : Please help.
code:
Public Overloads Function ExecuteNonQuery(ByVal connection As IDbConnection, ByVal commandType As CommandType, ByVal commandText As String, ByVal ParamArray commandParameters() As IDbDataParameter) As Integer
Try
Dim cmd As IDbCommand = DataFactory.CreateCommand(_providerType)
PrepareCommand(cmd, connection, CType(Nothing, IDbTransaction), commandType, commandText, commandParameters)
Dim retVal As Integer = cmd.ExecuteNonQuery
cmd.Parameters.Clear()
If connection.State = ConnectionState.Open Then connection.Close() 'JAY 2008-08-15
Return retVal
Catch ex As Exception
Throw New Exception(ex.Message)
End Try
End Function
sp:
USE [Sony_EQPortal]
GO
/****** Object: StoredProcedure [dbo].[spEdiSI] Script Date: 12/28/2011 09:26:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
UPDATE EdiSI SET IsProcessed = 0
*/
========================================================================================== */
ALTER PROCEDURE [dbo].[spEdiSI] (
@HeaderLinkId uniqueidentifier = Null
) AS
SET NOCOUNT ON
DECLARE @CompanyCode varchar(2), @BranchCode varchar(2), @UserId varchar(10)
DECLARE @RetVal int, @Count int, @SeqNo int
DECLARE @EdiType varchar(1)
-- Hardcoded for Sony
SET @CompanyCode = 'SL'
SET @BranchCode = 'HQ'
SET @UserId = 'EDI'
-- Reset ErrorDesc
UPDATE EdiSI
SET ErrorDesc = Null
WHERE IsProcessed = 0
IF @@ERROR <> 0 RETURN -2
-- Convert Type Size from C_20_GP_CONT => 20GP
UPDATE Edi
SET TypeSize = Replace(Replace(Replace(ContainerTypeSize, '_CONT', ''), 'C_', ''), '_', '')
FROM EdiSIContainer Edi
WHERE Edi.TypeSize IS Null
IF @@ERROR <> 0 RETURN -2
EXEC @RetVal = spEdiSI_Validation @CompanyCode, @BranchCode, @UserId, @HeaderLinkId
IF @@ERROR <> 0 Or @RetVal <> 0 RETURN -2
DECLARE @Tmp TABLE (
RecordId int identity(1, 1) PRIMARY KEY CLUSTERED,
HeaderLinkId uniqueidentifier,
EdiType varchar(1) collate database_default
)
-- Insert unprocessed EDI that passed the validation into temp table
INSERT @Tmp (
HeaderLinkId, EdiType
)
SELECT HeaderLinkId, EdiType
FROM EdiSI
WHERE IsProcessed = 0 And ErrorDesc IS Null
And (@HeaderLinkId IS Null Or (@HeaderLinkId IS NOT Null And HeaderLinkId = @HeaderLinkId))
ORDER BY CreateDate
IF @@ERROR <> 0 RETURN -2
--SELECT * FROM @Tmp
SET @SeqNo = 1
SELECT @Count = COUNT(*) FROM @Tmp
WHILE @SeqNo <= @Count BEGIN
SELECT @HeaderLinkId = HeaderLinkId, @EdiType = EdiType
FROM @Tmp
WHERE RecordId = @SeqNo
IF @EdiType = 'N' EXEC spEdiSI_Add @CompanyCode, @BranchCode, @UserId, @HeaderLinkId
ELSE IF @EdiType = 'R' EXEC spEdiSI_Edit @CompanyCode, @BranchCode, @UserId, @HeaderLinkId
ELSE IF @EdiType = 'C' EXEC spEdiSI_Cancel @CompanyCode, @BranchCode, @UserId, @HeaderLinkId
SET @SeqNo = @SeqNo + 1
END