CODEWITHSUNDEEP
sqlsql-servervb.net
Ruwan Disanayaka
Ruwan Disanayaka

Reputation: 25

SQL Server : duplicates value

I'm updating my project And now the problem is sometimes ticket number Duplicates after update whats wrong with this code?

Or what would be the most elegant solution to allow me to

My code is 

DECLARE @tbl_ID TABLE (nID int)
UPDATE tbl_Tickets
SET Ticket_Type = @Ticket_Type,
VehicleNo= @VehicleNo,
Customer=@Customer,
Job_Code=@Job_Code,
Material=@Material,
First_Weight=@First_Weight,
Second_Weight=@Second_Weight,
Net_Weight=@Net_Weight,
Add_Charges=@Add_Charges,
Deduction=@Deduction,
Ticket_Amount=@Ticket_Amount,
SOURCE=@Source,
       Destination=@Destination,
OPERATOR=@Operator,
     Out_Time=@Out_Time,
     PC=@PC,
     Unit_Price=@Unit_Price OUTPUT INSERTED.QS_Code INTO @tbl_ID (nID)
 WHERE (VehicleNo=@VehicleNo)
 AND (Second_Weight IS NULL)
 AND (Ticket_Type <>'DELETED') IF @@ROWCOUNT=0 BEGIN

INSERT INTO tbl_Tickets (Ticket_Type, TicketNo, VehicleNo, nDate, Customer,Job_Code,Material,     First_Weight, Second_Weight, Net_Weight, Unit_Price, Add_Charges, Deduction, Ticket_Amount,SOURCE, Destination,
                       OPERATOR, In_Time, PC)
VALUES (@Ticket_Type,
      @TicketNo,
      @VehicleNo,
      @nDate,
      @Customer,
      @Job_Code,
      @Material,
      @First_Weight,
      @Second_Weight,
      @Net_Weight,
      @Unit_Price,
      @Add_Charges,
      @Deduction,
      @Ticket_Amount,
      @Source,
      @Destination,
      @Operator,
      @In_Time,
      @PC)
 SELECT IDENT_CURRENT('tbl_Tickets')
 FROM tbl_Tickets
 UPDATE tbl_TicketNumber
 SET TicketNo = @TTicket,
  PC= @TPC,
  nDate= @TnDate WHERE (PC=@TPC) IF @@ROWCOUNT=0 BEGIN
 INSERT INTO tbl_TicketNumber (TicketNo, PC, nDate)
 VALUES (@TTicket,
      @TPC,
      @TnDate) END END
SELECT *
FROM @tbl_ID

Then I change it to 

cm.CommandText = " DECLARE @tbl_ID TABLE (nID int) UPDATE tbl_Tickets " & _
        " SET Ticket_Type = @Ticket_Type, TicketNo = @TicketNo, VehicleNo= @VehicleNo," & _
        " nDate=@nDate, Customer=@Customer, Job_Code=@Job_Code, Material=@Material, " & _
        " First_Weight=@First_Weight, Second_Weight=@Second_Weight, Net_Weight=@Net_Weight, " & _
        " Add_Charges=@Add_Charges, Deduction=@Deduction, Ticket_Amount=@Ticket_Amount, " & _
        " Source=@Source, Destination=@Destination, Operator=@Operator, Out_Time=@Out_Time, PC=@PC, Payment=@Payment,Unit_Price=@Unit_Price " & _
        EditString & _
        " OUTPUT INSERTED.QS_Code INTO @tbl_ID (nID)  WHERE (VehicleNo=@VehicleNo) AND (Second_Weight IS NULL) AND  (Ticket_Type <>'Weighbridge_VOID') AND  (Ticket_Type <>'Cash_VOID') " & _
       " IF @@ROWCOUNT=0  " & _
        " BEGIN " & _
            " INSERT INTO tbl_Tickets (Ticket_Type, TicketNo, VehicleNo, nDate, Customer,Job_Code,Material, " & _
            " First_Weight, Second_Weight, Net_Weight, Unit_Price, Add_Charges, Deduction, Ticket_Amount,Source, " & _
            " Destination, Operator, In_Time, PC, Payment " & _
            AddString & " ) VALUES (@Ticket_Type, '00-00', @VehicleNo, @nDate, @Customer,@Job_Code,@Material, " & _
            " @First_Weight, @Second_Weight, @Net_Weight, @Unit_Price, @Add_Charges, @Deduction, @Ticket_Amount,@Source, " & _
            " @Destination, @Operator, @In_Time, @PC, @Payment  " & _
            AddStringVal & " ) SELECT IDENT_CURRENT('tbl_Tickets') FROM tbl_Tickets " & _
           " END SELECT * From @tbl_ID" '

And add the TicketNumberUpdate sub after this update like this

cm.CommandText = " UPDATE tbl_TicketNumber " & _
            " SET TicketNo = @TTicket, PC= @TPC, nDate= @TnDate" & _
            " WHERE (PC=@TPC) " & _
           " IF @@ROWCOUNT=0 " & _
           " INSERT INTO tbl_TicketNumber (TicketNo, PC, nDate) VALUES (@TTicket,@TPC,@TnDate) "

Upvotes: 0

Views: 86

Answers (1)

Spock
Spock

Reputation: 4900

It looks like you're ignoring the value of the IDENT_CURRENT Try changing 

SELECT IDENT_CURRENT('tbl_Tickets')
FROM tbl_Tickets

UPDATE tbl_TicketNumber SET
    TicketNo = @TTicket,
    PC= @TPC,
    nDate= @TnDate
WHERE (PC=@TPC)

to this

SET @TTicket = IDENT_CURRENT('tbl_Tickets')

UPDATE tbl_TicketNumber SET
    TicketNo = @TTicket,
    PC= @TPC,
    nDate= @TnDate
WHERE (PC=@TPC)

Upvotes: 1

Related Questions