[RESOLVED] [2005] Help: Dataset not updating database

[Makavelli]

I'm using a typed dataset to retrieve data from an access db. The retrieval works fine; the disconnected datatable updates fine but the dataset does not update the database when I use adapter.dataset.update function. Please help. Neither works.

Dim myDataRow As DataRow
 
myDataRow = Me.DsDefault.tb_users.NewRow()
myDataRow("fname") = strFname
myDataRow("lname") = strLname
myDataRow("phone") = strPhone
myDataRow("address") = strAddress
myDataRow("city") = strCity
myDataRow("current_flag") = intFlag
myDataRow("state") = strState
myDataRow("zip") = strZip
myDataRow("fax") = strFax
myDataRow("contact") = strContact
myDataRow("date_added") = strDateAdded
 
Me.DsDefault.tb_users.Rows.Add(myDataRow)
Debug.WriteLine("After Add Row: " & myDataRow.RowState.ToString)
 
Me.tb_usersTableAdapter.Update(Me.DsDefault.tb_users)
 
 
'======Update datarow routine ========
Dim myDataRow As DataRow = Me.DsDefault.tb_users.Rows(intRow)
 
myDataRow = Me.DsDefault.tb_users.NewRow()
myDataRow("fname") = strFname
myDataRow("lname") = strLname
myDataRow("phone") = strPhone
myDataRow("address") = strAddress
myDataRow("city") = strCity
myDataRow("current_flag") = intFlag
myDataRow("state") = strState
myDataRow("zip") = strZip
myDataRow("fax") = strFax
myDataRow("contact") = strContact
myDataRow("date_added") = strDateAdded
 
Debug.WriteLine("After Edit Row: " & myDataRow.RowState.ToString)
 
Me.tb_usersTableAdapter.Update(Me.DsDefault.tb_users)

[jmcilhinney]

Your alleged Update routine is not updating any row. You get an existing row first but then you create a new row and set its fields instead. I'm guessing that you've copied and pasted and forgotten to delete that row.

'======Update datarow routine ========
Dim myDataRow As DataRow = Me.DsDefault.tb_users.Rows(intRow) '<- This gets an existing row.
 
myDataRow = Me.DsDefault.tb_users.NewRow() '<- This creates a new row so the reference to the existing row is lost.

All the subsequent code is setting the fields of the new row, which never gets added to the table, thus there are no changed rows for the Update call to affect.

[Makavelli]

Thanks for your response jmcilhinney. The code I posted are 2 different sub-routines. The Dataset gets updated but doesn't update the database. Is there anything I'm doing wrong? Please help.

AddNewUser() '<<< Doesn't work
UpdateOldUser(2) '<<<Doesn't work either.
 
Private Sub AddNewUser()
Dim myDataRow As DataRow
 
myDataRow = Me.DsDefault.tb_users.NewRow()
myDataRow("fname") = strFname
myDataRow("lname") = strLname
myDataRow("phone") = strPhone
myDataRow("address") = strAddress
myDataRow("city") = strCity
myDataRow("current_flag") = intFlag
myDataRow("state") = strState
myDataRow("zip") = strZip
myDataRow("fax") = strFax
myDataRow("contact") = strContact
myDataRow("date_added") = strDateAdded
 
Me.DsDefault.tb_users.Rows.Add(myDataRow)
Debug.WriteLine("After Add Row: " & myDataRow.RowState.ToString)
 
Me.tb_usersTableAdapter.Update(Me.DsDefault.tb_users)
End Sub
 
Private Sub UpdateOldUser(ByVal intRow As Integer)
 
'======Update datarow routine ========
Dim myDataRow As DataRow = Me.DsDefault.tb_users.Rows(intRow)
 
myDataRow = Me.DsDefault.tb_users.NewRow()
myDataRow("fname") = strFname
myDataRow("lname") = strLname
myDataRow("phone") = strPhone
myDataRow("address") = strAddress
myDataRow("city") = strCity
myDataRow("current_flag") = intFlag
myDataRow("state") = strState
myDataRow("zip") = strZip
myDataRow("fax") = strFax
myDataRow("contact") = strContact
myDataRow("date_added") = strDateAdded
 
Debug.WriteLine("After Edit Row: " & myDataRow.RowState.ToString)
 
Me.tb_usersTableAdapter.Update(Me.DsDefault.tb_users)
End Sub

[jmcilhinney]

Well, the second one can't possibly work for the reason I have already stated. Why do you say it doesn't work? Have you done a query afterwards to see if the data is there, or are you exiting the application and then finding it's not there when you restart? The simple way to know whether it is working or not is to use the Update method. It is not a procedure, but rather a function. That means that it returns a value, and the value it returns is the number of rows affected by the call. You can simply do this:

MessageBox.Show(Me.tb_usersTableAdapter.Update(Me.DsDefault.tb_users).ToString())

A message will popup telling you how many rows were deleted, inserted or updated. If that message says zero then you haven't saved any changes, otherwsie you have. Note that a more professional way to do it would be something like this:

Trace.WriteLine(Me.tb_usersTableAdapter.Update(Me.DsDefault.tb_users) & " rows affected")

Using this code the app will behave normally when released but while debugging it will write a message to the Immediate window telling you how many rows were affected.

[rfiddelke]

I have found that if you are debugging the application the database will not update. To acctually see the database get updated you need to run the application outside of VS.

[jmcilhinney]

I have found that if you are debugging the application the database will not update. To acctually see the database get updated you need to run the application outside of VS.

That is absolutely not true.

[rfiddelke]

I think this only happens with SQL express. When debugging it runs against a copy of the database. I know I was having a similar problem and that was the issue. Sorry for not being more specific.

[jmcilhinney]

I think this only happens with SQL express. When debugging it runs against a copy of the database. I know I was having a similar problem and that was the issue. Sorry for not being more specific.

It absolutely does run against a copy of the database, whether you're debugging or in release. When you compile the database file is copied to the output folder along with the compiled assembly. You'd hardly want to be saving data to your original database would you? The default behaviour is for the database to be copied to the output folder every time you compile, which means every time you run the run the project in the debugger. When you save data to the database it IS saved, but if you exit the app and then run it again your database will be overwritten with a new clean copy. People seem to just assume that this means that the save didn't work without checking whether it worked at the time. You can change this default behaviour in the Properties window for the database so that it will only be copied to the output folder if the origianl has changed or not at all. In my opinion the default should have been only when it has changed, but you can't have everything.

[Begineeralbert]

I have a similar Problem using Vs2005 ....and an access Database using a table adapter and update .... can you help?

[jmcilhinney]

I have a similar Problem using Vs2005 ....and an access Database using a table adapter and update .... can you help?

It's the same exact situation. The database is being overwritten each time you compile. If this is an issue then change the properties of the database. You can confirm that your data is actually being saved using the code I posted earlier, or simply run a query straight after updating and see what the database contains.

[Makavelli]

what do you mean by changing the properties of the database? What should I change? and to what should I change it to?

Thanks

[Makavelli]

Got it. You were right about it copying the database temporarily.

To Begineeralbert: Right-clicked on the access db within the solution explorer click on property. Set the Copy to output to copy if newer.

Thanks jmcilhinney.

[edubnmrg]

i have this problem with a paremterized query like
select * from table where field =?
The record loads fine, but after modifying the value in the textboxes
of the form and click save in the bindingnavigator:

bindingnavigator.endedit()
tableadapter.udpdate(dataset)

in the last sentence i get
"no valid update command" error

the dataset marks the row as modified
but if i use
dataset.acceptchanges
the modify flag is reset but the value
in the database is not changed ( i open the database in access and look the record)

[edubnmrg]

i have this problem with a paremterized query like
select * from table where field =?
The record loads fine, but after modifying the value in the textboxes
of the form and click save in the bindingnavigator:

bindingnavigator.endedit()
tableadapter.udpdate(dataset)

in the last sentence i get
"no valid update command" error

the dataset marks the row as modified
but if i use
dataset.acceptchanges
the modify flag is reset but the value
in the database is not changed ( i open the database in access and look the record)