Not able to update in VB.NET Access Please help

[DanielTradeWs]

I have this code working on VB.NET 2008 but not working in VB.NET 2010 and show no error code. When I use the query analyzer the query work good, but when I try on my code nothing happen. I can do SELECT code and that ok, it’s just the update my problem. Please is somebody can help me?

`Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Try
            Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\DB_MAINT_TRDWND.mdb"
            Dim dbConnection As System.Data.IDbConnection = New System.Data.OleDb.OleDbConnection(connectionString)

            Dim myUpdateQuery As String = "Update Navire_Clients set equipage = 'Skipper' where Num = 10"
            Dim myConnection As New OleDb.OleDbConnection(connectionString)
            Dim myCommand As New OleDb.OleDbCommand(myUpdateQuery)
            myCommand.Connection = myConnection
            myConnection.Open()
            myCommand.ExecuteNonQuery()
            myConnection.Close()
        Catch ex As Exception
            MsgBox(ex.Message.ToString)
        End Try

    End Sub`

[Jamblaster]

You have nothing wrong with your code that I can find. I copy/pasted your code and changed the connection string to connect to .accdb instead of .mdb and it ran perfectly. I created quick test Db and a windows form with just the 1 button and it ran. Below is the working code as it worked on my machine less than 1 minute ago:

Public Class Form1

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Try
            Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0 ;" & "Data Source= C:\TEST.accdb"
            Dim dbConnection As System.Data.IDbConnection = New System.Data.OleDb.OleDbConnection(connectionString)
            Dim myUpdateQuery As String = "Update Navire_Clients set equipage = 'Skipper' where Num = 10"
            Dim myConnection As New OleDb.OleDbConnection(connectionString)
            Dim myCommand As New OleDb.OleDbCommand(myUpdateQuery)
            myCommand.Connection = myConnection
            myConnection.Open()
            myCommand.ExecuteNonQuery()
            myConnection.Close()
        Catch ex As Exception
            MsgBox(ex.Message.ToString)
        End Try
    End Sub
End Class

[G_Waddell]

Hi,
Try debug with break points and step through it, paying particular attention to the connection string values. Also, check your access Database isn't open elsewhere or locked - This could prevent the update from happening

[DanielTradeWs]

Thank you JamBlaster to take time for me
Still not work, may be is because I have access 2002 ? Or somthing need to be unblock on visual studio or access to write on db ?
No boddy have and Idea

[DanielTradeWs]

Hi G Waddell, thank you...
I check my access in security and notting is locked. Also I follow the step and no warning or error appen. The code look to work correctly. To check if I am locked I was to Tools-Secirity-Access autorisation and all option are checked.
Have you other idea ?
My DB is Access 2002
Thanks

[DanielTradeWs]

I just see that the data is changed in the dataset, but not in the DB... I miss sumthing here ?

[G_Waddell]

Hi
Have you tried running the update statement directly inside your database?
Also, that statement is not being dynamically changed ie. you are always setting the equipage field to Skipper on the record(s) where the Num field = 10

[Jamblaster]

The myCommand.ExecuteNonQuery() line is what updates the database. This event handler is pretty small. Have you tried debugging it with breakpoints line by line and watching what happens at each line. This might give you a better idea of where it gets off track.

[DanielTradeWs]

Thank you Guy for your answer,
Yes I make test with brake point step by step.
No problem occur.. Also the catch statement don't catch notting bad.
What I note, the dataset is changing ok. If I close and reopen my form, the data look like changed, but in the database not. If I refresh the data base before restarting it the old value come back.
My true request is more complicate and is why I try with this simple one.
I think I maybe do something bad with the dataset or think like this when I install it or modified it... But don't know what... I will try to make a new form to see if working or not.
If you have any ideas....

Thanks

[G_Waddell]

Dim myUpdateQuery As String = "Update Navire_Clients set equipage = 'Skipper' where Num = 10"
Should you not be changing this update query dynamically? Basically you have hardcoded in that the same record(s) will be updated I would expect to see something like this:
Dim myUpdateQuery As String = "Update Navire_Clients set equipage = '" &equipageValue &"' where Num = " &NumValue

Or if you are using parameters this:
Dim myUpdateQuery As String = "Update Navire_Clients set equipage = @equipage where Num = @Num"

[DanielTradeWs]

Hi all!!!
Hi finaly find the problem!!! And is not a problem... During the debug session, vs open a temporary access data base on a file named DEBUG... and close it after stopping the application. Completly different from when we work with SqlServer and Oracle.
Incredible to loose so much time with simple think like this one...
Mouahahahahahaha!!!!
Aniway guy!!! Thanks for your help G_Waddell and Jamblaster