Unable to modify data of an MS Access database with VB.NET 2005

[drainville]

Hi Guys,

Context :

It has been now at least 5 days that i've been searching to find a solution for my problem. Just to let you know, i'mn not a newbie in programming and have been working for a while in vb.net and .net environmnent.

Problem:

For some reason, while developing a vb.net 2005 application, when I try to update, delete or insert data into a table in an ms access database (2000 or 2002), all the execution is done successfully (without errors or exceptions) but the data is still unmodified. How do I modifiy the data? I tried with a dataset and calling update after modifying the dataset (and yes the rowstate was set to modified before calling the update) and I received a result of 1 row changed but nothign has changed on the access db. I tried with a Execute non query with a simple update query and again, the query returns 1 row affected (at least) but on ms access db side, nothing has changed. Weird enought, when I update the data with the visual studio 2005 network tab, the data remains updated which makes this really weird.

BTW: i tried the same code with an SQL Server connection and the update is permanent on the db side..

I would really appreciate any help or any tips that could come up ..

Here's the code I use for a simple update:

Imports System.Data
Imports System.Data.OleDb



Public Class Form1

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim conn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\Todo.mdb"
Dim con1 As OleDb.OleDbConnection = New OleDb.OleDbConnection(conn)

Try



con1.Open()
Dim cmdtest As OleDb.OleDbCommand = New OleDbCommand("update Exer1Arbitres set arbNom = 'test11'", con1)


msgbox(cmdtest.ExecuteNonQuery()) ' this returns 1


Catch ex As Exception ' no errors returned
MsgBox("Oops " + ex.Message.ToString)
End Try


End Sub
End Class




thanks for your time

 

[JuggaloBrotha]

i think i see the error, you're not specifying a primary key:
"update Exer1Arbitres set arbNom = 'test11'", con1

"update Exer1Arbitres set arbNom = 'test11' WHERE PrimKeyField = SomeValue", con1

 

[drainville]

Thanks for the reply but the query is still valid even if you dont specify a PK. It will only affect all the records in the table (it did in the SQL Server).

any other thoughts?

 

[munk]

its because your rebuilding the application each time you are testing it so it is reverting back to the origional database file, check \bin\debug\databasename.mdb, after you have added data etc.

 

[Arg81]

Munk is correct, here's a link to the post by Cjard explaining the problem:

http://vbdotnetforums.com/showthread.php?p=54914

 

[cjard]

BTW: i tried the same code with an SQL Server connection and the update is permanent on the db side..

You get the same "not updating" with a SQL server MDF file (which is copied out and attached each time the project is run) but not with a SQL Server accessed via tcp connection.. As you might expect, your app doesnt overwrite a SQL Server it accesses via tcp

 

[drainville]

Quite right! The problem is exactly this, the DB file is copied over the release or debug directory so all updates are done on the copied DB which is overwritten each time I recompile.. duhh!

Thanks guys, really helped!