shuhana84 0 Light Poster

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.20 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.