none
Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE RRS feed

  • Question

  • Here is my first attempt on using transaction and am getting the following error message “Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE”  Thanks for your time

    DeleteRecords()
            Dim trans As OleDbTransaction
            Dim conn As New OleDb.OleDbConnection
            Dim cmd As New OleDb.OleDbCommand
            Dim cmd1 As New OleDb.OleDbCommand
            'Call validationCheck()
            conn.ConnectionString = connstr
            conn.Open()
            cmd.Connection = conn
            cmd.CommandText = "INSERT INTO Student(Student_ID,Programtbl,Leveltbl,Arm)VALUES( @StuID, @Prog, @Class,@Arm)"
            cmd.Parameters.AddWithValue("@StuID", txtStudentID.Text)
            cmd.Parameters.AddWithValue("@Prog", cboProgram.Text)
            cmd.Parameters.AddWithValue("@class", cboLevel.Text)
            cmd.Parameters.AddWithValue("@Arm", cboArm.Text)
    
    
    
            Cmd1.Connection = conn
            Cmd1.CommandText = "INSERT INTO StudentNOK(Student_ID, SurName,FirstName, MiddleName)VALUES(@StuID,@SNameNk,@FNameNk,@MidNamNK)"
            Cmd1.Parameters.AddWithValue("@StuID", txtStudentID.Text)
            Cmd1.Parameters.AddWithValue("@SNameNK", txtSurnameNK.Text)
            Cmd1.Parameters.AddWithValue("@FNameNK", txtFirstNameNK.Text)
            Cmd1.Parameters.AddWithValue("@MidNameNK", txtMiddleNameNK.Text)
            'conn.Open()
            cmd = New OleDbCommand(connstr, conn)
            Cmd1 = New OleDbCommand(connstr, conn)
            trans = conn.BeginTransaction
            cmd.Transaction = trans
            Cmd1.Transaction = trans
            ' execute the commands 
            cmd.ExecuteNonQuery()
            Cmd1.ExecuteNonQuery()
            Dim Response As DialogResult = MessageBox.Show(" already been executed." & Environment.NewLine & _
             "Proceed with transaction?", "Performing Transaction", _
                MessageBoxButtons.YesNo)
            Select Case Response
                Case Windows.Forms.DialogResult.Yes
                    trans.Commit()
                Case Windows.Forms.DialogResult.No
                    trans.Rollback()
            End Select
            conn.Close()
    

     Private Sub btnPerformTransation_Click(sender As Object, e As EventArgs) Handles btnPerformTransation.Click
            PerformTransaction()
     End Sub

     
     Private Sub DeleteRecords()
            ' Dim conn As New OleDbConnection
            conn.ConnectionString = connstr
    
            ' Clear these records if they exist.
            Dim cmd As New OleDbCommand("DELETE FROM Students WHERE SName =@StuName' DELETE FROM NOK" & _
              " WHERE Student_ID = NOKID", conn)
            Conn.Open()
            cmd.ExecuteNonQuery()
            Conn.Close()
        End Sub

    Saturday, August 26, 2017 3:03 PM

All replies

  • Hello,

    You should use one connection and one command implemented with Using statements. See the following for a simple example but don't places values into the actual command text, instead use AddWithValue, set the first command parameters, execute, clear the parameters, add parameters for the second command and execute. Your commit and Rollback are used as you are now.


    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

    Saturday, August 26, 2017 10:36 PM
    Moderator
  • I think that the lines

       cmd = New OleDbCommand(connstr, conn)
       Cmd1 = New OleDbCommand(connstr, conn)

    create new objects, therefore this cancels all of your preparations made before for CommandText and Parameters. You should reconsider the code, and probably remove these two lines.

    By the way, I think that both of the statements can be separated by ‘;’ and executed using a single command:

       cmd.CommandText = "INSERT INTO Student … ; INSERT INTO StudentNOK …"

    Then Cmd1 is not needed.


    • Edited by Viorel_MVP Sunday, August 27, 2017 7:14 AM
    Sunday, August 27, 2017 7:09 AM
  • Hi alobi,

    Based on your description, you want to use transaction in your sql statement, please take care how to use transaction, like this:

    Dim conn As New SqlConnection(Str)
            Dim cmd As SqlCommand = conn.CreateCommand()
            Dim tran As SqlTransaction
            conn.Open()
            tran = Conn.BeginTransaction()
            'Begin Transaction
            cmd.Transaction = tran
            Try
                cmd.CommandText = "sql1"
                cmd.ExecuteNonQuery()
    
                cmd.CommandText = "sql2"
                cmd.ExecuteNonQuery()
    
                'Commit Transaction
                tran.Commit()
            Catch e As Exception
                'Transaction rollback
                tran.Rollback()
            Finally
                conn.Close()
            End Try
     

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Monday, August 28, 2017 3:44 AM
    Moderator
  • @Kareminstructor, I copied the code below from the link You provided but when I tried to run the program I got a  warning at 'transaction.Rollback'. I have reviewed but could not tell where to make the necessary change. Could you please take the opportunity and look at the entire code and see if I am in the right direction, that I made the suggested changes. Thank you for your time.

     Public Sub ExecuteTransaction(ByVal connectionString As String)
    
            Using connection As New OleDbConnection(connectionString)
                Dim cmd As New OleDbCommand()
                Dim transaction As OleDbTransaction
    
                ' Set the Connection to the new OleDbConnection.
                cmd.Connection = connection
    
                ' Open the connection and execute the transaction.
                Try
                    connection.Open()
    
                    ' Start a local transaction with ReadCommitted isolation level.
                    transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted)
    
                    ' Assign transaction object for a pending local transaction.
                    cmd.Connection = connection
                    cmd.Transaction = transaction
    
                    ' Execute the commands.
                    cmd.CommandText = _
                        "Insert into Student (Student_ID, Program) VALUES (100, 'Description')"
                    cmd.ExecuteNonQuery()
                    cmd.CommandText = _
                        "Insert into StudentNOK (NOkID, Surname) VALUES (101, 'Description')"
                    cmd.ExecuteNonQuery()
    
                    ' Commit the transaction.
                    transaction.Commit()
                    Console.WriteLine("Both records are written to database.")
    
                Catch ex As Exception
                    Console.WriteLine(ex.Message)
                    ' Try to rollback the transaction
                    Try
                        transaction.Rollback()
    
                    Catch
                        ' Do nothing here; transaction is not active.
                    End Try
                End Try
                ' The connection is automatically closed when the
                ' code exits the Using block.
            End Using
             
    
    
    
        End Sub

    Monday, August 28, 2017 5:26 PM
  • Use the following to declare the transaction object

    Dim transaction As OleDbTransaction = Nothing


    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

    Monday, August 28, 2017 6:14 PM
    Moderator
  • I have made that change but when I run the program it does not insert in any of the tables "Student" or StudentNOK

    I do not get any error message ether. My complete code is show below. All helps will be appreciated.

     Public Sub ExecuteTransaction(ByVal connectionString As String)
    
            Using connection As New OleDbConnection(connectionString)
                Dim cmd As New OleDbCommand()
                Dim transaction As OleDbTransaction = Nothing
    
                ' Set the Connection to the new OleDbConnection.
                cmd.Connection = connection
    
                ' Open the connection and execute the transaction.
                Try
                    connection.Open()
    
                    ' Start a local transaction with ReadCommitted isolation level.
                    transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted)
    
                    ' Assign transaction object for a pending local transaction.
                    cmd.Connection = connection
                    cmd.Transaction = transaction
    
                    ' Execute the commands.
                    cmd.CommandText = _
                        "Insert into Student (Student_ID, Program) VALUES (100, 'Description')"
                    cmd.ExecuteNonQuery()
                    cmd.CommandText = _
                        "Insert into StudentNOK (NOkID, Surname) VALUES (101, 'Description')"
                    cmd.ExecuteNonQuery()
    
                    ' Commit the transaction.
                    transaction.Commit()
                    Console.WriteLine("Both records are written to database.")
    
                Catch ex As Exception
                    Console.WriteLine(ex.Message)
                    ' Try to rollback the transaction
                    Try
                        transaction.Rollback()
    
                    Catch
                        ' Do nothing here; transaction is not active.
                    End Try
                End Try
                ' The connection is automatically closed when the
                ' code exits the Using block.
            End Using     
        End Sub

    Tuesday, August 29, 2017 12:50 PM
  • A couple of thoughts, first thing is to assign a variable to cmd.ExecuteNonQuery which returns an integer representing how many rows in the underlying table which were affected, in this case, each execution should return 1. If 1 is not returned then there is an issue of course with the SQL or the code. If 1 is returned for each ExecuteNonQuery the first thought is each time you build the project the database is being overwritten which can happen if the database is shown in Solution Explorer and if so you need to change the property for the database “Copy to Output folder” to the option for “if newer” while the default is “copy always”.


    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

    Tuesday, August 29, 2017 2:48 PM
    Moderator
  • Thanks for the suggestion and your time, I will get with it and get back to the forum either way. Thanks
    Wednesday, August 30, 2017 7:51 AM
  • I  assign a variable to cmd.ExecuteNonQuery.  However when I ran the code it does not returned anything. I have checked through the SQL and did not see any error. The code including the sql is show below.

    Private Sub btnPerformTransation_Click(sender As Object, e As EventArgs) Handles btnPerformTransation.Click Using connection As New OleDbConnection(connstr) Dim cmd As New OleDbCommand Dim transaction As OleDbTransaction 'Call validationCheck() ' Set the Connection to the new OleDbConnection. cmd.Connection = connection ' Open the connection and execute the transaction. Try connection.Open() Catch ex As Exception

    end sub ' Start a local transaction with ReadCommitted isolation level. transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted) ' Assign transaction object for a pending local transaction. cmd.Connection = connection cmd.Transaction = transaction ' Execute the commands. cmd.CommandText = "INSERT INTO Student(Student_ID,Programtbl,Leveltbl,Arm)VALUES( @StuID, @Prog, @Class,@Arm)" cmd.Parameters.AddWithValue("@StuID", txtStudentID.Text) cmd.Parameters.AddWithValue("@Prog", cboProgram.Text) cmd.Parameters.AddWithValue("@class", cboLevel.Text) cmd.Parameters.AddWithValue("@Arm", cboArm.Text) cmd.ExecuteNonQuery() Dim rowsaffected As Integer = cmd.ExecuteNonQuery() TextBox1.Text = rowsaffected cmd.CommandText = "INSERT INTO StudentNOK(Student_ID, SurName,FirstName, MiddleName)VALUES(@StuID,@SNameNk,@FNameNk,@MidNamNK)" cmd.Parameters.AddWithValue("@StuID", txtStudentID.Text) cmd.Parameters.AddWithValue("@SNameNK", txtSurnameNK.Text) cmd.Parameters.AddWithValue("@FNameNK", txtFirstNameNK.Text) cmd.Parameters.AddWithValue("@MidNameNK", txtMiddleNameNK.Text) cmd.ExecuteNonQuery() TextBox1.Text = cmd.ExecuteNonQuery() ' Commit the transaction. transaction.Commit() MsgBox("Both records are written to database.") Console.WriteLine(ex.Message) ' Try to rollback the transaction Try transaction.Rollback() Catch ' Do nothing here; transaction is not active. End Try End Try ' The connection is automatically closed when the ' code exits the Using block. End Using


    Thursday, August 31, 2017 1:00 PM
  • Why did you do this rather then one time and on both you only ask for results on the second time of ExecutingNonQuery?


    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

    Thursday, August 31, 2017 1:06 PM
    Moderator
  • I don't Understand, could you show me an example as this is my first project using transaction, commit and roll back.  

    Thanks

    Thursday, August 31, 2017 2:56 PM
  • I don't have an ms-access database available so here is one from a sql-server database.

    Public Function AddNewCustomer(
        ByVal CompanyName As String,
        ByVal ContactName As String,
        ByVal ContactTitle As String,
        ByRef NewIdentifier As Integer) As Boolean
    
        Dim tran As SqlTransaction = Nothing
    
        Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
            Using cmd As New SqlCommand With {.Connection = cn}
                cmd.CommandText = "INSERT INTO Customers (CompanyName,ContactName,ContactTitle) " &
                                    "VALUES (@CompanyName,@ContactName,@ContactTitle); " &
                                    "SELECT CAST(scope_identity() AS int);"
    
                cmd.Parameters.AddWithValue("@CompanyName", CompanyName)
                cmd.Parameters.AddWithValue("@ContactName", ContactName)
                cmd.Parameters.AddWithValue("@ContactTitle", ContactTitle)
    
                cn.Open()
                tran = cn.BeginTransaction()
                cmd.Transaction = tran
                Try
                    NewIdentifier = CInt(cmd.ExecuteScalar)
                    Return True
                Catch ex As Exception
                    tran.Rollback()
                    Return False
                Finally
                    tran.Commit()
                End Try
            End Using
        End Using
    End Function

    The caller in a button click event first checks to see if all TextBox controls have values to pass to the add method. Upon success the new primary key is returned and a new row is added to the data source of the DataGridView.

    Private Sub cmdAdd_Click(sender As Object, e As EventArgs) Handles cmdAdd.Click
    
        If Not Panel1.Controls.OfType(Of TextBox).Where(Function(tb) String.IsNullOrWhiteSpace(tb.Text)).Any Then
            Dim ops As New DataOperations(False)
            Dim NewId As Integer = 0
            If ops.AddNewCustomer(txtCompany.Text, txtContact.Text, txtTitle.Text, NewId) Then
                Dim dt As DataTable = CType(bs.DataSource, DataTable)
                dt.Rows.Add(New Object() {NewId, txtCompany.Text, txtContact.Text, txtTitle.Text})
            Else
                ' should not happen unless there is a constraint issue but none in this code sample
                MessageBox.Show("Failed to add new row")
            End If
        Else
            MessageBox.Show("All text boxes must have data")
        End If
    End Sub


    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

    Thursday, August 31, 2017 3:24 PM
    Moderator