Duplicating Ticket Numbers

Question

My project generates consecutive Ticket numbers when creating new tickets. Yet, in rare occasions this number duplicates. I have analyzed the table and found out that this happens when tickets generate between 1 or 2 minutes.

The following code is for reading and populating Ticket number for text box; I use Sql reader:

 Sub loadTicketNumber()


        Dim connetionString As String
        Dim sqlCnn As SqlClient.SqlConnection
        Dim sqlCmd As SqlClient.SqlCommand
        Dim sql As String

        connetionString = My.Settings.DBServer
        sql = "Select TicketNo FROM tbl_TicketNumber WHERE PC= '" & Soft_Parameters.QS_ClientPC & "'"

        sqlCnn = New SqlClient.SqlConnection(connetionString)
        Try
            sqlCnn.Open()
            sqlCmd = New SqlClient.SqlCommand(sql, sqlCnn)
            Dim sqlReader As SqlClient.SqlDataReader = sqlCmd.ExecuteReader()
            Dim x As Integer
            While sqlReader.Read()
                x = sqlReader.Item("TicketNo")

            End While
            sqlReader.Close()
            sqlCmd.Dispose()
            sqlCnn.Close()
            txtTicket.Text = x + 1
            txt_ReTicketNo.Text = x
        Catch ex As Exception
            MsgBox("Can not open connection ! ")
        End Try

    End Sub

But when I am saving my SQL code will search Edit or New ticket; if new ticket, it will save the new ticket number in another table.

Ill explain more Ticket Number will generate if its new ticket if this edited ticket it will not generate that's why Edit and Add new are same sub.

This is my code:

' these strings will dynamically generate at runtime i can add fields to form SQL statements 
    ' EditString is variable for edit SQL strin

    ' AddString is a variable for Insert into statement that holds data table columns
    ' AddStringVal uses that holds above Column value

    Try

        con.Open()
        Dim cm As New SqlClient.SqlCommand
        cm.Connection = con
        cm.CommandText = " 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" & _
        EditString & _
        " 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 " & _
            AddString & " ) 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  " & _
            AddStringVal & " ) 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"

        'this will pass parameters to update tbl_TicketNumber that stores my last ticket number
        cm.Parameters.AddWithValue("@TTicket", Val(txtTicket.Text))
        cm.Parameters.AddWithValue("@TPC", Soft_Parameters.QS_ClientPC)
        cm.Parameters.AddWithValue("@TnDate", Now)


        'this will pass parameters to update tbl_Tickets that stores all my ticket details
        cm.Parameters.AddWithValue("@Ticket_Type", cboTicketType.Text)
        cm.Parameters.AddWithValue("@TicketNo", Val(txtTicket.Text))
        cm.Parameters.AddWithValue("@VehicleNo", cboV_Code.Text)
        cm.Parameters.AddWithValue("@nDate", dtpDate.Value)
        cm.Parameters.AddWithValue("@Customer", Val(cboCustomer.SelectedValue))
        cm.Parameters.AddWithValue("@Job_Code", Val(cboJobCode.SelectedValue))
        cm.Parameters.AddWithValue("@Material", Val(cboMaterial.SelectedValue))
        cm.Parameters.AddWithValue("@First_Weight", Val(txtFWeight.Text))
        If Val(txtSWeight.Text) > 0 Then cm.Parameters.AddWithValue("@Second_Weight", Val(txtSWeight.Text)) Else cm.Parameters.AddWithValue("@Second_Weight", DBNull.Value)
        If Val(txtNetWeight.Text) > 0 Then cm.Parameters.AddWithValue("@Net_Weight", Val(txtNetWeight.Text)) Else cm.Parameters.AddWithValue("@Net_Weight", DBNull.Value)
        If txtUnitPrice.Visible = True Then cm.Parameters.AddWithValue("@Unit_Price", Val(txtUnitPrice.Text)) Else cm.Parameters.AddWithValue("@Unit_Price", DBNull.Value)
        If txtAddition.Visible = True Then cm.Parameters.AddWithValue("@Add_Charges", Val(txtAddition.Text)) Else cm.Parameters.AddWithValue("@Add_Charges", DBNull.Value)
        If txtDeduction.Visible = True Then cm.Parameters.AddWithValue("@Deduction", Val(txtDeduction.Text)) Else cm.Parameters.AddWithValue("@Deduction", DBNull.Value)
        If txtTktPrice.Visible = True Then cm.Parameters.AddWithValue("@Ticket_Amount", Val(txtTktPrice.Text)) Else cm.Parameters.AddWithValue("@Ticket_Amount", DBNull.Value)
        If cboSource.Visible = True Then cm.Parameters.AddWithValue("@Source", cboSource.Text) Else cm.Parameters.AddWithValue("@Source", DBNull.Value)
        If cboDestination.Visible = True Then cm.Parameters.AddWithValue("@Destination", cboDestination.Text) Else cm.Parameters.AddWithValue("@Destination", DBNull.Value)
        cm.Parameters.AddWithValue("@Operator", txtOperator.Text)
        cm.Parameters.AddWithValue("@Out_Time", Now)
        cm.Parameters.AddWithValue("@In_Time", Now)
        cm.Parameters.AddWithValue("@PC", Soft_Parameters.QS_ClientPC)


        lblQS_Code.Text = Convert.ToInt32(cm.ExecuteScalar.ToString())

    Catch ex As Exception
            txtError.Text = ex.Message
    Finally
        If Not con.State = ConnectionState.Closed Then con.Close()
             con.Close()
        'if this save is not firstweight edit then it will save new ticket no and generate new ticket
        If TicketFirstweightEdit = False Then
            '  SaveTicketNumber()
            loadTicketNumber()
        Else
            TicketFirstweightEdit = False
        End If

        'after saving this will clear fields automatically
        MsgBox("Data has been Updated Successfully!", MsgBoxStyle.OkOnly, "Data Updated!!")

    End Try

forget that string part i passes

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

where does the ticket number for new tickets is coming from? — Paolo, May 25 '15 at 07:37
you wrote that _my project generate consecutive Ticket number when creating new ticket_. where does that generated ticket number comes from? from a table? you 'generate' a duplicate ticket number reading from a table? — Paolo, May 25 '15 at 09:11
you can see there first code sub loadTicketNumber () will generate ticket number from tbl_TicketNumber and tbl_Tickets use for storing ticket details only, i use SqlDataReader to get last ticket number that i have save, — Ruwan Disanayaka, May 25 '15 at 09:29
Ruwan, just a few tips on your syntax. Use `Using` (refer http://stackoverflow.com/questions/887831/how-does-the-using-statement-translate-from-c-sharp-to-vb) it helps with the `Close` and `Dispose` to happen. — Jaques, May 25 '15 at 19:51
this is big tip for me :) Thanks again Jaques i will keep in mind — Ruwan Disanayaka, May 26 '15 at 05:46

score 1 · Accepted Answer · answered May 25 '15 at 20:15

Change your loadTicket sub to

Sub loadTicketNumber()

    Dim connetionString As String
    Dim sqlCnn As SqlClient.SqlConnection
    Dim sqlCmd As SqlClient.SqlCommand
    Dim sql As String

    connetionString = My.Settings.DBServer
    sql = String.Format("Select ISNULL(MAX(TicketNo), 0) TicketNo FROM tbl_TicketNumber WHERE PC= '{0}'", Soft_Parameters.QS_ClientPC)

    Using sqlCnn SqlClient.SqlConnection = New SqlClient.SqlConnection(connetionString)
        Try
            sqlCnn.Open()
            Using SqlClient.SqlCommand sqlCmd = New SqlClient.SqlCommand(sql, sqlCnn)
                Dim x As Integer
                x = Convert.ToInt32(sqlCmd.ExecuteScalar());
            End Using
            txtTicket.Text = x + 1
            txt_ReTicketNo.Text = x
        Catch ex As Exception
            MsgBox("Can not open connection ! ")
        End Try
    End Using
End Sub

This will not only run faster, but will dispose of the object properly as well, because you don't have a finally in your try, if there is a problem, you don't dispose of the connection or command.

I can't see however why a duplicate TicketNo will be created. The code is very difficult to read though. I would suggest that you just format it better. Why do you run SELECT IDENT_CURRENT('tbl_Tickets') FROM tbl_Tickets after the Insert? Because it does not seem that it serve any purpose?

Thanks jaques, wish i could give you vote, someone given minus so my rep fell down, many helpful people given me kind hand thanks a lot guys i can only give my words :) for the code i have edited it to clear view and for the SQL statement ill paste it below SELECT IDENT_CURRENT('tbl_Tickets') i use this before to get Auto gen number from table, now im not using it :) — Ruwan Disanayaka, May 26 '15 at 05:37
A lot of people on SO unfortunatly forgot that they at one stage also started off, and now they expect people to be on the same level you are. I gave you a +1. What I might suggest as well is to go and read up about `EXISTS` in sql, then you don't have to execute an update before the insert, but your code is efficient in this way, just another alternative — Jaques, May 27 '15 at 06:45

Duplicating Ticket Numbers

1 Answers1