none
Update Command will not update to the database RRS feed

  • Question

  • I am having a problem this code will not update the database, i seems to work when the program is running and when i restart it the changes have vanished, i also cant see the changes in the database and i dont get any errors it works like everything has gone alright, please help i have been stuck at this for hours, this used to be so much more easier in vb6

    here is the code

    Dim adoRst As New ADODB.Recordset
            Dim adoConn As New OleDbConnection
            Dim adocmd As New OleDbCommand

            adoConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Bellheaven.accdb;"


            Dim i As Integer = dgvShowCheckIn.CurrentRow.Index
            Dim ObjTrans As OleDbTransaction
            Dim dataset As New OleDbDataAdapter

            adocmd.Connection = adoConn
            adocmd.CommandText = "UPDATE Bookings SET Check_In = #" & CDate(dtpCheckInDate.Value) & "# WHERE (Bookings.Booking_Id = " & CInt((dgvShowCheckIn.Item(0, i).Value)) & ")"

     


            Try
                adoConn.Open()

                adocmd.Connection = adoConn
                ObjTrans = adoConn.BeginTransaction()
                adocmd.CommandText = "UPDATE Bookings SET Check_In = #9/9/2009#  WHERE (Booking_Id = 4 )"
                adocmd.Transaction = ObjTrans

                dataset.UpdateCommand = adocmd

                dataset.UpdateCommand.Connection = adoConn

                dataset.UpdateCommand.ExecuteNonQuery()

     

                MessageBox.Show("Guest Checked in Succesfully " & adoConn.State & "   " & adocmd.CommandText, "Checked IN")

            Catch ex As OleDb.OleDbException

                MessageBox.Show(ex.ToString)

            End Try

            adocmd.Dispose()
            adoConn.Close()

    Wednesday, September 9, 2009 4:55 PM

Answers

  • Not sure I follow your code, first you set the commandtext to one thing and then you change it to another. Perhpas this just for testing.
    If you just want to to a simple update using straight SQL, there is no real need to use datasets and dataadapters.

    However, your problem comes from not commiting the transaction. You could to this like so:

                try
                {
                    using (OleDbConnection con = new OleDbConnection(cs))
                    {
                        con.Open();
                        OleDbTransaction tran = con.BeginTransaction();
                        OleDbCommand cmd = new OleDbCommand("UPDATE ... SET ... WHERE ...", con);
                        cmd.Transaction = tran;
                        cmd.ExecuteNonQuery();
                        tran.Commit();
                        con.Close();
                    }
                }
                catch (OleDbException ex)
                {
                    Console.WriteLine(ex);
                }
    HTH
    //Michael
    This posting is provided "AS IS" with no warranties.
    Thursday, September 10, 2009 7:37 AM
  • You open database transaction, but you did not commit it. All the changes to database would be lost after connection is closed. You actually do not need to open transaction at all, since you are using single SQL statement, which would be atomic anyway and all the changes will finish or not without transaction. I am also not clear why you assign one UPDATE SQL statement and then use another one for the same CommandText. Another point (which is not related to the problem) is that you declared COM-based ADO recordset for no reason.
    Val Mazur (MVP) http://www.xporttools.net
    • Marked as answer by Yichun_Feng Wednesday, September 16, 2009 1:20 AM
    Thursday, September 10, 2009 10:29 AM
    Moderator

All replies

  • Not sure I follow your code, first you set the commandtext to one thing and then you change it to another. Perhpas this just for testing.
    If you just want to to a simple update using straight SQL, there is no real need to use datasets and dataadapters.

    However, your problem comes from not commiting the transaction. You could to this like so:

                try
                {
                    using (OleDbConnection con = new OleDbConnection(cs))
                    {
                        con.Open();
                        OleDbTransaction tran = con.BeginTransaction();
                        OleDbCommand cmd = new OleDbCommand("UPDATE ... SET ... WHERE ...", con);
                        cmd.Transaction = tran;
                        cmd.ExecuteNonQuery();
                        tran.Commit();
                        con.Close();
                    }
                }
                catch (OleDbException ex)
                {
                    Console.WriteLine(ex);
                }
    HTH
    //Michael
    This posting is provided "AS IS" with no warranties.
    Thursday, September 10, 2009 7:37 AM
  • You open database transaction, but you did not commit it. All the changes to database would be lost after connection is closed. You actually do not need to open transaction at all, since you are using single SQL statement, which would be atomic anyway and all the changes will finish or not without transaction. I am also not clear why you assign one UPDATE SQL statement and then use another one for the same CommandText. Another point (which is not related to the problem) is that you declared COM-based ADO recordset for no reason.
    Val Mazur (MVP) http://www.xporttools.net
    • Marked as answer by Yichun_Feng Wednesday, September 16, 2009 1:20 AM
    Thursday, September 10, 2009 10:29 AM
    Moderator