Sunday, September 25, 2005 7:36 PM
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 = updateCMDTry 'Refresh the DataSet
CompDataAdapter.Fill(CompDataSet, "CompanyInfo")' Write Changes to Db File
MsgBox("Company was NOT De-Activated")End Try End Sub
Monday, September 26, 2005 5:50 AMModerator
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...
Paul June A. Domag
Sunday, October 02, 2005 6:47 PMNo, 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
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.
Monday, October 03, 2005 1:13 AMModerator
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:
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...
Paul June A. Domag