Data doesn't update

Question

MS Access 2000 dB,  table name CompanyInfo.  field to update is called Activerec
defined as datatype Yes/No   format = True/False.

What I am expecting the following sub routine to do is Query the db for records where the ActiveRec field is True.  This should return only one record to the dataset. Then using the Update query I want to set that Activerec field to False and update back to the database.

This routine runs through OK but the Activerec field isn't getting updated as False.
any idea why ?

Private Sub DeActivate_Comp() Dim cb As New OleDbCommandBuilder(CompDataAdapter) ' Create SQL Commands Dim selectSQL As String = "Select * From CompanyInfo Where Activerec = False" Dim updateSQL As String = "Update CompanyInfo Set Activerec = True Where Activerec = False" ' Create Select command Dim selectCMD As OleDbCommand = New OleDbCommand(selectSQL, CompDataConnection) CompDataAdapter.SelectCommand = selectCMD ' Create Update command Dim updateCMD As OleDbCommand = New OleDbCommand(updateSQL, CompDataConnection) CompDataAdapter.UpdateCommand = updateCMD Try 'Refresh the DataSet cb.RefreshSchema() CompDataAdapter.Fill(CompDataSet, "CompanyInfo") ' Write Changes to Db File CompDataAdapter.Update(CompDataSet, "CompanyInfo") Catch MsgBox("Company was NOT De-Activated") End Try End Sub

Answer 1

Hi,

Did you make a call to AcceptChanges() method anywhere in your program? Coz when you call acceptChanges before you issue an update, the changes would not be reflected in the database...

 

 

cheers,

Paul June A. Domag

Answer 2

No,  there is no call to AcceptChanges()

What I have tried since is to use the Toolbox and create all objects from there.
ie; connection, adapter,dataset and commands.

I have created specific commands and set the command.text at design time.
ie; comp1select is a command where the text =
"SELECT from CompanyInfo WHERE Activerec = True"
comp1update is a command where the text =
"UPDATE CompanyInfo SET Activerec = False"

Now If I understand this all correctly,  I should just be able to specify the command as follows.

CompAdatper1.Selectcommand = comp1select

CompAdapter1.Fill(Compds1, "CompanyInfo")

I would expect that this will return 1 record to the DataSet from my Access dB because I only have one record in that table where the Activerec field is True.
Then I expect that, since I have defined the text for the update command
"comp1update" and specified that command name on the Adapter, when I run the following

CompAdapter1.Updatecommand = comp1update

Shouldn't that automatically update the data the table and set the Activerec field
to false ?

As I am writing this I think I see that I am mixed up somewhere.
between filling the dataset and doing the update I am not changing the value of
the Activerec field in the dataset.

If I changed the value in the DataSet with the following line

Compds1.Tables.Item("CompanyInfo").Rows(0).Item("Activerec")=False
What is ever the point of creating the Updatecommand.text ?
 
Wouldn't I always just change the values of the fields in my dataset and just call the update feature of the DataAdapter ?

Thanks for your patience.

Tattoo

Answer 3

Hi,

No, the CompAdapter1.Updatecommand = comp1update statement will not automatically update the database. The updateCommand will only be called when you call the adapters' update command to a dataset...

myAdapter.Update(ds1) '' This action would automatically call the update command...

BTW, in your example, if you only want to call your update command then you can simply execute it directly from your command:

comp1Update.ExecuteNonQuery()

Also, modifying the record by Compds1.Tables.Item("CompanyInfo").Rows(0).Item("Activerec")=False doesn't actually change the database if you don't have an UpdateCommand...

 

 

cheers,

Paul June A. Domag