Hi,
I have a dal code as
public int InsertScanedTemp(string SQLquery, long pvid,out int suceesFlag)
{
try
{
suceesFlag = 0;
int retVal = 0;
Database db = DatabaseFactory.CreateDatabase("testConnectionString5");
DbCommand myCommand = db.GetStoredProcCommand("Pv_InsertScanData_Temp");
db.AddInParameter(myCommand, "@sqlqueries", DbType.String, SQLquery);
db.AddInParameter(myCommand, "@PVID", DbType.Int64, pvid);
db.AddOutParameter(myCommand, "@suceesFlag", DbType.Int32, 100);
retVal = db.ExecuteNonQuery(myCommand);
suceesFlag = (int)db.GetParameterValue(myCommand, "@suceesFlag");
return retVal;
}
catch (Exception ex)
{
string message = ex.Message.ToString();
return 0;
}
}
and proc as
-- Pv_InsertScanData_Temp 'INSERT INTO dbo.ScannedTemp(DeptID,LocationID,inv_id ,scannedDate ,DownloadDate ,PVID ,Comitted ,UserName,Article_DescID)VALUES(95 ,356 ,4716 ,''2013/10/08 09:19:09'' , getdate() ,22,0 ,1 ,2)#INSERT INTO dbo.ScannedTemp(DeptID,LocationID,inv_id ,scannedDate ,DownloadDate ,PVID ,Comitted ,UserName,Article_DescID)VALUES(95 ,356 ,3130 ,''2013/10/08 09:19:09'' , getdate() ,22,0 ,1 ,4)#INSERT INTO dbo.ScannedTemp(DeptID,LocationID,inv_id ,scannedDate ,DownloadDate ,PVID ,Comitted ,UserName,Article_DescID) VALUES(95 ,1 ,5870 ,''2013/10/08 09:19:09'' , getdate() ,22,0 ,1 ,0)#',22,0
ALTER proc [dbo].[Pv_InsertScanData_Temp]
@sqlqueries varchar(max) ,@PVID bigint ,@suceesFlag int output
as
BEGIN
SET NOCOUNT OFF
DECLARE @starttrancount int
BEGIN TRY
SELECT @starttrancount = @@TRANCOUNT
IF @starttrancount = 0
BEGIN TRANSACTION
truncate table ScannedTemp
SET @sqlqueries = REPLACE ( @sqlqueries , '#' , CHAR(13) + CHAR(10) )
DECLARE @cnt bigint,@i bigint,@loop bigint
SELECT @loop=COUNT(ScanID) FROM ScannedTemp
set @i=1
while(@i<=@loop)
begin
SELECT @DeptID=DeptID,@LocationID=LocationID,@inv_id=inv_id,@old_inventory_no=old_inventory_no,@scannedDate=scannedDate,
@DownloadDate= DownloadDate,@PVID=PVID,@Comitted = Comitted,@RecoDate=RecoDate,
@UserName= UserName,@ScannedTempRemarks=ScannedTempRemarks,@PVStatus=PVStatus,@Type=[Type],
@Article_DescID= Article_DescID,@Remark=Remark,@TagStatus=TagStatus
from ScannedTemp where ScanID=@i
if(@Article_DescID<>0)
begin
update inventory set Inventory_no=dbo.fn_GenerateInvNum_withArticleId(@inv_id,@Article_DescID) where inv_id=@inv_id
end
else
begin
if(@Inventory_no = null)
begin
print '' --data to be filled
--print 'elsebegin@Article_DescID'
end
end
set @i =@i + 1
end
-- select 'sucess' as SucessMsg
set @suceesFlag=1
COMMIT TRANSACTION
----------------------------------------------------
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0 AND @starttrancount = 0
ROLLBACK TRANSACTION
print 'error'
-- select 'error' as SucessMsg
set @suceesFlag=0
-- set @suceesFlag =0
-- RAISERROR [rethrow caught error using @ErrorNumber, @ErrorMessage, etc]
END CATCH
END
my problem is when i am running stored procedure in sql server directly ,the @suceesFlag is showing me 1 but when i debug it from DAL its showing me 0 ,i am not getting any error.