confirmation of saving on the database for printing (VB.NET)

I am developing a small POS system, how can we confirm that the invoice data is saved on the database without problem, to start printing

if there is a connection problem with the server (internet problem for example)
the application executes the code for printing the invoice, without saving the data on the database
(The rule is that you should not print an invoice that is not in the database ,that is the problem)

please give me the best idea to avoid the problem

thank you very much

here is the classification of my current code:

Dim Cmd As New SqlCommand

If Not cn_SQL.State = ConnectionState.Open Then cn_SQL.Open()

Dim _trans As SqlTransaction = cn_SQL.BeginTransaction 
Cmd.Transaction = _trans 


Try

  **PrintDocument1.Print()**

  Cmd.CommandText = "UPDATE  INTO   TABLE_STOCK ... "
  Cmd.Connection = cn_SQL
  Cmd.ExecuteNonQuery()

  Cmd.CommandText = "INSERT  INTO   TABLE_HEADER "
  Cmd.Connection = cn_SQL
  Cmd.ExecuteNonQuery()

  Cmd.CommandText = "INSERT  INTO   TABLE_BODY "
  Cmd.Connection = cn_SQL
  Cmd.ExecuteNonQuery()

  DataGridView1.Rows.Clear()



 
  _trans.Commit()
             
  cn_SQL.Close()

Catch ex As Exception

  _trans.Rollback()

  MsgBox(ex.Message)

End Try

You can do this two ways. One would be to ensure the business rule in your function calls. I don't know if your posted code is in a Sub or a Function, but I'm guessing a Sub based on no return values. Make it a Function, and only generate the invoice if the data successfully saves:

Private Sub ProcessSale()

    If Save() Then
        
        GenerateInvoice()
        
    End If

End Sub


Private Function Save() As Boolean

    Try
    
        ' Your code to save to the database
    
        Return True
    
    Catch ex As Exception

        _trans.Rollback()
        MsgBox(ex.Message)
        
        Return False

    End Try

End Function

Call Sub ProcessSale() to run and check the return value of Save(). Then, run any other processes you need after a successful save, such as GenerateInvoice().

Another method would be to save the data to the database, and in the code that generates the invoice, read back the invoice data from the database, and ensure you do not end up with an empty result set (i.e., saving failed).

In addition, you should use the Using statement to handle objects that implement IDisposable, such as SqlConnection objects, etc. You also should NOT be using global connection objects. Trust in SQL Server connection pooling, and declare the connection, use it, and let it dispose.

Public Function Save() As Boolean

    Using conn As New SqlConnection(connectionString)

        Using cmd As New SqlCommand()

            Dim tran As SqlTransaction = conn.BeginTransaction

            With cmd

                Try

                    .Transaction = tran

                    .CommandText = "UPDATE  INTO   TABLE_STOCK ... "
                    .ExecuteNonQuery()

                    .CommandText = "INSERT  INTO   TABLE_HEADER "
                    .ExecuteNonQuery()

                    .CommandText = "INSERT  INTO   TABLE_BODY "
                    .ExecuteNonQuery()

                    ' Move this to the calling function. Don't mix data access and UI code.
                    DataGridView1.Rows.Clear()

                    tran.Commit()

                    Return True

                Catch ex As Exception

                    tran.Rollback()    
                    MsgBox(ex.Message)

                    Return False

                End Try

            End With

        End Using

    End Using

End Sub