VB.NET Dataset Update

[jackb86]

I have a VB.NET application that has a ole connection, adapter and dataset. UI is a form that has text boxes that are populated from the database (DbAdapter1.Fill(Dataset)).

On the leave event for the textbox I run this code:
OleDbConnection1.Open()
Debug.WriteLine(dataset.RBIGoals.Rows(0).RowState) 'returns Unchanged
dataset.RBIGoals.Rows(0).BeginEdit()
dataset.Tables("RBIGoals").Rows(0).Item("fldname") = CDbl(tbfield.Text)
Debug.WriteLine(dataset.RBIGoals.Rows(0).RowState) 'returns Unchanged
dataset.RBIGoals.Rows(0).EndEdit()
Debug.WriteLine(dataset.RBIGoals.Rows(0).RowState) 'returns Modified
dataset.RBIGoals.Rows(0).AcceptChanges()
Debug.WriteLine(dataset.RBIGoals.Rows(0).RowState) 'returns Unchanged
DbAdapter1.Update(dataset)
Debug.WriteLine(dataset.RBIGoals.Rows(0).RowState) 'returns Unchanged
OleDbConnection1.Close()

If I look at the data in the dataset it is updated, hoever the data in the underlying database (MS Access) never gets updated.

 

[RiverGuy]

You need to study up on ADO.net. To update a database from a DataSet, you need to call your DataAdapter's .Update method. But you also need to be sure your DataAdapter does, in fact, have its update, insert, and delete command properties set.

 

[jackb86]

Well that all makes perfect sense to me, but I thought it is all configured correctly.

I added the connection by drag and dropping the connection from the server explorer, as I did the adapter. The data set was added via the data menu (generate dataset). I of this completed without a problem or error message.

What am I missing here?

 

[RiverGuy]

Make a button to save your changes. Call the .Update() method of the DataAdapter. Just changing the values won't push them back to the database.

 

[jackb86]

I do that via the leave event of the textbox. I call DbAdapter1.Update(dataset).

 

[RiverGuy]

Check to see if you have propert Insert and Update commands for your DataAdapter.

 

[jackb86]

The insert and update commands ( as well as the select and delete commands) were generated automatically by draging and dropping the table on the form. There are listed below.
I assume they are correct by virture of the fact that VB.NET generated them, the 'big assume'!

Insert:
INSERT INTO RBIGoals(AnnivMOnoth, AppliedDate, BuyerApptConvRate, BuyerAvgSalePrice, BuyerCommPct, BuyerContactConvRate, BuyerPctofTotalGCI, BuyerSoldConvRate, Cap, EO, ExpensePct, GoalsUniqueID, NetINcome, SellerAgvSalePrice, SellerApptConvRate, SellerCommPct, SellerContactConvRate, SellerListingConvRate, SellerPctofTotalGCI, Split, UniqueDBID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

Update:
UPDATE RBIGoals SET AnnivMOnoth = ?, AppliedDate = ?, BuyerApptConvRate = ?, BuyerAvgSalePrice = ?, BuyerCommPct = ?, BuyerContactConvRate = ?, BuyerPctofTotalGCI = ?, BuyerSoldConvRate = ?, Cap = ?, EO = ?, ExpensePct = ?, GoalsUniqueID = ?, NetINcome = ?, SellerAgvSalePrice = ?, SellerApptConvRate = ?, SellerCommPct = ?, SellerContactConvRate = ?, SellerListingConvRate = ?, SellerPctofTotalGCI = ?, Split = ?, UniqueDBID = ? WHERE (UniqueDBID = ?) AND (AnnivMOnoth = ? OR ? IS NULL AND AnnivMOnoth IS NULL) AND (AppliedDate = ? OR ? IS NULL AND AppliedDate IS NULL) AND (BuyerApptConvRate = ? OR ? IS NULL AND BuyerApptConvRate IS NULL) AND (BuyerAvgSalePrice = ? OR ? IS NULL AND BuyerAvgSalePrice IS NULL) AND (BuyerCommPct = ? OR ? IS NULL AND BuyerCommPct IS NULL) AND (BuyerContactConvRate = ? OR ? IS NULL AND BuyerContactConvRate IS NULL) AND (BuyerPctofTotalGCI = ? OR ? IS NULL AND BuyerPctofTotalGCI IS NULL) AND (BuyerSoldConvRate = ? OR ? IS NULL AND BuyerSoldConvRate IS NULL) AND (Cap = ? OR ? IS NULL AND Cap IS NULL) AND (EO = ? OR ? IS NULL AND EO IS NULL) AND (ExpensePct = ? OR ? IS NULL AND ExpensePct IS NULL) AND (GoalsUniqueID = ?) AND (NetINcome = ? OR ? IS NULL AND NetINcome IS NULL) AND (SellerAgvSalePrice = ? OR ? IS NULL AND SellerAgvSalePrice IS NULL) AND (SellerApptConvRate = ? OR ? IS NULL AND SellerApptConvRate IS NULL) AND (SellerCommPct = ? OR ? IS NULL AND SellerCommPct IS NULL) AND (SellerContactConvRate = ? OR ? IS NULL AND SellerContactConvRate IS NULL) AND (SellerListingConvRate = ? OR ? IS NULL AND SellerListingConvRate IS NULL) AND (SellerPctofTotalGCI = ? OR ? IS NULL AND SellerPctofTotalGCI IS NULL) AND (Split = ? OR ? IS NULL AND Split IS NULL)

 

[RiverGuy]

OK. Sorry, I see now. In your original code. It is hard to read when one doesn't use the

Code Format.

Anyhow, you are accepting changes before you are doing the update. It needs to be done before.

 

[jackb86]

RiverGuy, Thanks I love it when it is that simple!!

This is part of a magration from VB6 to VB.NET so I am struggling with some of the .NET data access concepts. IS the concept here that the code 'update' sends the changes from the adapter to the dataset then the acceptchanges sends then to the database?