Problems Saving Data

[Shaggy19]

I am currently having a few issues saving new information to a dataset. I could update the set, but on exiting and reopening the data all updates were gone. I found a few tips online but now get error messages when running the update command. Any help would be greatly appreciated.

This is how the code looks at present.

Private Sub Confirm_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Confirm.Click

Dim TeamInfo As DataRow = DataDataSet.Tables("Teams").NewRow()
Dim DataDataSet2 As OleDb.OleDbDataAdapter
Dim Connection As New OleDb.OleDbConnection
Dim SQLCommand As String

SQLCommand = "SELECT * FROM Teams"
Connection.ConnectionString = "Provider=SQLOLEDB;Data Source = E:\Database Manager\Database Manager\Data.sdf"

DataDataSet2 = New OleDb.OleDbDataAdapter(SQLCommand, Connection)

Dim cb As New OleDb.OleDbCommandBuilder(DataDataSet2)

TeamInfo.Item("Team Name") = Me.Team_NameTextBox.Text
TeamInfo.Item("Abbreviation") = Me.AbbreviationTextBox.Text
TeamInfo.Item("Nationality") = Me.NationalityComboBox.SelectedItem(0)
TeamInfo.Item("Tour") = Me.TourComboBox.SelectedItem(0)
TeamInfo.Item("Finance") = Me.FinanceTextBox.Text
TeamInfo.Item("ID") = Me.IDTextBox.Text

DataDataSet.Tables("Teams").Rows.Add(TeamInfo)
DataDataSet2.Update(DataDataSet, "Teams")

Me.Close()

End Sub

[Oxiegen]

Add one single line of code just before you call the Update() method on the second dataset.

DataDataSet.Tables("Teams").Rows.Add(TeamInfo)
''' Add this line, this will commit any changes made to the dataset since it was loaded
DataDataSet.AcceptChanges()
'''
DataDataSet2.Update(DataDataSet, "Teams")

[Shaggy19]

I added this in and it has stopped the code from crashing. However, it still doesn't seem to commit the data to dataset on a permanent basis.

[Oxiegen]

Ok. Well then it depends on which dataset you're using as a permanent dataset.
DataDataSet or DataDataSet2?

[Shaggy19]

DataDataSet is the permanent database. This is the "local database" that sits in the VB Project.

[Oxiegen]

Ok. Then you can try to move the call to AcceptChanges down a notch.
All DataSet, DataTables and DataRows have that method. DataDataSet.Tables("Teams").AcceptChanges()

[Shaggy19]

Tried this and seem to be getting a little further. When stepping through the project I can see that after this command the table has been updated inside the application. However, as soon as I close the application the data is lost again.

[Oxiegen]

Can you confirm that the InsertCommand property of DataDataSet2 indeed contains the SQL query for inserting new records?
If I were to guess, then that may be where the problem lies now.

[Shaggy19]

The SQL command is SQLCommand = "SELECT * FROM Teams"

Which is what the help file suggested using, however, I find it strange that you'd use the select command to add. I'd have though INSERT would have been the correct line, but I'm not sure how to formulate the line.

[Hisham-Usif]

This is my way. It works perfectly for me. The only difference is that you have to use indexes instead of strings to access the fields. Make sure the indexes I have put in red match the ones in your database table. Goodluck.

Private Sub Confirm_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Confirm.Click

Dim TeamInfo As DataRow = DataDataSet.Tables("Teams").NewRow()
Dim DataDataSet2 As OleDb.OleDbDataAdapter
Dim Connection As New OleDb.OleDbConnection
Dim SQLCommand As String

SQLCommand = "SELECT * FROM Teams"
Connection.ConnectionString = "Provider=SQLOLEDB;Data Source = E:\Database Manager\Database Manager\Data.sdf"

DataDataSet2 = New OleDb.OleDbDataAdapter(SQLCommand, Connection)

Dim cb As New OleDb.OleDbCommandBuilder(DataDataSet2)

TeamInfo(0) = Me.Team_NameTextBox.Text 
TeamInfo(1) = Me.AbbreviationTextBox.Text
TeamInfo(2) = Me.NationalityComboBox.SelectedItem(0)
TeamInfo(3) = Me.TourComboBox.SelectedItem(0)
TeamInfo(4) = Me.FinanceTextBox.Text
TeamInfo(5) = Me.IDTextBox.Text

DataDataSet.Tables("Teams").Rows.Add(TeamInfo)
DataDataSet2.Update(DataDataSet, "Teams")

Me.Close()

End Sub

[Oxiegen]

The SQL command is SQLCommand = "SELECT * FROM Teams"

Which is what the help file suggested using, however, I find it strange that you'd use the select command to add. I'd have though INSERT would have been the correct line, but I'm not sure how to formulate the line.

That's how the commandbuilder works.
You insert the SELECT query and a DataAdapter, and it spits out the queries for SELECT, INSERT, UPDATE and DELETE.
Very cool, if I may say so. :)

[Shaggy19]

Hisham, this seems to work okay until it reaches the "update" line, then it throws up an unhandled exception. Not really sure where to go from here as the code does seem to conform to all the advice online.