Click here to Skip to main content
15,895,557 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi i am using Stored Procedure

SQL
ALTER PROCEDURE [dbo].[SP_InsTicketSale] 
(
@TktDate date, @TktId nvarchar(50),@Name nvarchar(50),  @Email nvarchar(50), @Mobile varchar(15),@TktStatus nvarchar(50)
,
@EmpId nvarchar(50),@TblLoc_ID varchar(3), @Promoid nvarchar(50), @TaxId nvarchar(50), @Grandtotal decimal(18,2),@subtotal decimal(18,2),

@Details ITEMDATA1   Readonly,

@id nvarchar(5) = NULL OUTPUT
)
AS
BEGIN


if not exists(select * from Tbl_TicketSale where TktId= @TktId)
	Begin
		declare @IdTmp	varchar(5)
    	set @IdTmp = 00000
  	    select @IdTmp = count(*) from Tbl_TicketSale
	   
	    set @IdTmp = @IdTmp + 1
	    set @TktId=   Right(('00000' + @IdTmp),5)

Begin Transaction


INSERT INTO Tbl_Client(TktDate, TktId, Name, Email, Mobile)
VALUES   (@TktDate, @TktId,@Name, @Email, @Mobile)


IF (@@ERROR <> 0) GOTO ERR_HANDLER	

INSERT INTO Tbl_TicketSale (TktId, EmpId, TktDate, TblLoc_ID,  TaxId, Grandtotal,subtotal,TktStatus)
VALUES  (@TktId , @EmpId, @TktDate, @TblLoc_ID, @TaxId , @Grandtotal ,@subtotal,'Delivered')


IF (@@ERROR <> 0) GOTO ERR_HANDLER	

--Temp Table
INSERT INTO Tbl_TicketSaleChild (TktId, ActivityId, Qty, PoxtypeAC, ActPrice, Amt, TktSale_childdate)
Select TktId=@TktId, tActivityId, tQty, tPoxtypeAC, tActPrice, tAmt, tTktSale_childdate from @Details ;

IF (@@ERROR <> 0) GOTO ERR_HANDLER	

End
Commit Transaction

 --RETURN 0
 Return @TktId 
 --select @TktId

ERR_HANDLER:
PRINT 'Unexpected error occurred!'
ROLLBACK Transaction
RETURN 1


END


and C# i am using

C#
//public int InsetTicketSale()
        public void InsetTicketSale()
        {
            int i = 0;
            try
            {

                SqlDBHelper SqlDb = new SqlDBHelper();
                SqlDb.CreateParameters("@TktDate", SqlDbType.Date, TktDate);
                SqlDb.CreateParameters("@TktId", SqlDbType.VarChar, TktId);
                SqlDb.CreateParameters("@Name", SqlDbType.VarChar, Name);
                SqlDb.CreateParameters("@Email", SqlDbType.VarChar, Email);
                SqlDb.CreateParameters("@Mobile", SqlDbType.VarChar, Mobile);
                SqlDb.CreateParameters("@EmpId", SqlDbType.VarChar, EmpId);
                SqlDb.CreateParameters("@TblLoc_ID", SqlDbType.VarChar, TblLoc_ID);
                SqlDb.CreateParameters("@Promoid", SqlDbType.VarChar, Promoid);
                SqlDb.CreateParameters("@TaxId", SqlDbType.VarChar, TaxId);
                SqlDb.CreateParameters("@Grandtotal", SqlDbType.Decimal, Grandtotal);
                SqlDb.CreateParameters("@subtotal", SqlDbType.Decimal, subtotal);
                SqlDb.CreateParameters("@Details", SqlDbType.Structured, Details);
                SqlDb.CreateParameters("@Servtax", SqlDbType.Decimal, Servtax);
                SqlDb.CreateParameters("@vat", SqlDbType.Decimal, vat);
                SqlDb.CreateParameters("@Tax3", SqlDbType.Decimal, Tax3);
                SqlDb.CreateParameters("@Servtaxp", SqlDbType.Decimal, Servtaxp);
                SqlDb.CreateParameters("@vatp", SqlDbType.Decimal, vatp);
                SqlDb.CreateParameters("@Tax3p", SqlDbType.Decimal, Tax3p);
                SqlDb.CreateParameters("@TktStatus", SqlDbType.VarChar, TktStatus);
                

                SqlDb.ProcedureName = "[SP_InsTicketSale]";

                i = Convert.ToInt32(SqlDb.ExecuteNonQueryReturn());
            }
            catch (Exception Excep)
            {
                throw new ApplicationException(Excep.Message);
            }
           // return i;
        }


What I have tried:

in C# i have tried int type and executenonquery() it was getting count of transactions done,
i am getting null as when used string and executescalar combination
Posted
Updated 6-Sep-16 22:30pm
Comments
Andy Lanng 7-Sep-16 4:19am    
If your using ExecuteScalar, try Select @TktId / Select 1

Return values have slightly different uses
ravikiran geddam 7-Sep-16 6:32am    
i dint get you i am getrrin varhcar as output like '000010' like this

1 solution

ExecuteNonQuery() will return the number of records affected. Which would be a number.
ExecureScalar() would return the first column of the top row. So you should use ExecureScalar() instead.

Learn more from Difference between ExecuteReader ExecuteScalar and ExecuteNonQuery[^]
 
Share this answer
 
Comments
ravikiran geddam 7-Sep-16 6:31am    
Hi thank you very much for reply.
But even i have used ExecuiteScalar() as string but i am getting null and procedure was executing successfully
NaibedyaKar 7-Sep-16 7:16am    
Use SELECT @TktId instead of Return @TktId.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900