Not Updating Access Database

A

askobiak

Member
Joined
Apr 5, 2003
Messages
6
Location
Seattle
Im building a simple database. On a form I created a Text box and connected (using DataBindings) to Access 2000 Database.

I have successfully created OleDbConnection, OledDbDataAdapter and DataSet. When I scroll through the records, proper data is displayed on all controlls.

How do I update the database when I modify data in the text?

I used the following statement out of the book:
OledDbDataAdapter.Update(DataSet)
When I check my database, the values didnt still didnt change. I made sure that ContinueUpdateOnError parameter is set to True.

I also tried this:

Try
Me.DA_TravelJ.Update(Me.DS_TravelJ1, "tblMain")
Me.DS_TravelJ1.AcceptChanges()
Catch
Dim err As Exception
MsgBox(err)
End Try

I dont get any exception errors either. This table does have a relation to other table, but that seems to work fine.

Any suggestions?
Thanks
 
Are you creating your dataadapter via the configuration wizard?
That is, is your sql being written for you by the "query builder"?
You may (probably) will need to write your own insert, update, and delete statements since you have more than one table included in your db.
 
Yes, I created DA via the configuration wizard and yes the sql was written by "query builder".

I tried to create another simple db with one table - same results.
I put this statement MsgBox(Me.DS_TravelDB1.HasChanges()), not matter what it brings me False value back. Its as if it doesnt see any changes.

How can I create my SQL statement without SQL connection? I dont have SQL server.

Thanks for help.
Anatoly
 
SQL desktop engine comes with VS.NET. Look in you VS.NET folder, then Setup, then MSDE
 
I have installed MSSQL and its running. What next? I tried to use wizard to add SqlConnection, then SqlDataAdapter. What do I choose for OLE DB Provider when I configure SqlDataAdapter?

Im new to VB.NET, so please bear with me.

Thank you,
Anatoly
 
Your messagebox appears to evaluate your dataset for changes.
You should be able to use the .mdb database.
Please post your code so we can see what is happening.
Perhaps we can discern how to fix the problem.
 
Just cut and paste that chunk of code that represents those portions surrounding: the fill statement, the databinding statement and the update statement.
 
A lot of this code was obviously created by wizard.


Me.DS_TravelDB1 = New DBTempTest.DS_TravelDB()
Me.Con_TravelDB = New System.Data.OleDb.OleDbConnection()
Me.DA_TravelDB = New System.Data.OleDb.OleDbDataAdapter()
Me.OleDbSelectCommand1 = New System.Data.OleDb.OleDbCommand()
Me.OleDbInsertCommand1 = New System.Data.OleDb.OleDbCommand()
Me.OleDbUpdateCommand1 = New System.Data.OleDb.OleDbCommand()
Me.OleDbDeleteCommand1 = New System.Data.OleDb.OleDbCommand()

CType(Me.DS_TravelDB1, System.ComponentModel.ISupportInitialize).BeginInit()

Me.txtPark.DataBindings.Add(New System.Windows.Forms.Binding("Tag", Me.DS_TravelDB1, "tblMain.ID"))
Me.txtPark.DataBindings.Add(New System.Windows.Forms.Binding("Text", Me.DS_TravelDB1, "tblMain.ParkName"))

Me.DS_TravelDB1.DataSetName = "DS_TravelDB"
Me.txtVisitDate.DataBindings.Add(New System.Windows.Forms.Binding("Tag", Me.DS_TravelDB1, "tblMain.ID"))
Me.txtVisitDate.DataBindings.Add(New System.Windows.Forms.Binding("Text", Me.DS_TravelDB1, "tblMain.VisitDate"))


Me.Con_TravelDB.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:\Documen" & _
"ts and Settings\Owner\Anatoly\Programming\VB.NET\DBTempTest\TravelJ.mdb;Mode=Sha" & _
"re Deny None;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Regis" & _
"try Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Dat" & _
"abase Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk T" & _
"ransactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Databas" & _
"e=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Dont Copy Locale on Compact=" & _
"False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False"
Me.DA_TravelDB.ContinueUpdateOnError = True



******* THIS IS WHAT I ADDED *************
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Me.DA_TravelDB.Fill(Me.DS_TravelDB1)
End Sub

Private Sub cmdUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdUpdate.Click
Dim err As Exception

MsgBox(Me.DS_TravelDB1.HasChanges())
Try
Me.DA_TravelDB.Update(Me.DS_TravelDB1)
Catch
MsgBox(err)
End Try
End Sub
 
the rowstate is changed only when u move off the current row. so if u change
the value in the textbox, move to the next row, and then examine the
rowstate property, u will find that it has indeed changed to "Modified"

To cause the rowstate to change without moving off the row, call the
EndCurrentEdit method on the underlying bindingcontext object that u create,
or if uve not specifically created a bindingcontext, use the following
syntax (possibly in the click event of the OK/Save button)

Me.BindingContext(dataset, "tablename").EndCurrentEdit
 
Thank you. .EndCurrentEdit did it. Its amazing that writers of so many books omit small things like this and expect beginners to know.

Thanks again.
SK
 
Back
Top