none
Variabla used before it has been assigned a value. RRS feed

  • Question

  • I lifted some of these codes from one of your sites, but this part of it is underlined at design time "transaction. rollback()"  and this error message variable transaction is used before it has been assigned a value. Could anyone help me figure this problem out. transaction rollback() appears just above the last Msgbox

    Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click Dim conn As New OleDb.OleDbConnection Dim Cmd As New OleDb.OleDbCommand Dim transaction As OleDbTransaction ' open connection conn.ConnectionString = getConnection() Try conn.Open() transaction = conn.BeginTransaction(IsolationLevel.ReadCommitted) Cmd.Connection = conn Cmd.Transaction = transaction 'make a command for the connection and transaction Cmd.CommandText = "INSERT INTO Patients (Title, Surname, FirstName, MiddleName, Phone)Values(?,?,?,?,?)" Cmd.Parameters.AddWithValue("?", cboTitle.Text) Cmd.Parameters.AddWithValue("?", txtSurname.Text) Cmd.Parameters.AddWithValue("?", txtFirstName.Text) Cmd.Parameters.AddWithValue("?", txtMiddleName.Text) ' Cmd.ExecuteNonQuery() 'create paremeters for the second comnamd Cmd.Parameters.Clear() Cmd.CommandText = "INSERT INTO PatientsNOk(Title, Surname.FirstName, MiddleName,Phone)Values(?,?,?,?,?)" Cmd.Parameters.AddWithValue("?", CboTitleNok.Text) Cmd.Parameters.AddWithValue("?", txtSurnameNok.Text) Cmd.Parameters.AddWithValue("?", txtFirstNameNok.Text) Cmd.Parameters.AddWithValue("?", txtMiddleNameNok.Text) Cmd.ExecuteNonQuery() Dim result As Integer = Cmd.ExecuteNonQuery() 'commit transaction transaction.Commit() MsgBox("Both records are written to the db") Catch ex As Exception Console.WriteLine(ex.Message) ' Try to rollback the transaction End Try

    transaction.Rollback() MsgBox("Record was not inserted") ' Do nothing here; transaction is not active. ' The connection is automatically closed when the ' code exits the Using block. End Sub


    Wednesday, May 23, 2018 8:28 AM

All replies

  • Alobi, 

    I assume it is a warning. But if the open fails then it becomes an exception. 

    I would make in your case a nested try and put your rollback in the first catch part and not behind it. Pseudo code.

    Try
       Open Connection
       Try 
         .....
       Catch 
         Rollback
         Messagebox.show(ex.message)
       End Try
    Catch ex as exception
       Messagebox.show(ex.message)
    End Try   


    Success
    Cor



    Wednesday, May 23, 2018 8:55 AM
  • Thanks, I will go try what you said and get back to the forum.

    alobi

    Wednesday, May 23, 2018 9:49 AM
  • Hello,

    Here is an example to try

    Dim insertStatement1 = "INSERT INTO Patients (Title, Surname, FirstName, MiddleName, Phone)Values(?,?,?,?,?)"
    Dim insertStatement2 = "INSERT INTO PatientsNOk(Title, Surname.FirstName, MiddleName,Phone)Values(?,?,?,?,?)"
    
    Using cn As New OleDbConnection With {.ConnectionString = GetConnection()}
        Dim transaction = cn.BeginTransaction()
        Using cmd As New OleDbCommand With {.Connection = cn, .CommandText = insertStatement1, .Transaction = transaction}
            cmd.Parameters.AddWithValue("?", cboTitle.Text)
            cmd.Parameters.AddWithValue("?", txtSurname.Text)
            cmd.Parameters.AddWithValue("?", txtFirstName.Text)
            cmd.Parameters.AddWithValue("?", txtMiddleName.Text)
            Try
                cn.Open()
                cmd.ExecuteNonQuery()
    
                'create paremeters for the second comnamd
                cmd.Parameters.Clear()
                cmd.CommandText = insertStatement2
                cmd.Parameters.AddWithValue("?", CboTitleNok.Text)
                cmd.Parameters.AddWithValue("?", txtSurnameNok.Text)
                cmd.Parameters.AddWithValue("?", txtFirstNameNok.Text)
                cmd.Parameters.AddWithValue("?", txtMiddleNameNok.Text)
    
                Dim result As Integer = cmd.ExecuteNonQuery()
    
                'commit transaction
                transaction.Commit()
                MsgBox("Both records are written to the db")
    
            Catch sqlex As OleDbException
                transaction.Rollback()
                MsgBox("Record was not inserted")
                ' Do nothing here; transaction is not active.
    
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
        End Using
    End Using

    Warning setting under project properties. When building, no errors.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, May 23, 2018 11:21 AM
    Moderator
  • As Cor said, the problem is that the "transaction.Rollback()" line of code (and related message box) should be in the Catch portion of the Try block.  The way the code is now, the transaction would rollback even if it was successful.

    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Wednesday, May 23, 2018 11:32 AM
    Moderator
  • Yes have changed my code as shown below, rollback still gets underlined, it does run but I get a message from the last message box saying that " record was not inserted " thanks

    Dim conn As New OleDb.OleDbConnection
            Dim Cmd As New OleDb.OleDbCommand
            Dim transaction As OleDbTransaction
            ' open connection 
            conn.ConnectionString = getConnection()
            Try
                conn.Open()
                Try
                    transaction = conn.BeginTransaction(IsolationLevel.ReadCommitted)
                    Cmd.Connection = conn
                    Cmd.Transaction = transaction
                    'make a command for the connection and transaction
                    Cmd.CommandText = "INSERT INTO Patients (Title, Surname, FirstName, MiddleName)Values(?,?,?,?)"
                    Cmd.Parameters.AddWithValue("?", cboTitle.Text)
                    Cmd.Parameters.AddWithValue("?", txtSurname.Text)
                    Cmd.Parameters.AddWithValue("?", txtFirstName.Text)
                    Cmd.Parameters.AddWithValue("?", txtMiddleName.Text)
                    '
                    Cmd.ExecuteNonQuery()
                    'create paremeters for the second comnamd
                    Cmd.Parameters.Clear()
                    Cmd.CommandText = "INSERT INTO PatientsNOk(Title, Surname.FirstName, MiddleName)Values(?,?,?,?)"
                    Cmd.Parameters.AddWithValue("?", CboTitleNok.Text)
                    Cmd.Parameters.AddWithValue("?", txtSurnameNok.Text)
                    Cmd.Parameters.AddWithValue("?", txtFirstNameNok.Text)
                    Cmd.Parameters.AddWithValue("?", txtMiddleNameNok.Text)
                    Cmd.ExecuteNonQuery()
                    Dim result As Integer = Cmd.ExecuteNonQuery()
                    'commit transaction
                    transaction.Commit()
                    MsgBox("Both records are written to the db")
                Catch ex As Exception
                    transaction.Rollback()
                    MsgBox("Record was not inserted")
                End Try
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try

    thank for your time

    Wednesday, May 23, 2018 9:16 PM
  • @karen, when I ran your code, it crashes here Dim Transaction =cn.BeginTransaction()" with a message transaction is closed"


    Wednesday, May 23, 2018 9:24 PM
  • I did not show much code and most was pseudo. 

    But you did not follow the almost only real code which I wrote. 

    Catch ex as exception
       Messagebox.show(ex.message)

    Or while you are still developing

    Catch ex as exception
       Messagebox.show(ex.ToString)

    Then there is given why it was not inserted. (A constraint or whatever)

    Be aware that is not related to your question in this thread. 


    Success
    Cor


    Wednesday, May 23, 2018 9:30 PM
  • There's a typo on your second CommandText:

    "INSERT INTO PatientsNOk(Title, Surname.FirstName, MiddleName)

    That period between Surname and FirstName should be a comma.

    As for the underline, change the code to:

    Catch ex As Exception
        If transaction IsNot Nothing Then transaction.Rollback()


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Wednesday, May 23, 2018 10:36 PM
    Moderator
  • @karen, when I ran your code, it crashes here Dim Transaction =cn.BeginTransaction()" with a message transaction is closed"


    Sorry, try this flow (as in the highlighted) where I declare the transaction as Nothing, then set it up after opening the connection


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, May 23, 2018 10:38 PM
    Moderator
  • @Karen, In your previous code, actually the error message is "connection is closed" and not transaction is closed,my bad. with "Dim Transaction =cn.BeginTransaction()" highlighted. I am not sure if this will help shade more focus on the connection. Thanks
    Thursday, May 24, 2018 10:46 AM
  • Alobi,

    Please don't always only look at the last message. 

     

    Reed has given the reason why the insert won't go one minute before Karen. 

     


    Success
    Cor

    Thursday, May 24, 2018 11:00 AM