Andy Gybels Andy Gybels - 2 months ago 7
C# Question

Databinding not updating database until after move to previous or next record

Creating a project for school, I have a form with some user controls.

3 textboxes a checkbox and 2 buttons for navigating through the records.

When I change the text on one of the textboxes, the data will only reflect to the database when I click the "Next" or "Prev" button.

public partial class Navigeren : UserControl
{
private readonly SqLiteDataAccess _sqLiteDataAccess;
private Timer _saveTimer;
private DataViewManager _dsView;
public Navigeren()
{
InitializeComponent();
_sqLiteDataAccess = new SqLiteDataAccess();
DataBinding();
_saveTimer = new Timer {Interval = 1000};
_saveTimer.Tick += _saveTimer_Tick;
_saveTimer.Start();
}

private void _saveTimer_Tick(object sender, EventArgs e)
{
_sqLiteDataAccess.UpdatePersonen(_dsView.DataSet);
// Tried using SqLiteDataAccess.PersonenDataSet gives me the same result.
}

private void DataBinding()
{
_dsView = SqLiteDataAccess.PersonenDataSet.DefaultViewManager;
textId.DataBindings.Add("Text", _dsView, "Personen.id", false, DataSourceUpdateMode.OnPropertyChanged);
textNaam.DataBindings.Add("Text", _dsView, "Personen.name", false, DataSourceUpdateMode.OnPropertyChanged);
textAdres.DataBindings.Add("Text", _dsView, "Personen.adres", false, DataSourceUpdateMode.OnPropertyChanged);
checkGehuwd.DataBindings.Add("Checked", _dsView, "Personen.gehuwd", false, DataSourceUpdateMode.OnPropertyChanged);
}

private void buttonNext_Click(object sender, EventArgs e)
{
CurrencyManager cm = (CurrencyManager)this.BindingContext[_dsView, "Personen"];
if (cm.Position < cm.Count - 1)
{
cm.Position++;
}
}

private void buttonPrev_Click(object sender, EventArgs e)
{
if (this.BindingContext[_dsView, "Personen"].Position > 0)
{
this.BindingContext[_dsView, "Personen"].Position--;
}
}
}


Sorry for my english.

Greetings Andy

EDIT:
SQLiteDataAccess:

class SqLiteDataAccess
{
private SQLiteConnection _sqliteconnection;
private string _database = @"Database.sqlite";

private static DataSet _personenDataSet;
public static DataSet PersonenDataSet
{
get { return _personenDataSet; }
set { _personenDataSet = value; }
}

public SqLiteDataAccess()
{
OpenConnection();
CreateTable();
CreateTableGeslachten();
CreateTableLanden();
FillDataSet();
}

private void OpenConnection()
{
if (!File.Exists(_database))
SQLiteConnection.CreateFile(_database);
_sqliteconnection = new SQLiteConnection("Data Source=" + _database + ";Version=3;");
_sqliteconnection.Open();
}

private void CreateTable()
{
try
{
string sql = "CREATE TABLE Personen (" +
"id INTEGER PRIMARY KEY AUTOINCREMENT, " +
"name VARCHAR, " +
"adres VARCHAR, " +
"gehuwd INT, " +
"land INT, " +
"geslacht INT, " +
"telnr VARCHAR, " +
"studies VARCHAR, " +
"geboorteDatum DATETIME, " +
"foto BLOB)";

SQLiteCommand command = new SQLiteCommand(sql, _sqliteconnection);
command.ExecuteNonQuery();
}
catch (SQLiteException sle)
{

}
}

private void CreateTableGeslachten()
{
try
{
string sql = "CREATE TABLE Geslachten (" +
"id INTEGER PRIMARY KEY AUTOINCREMENT, " +
"geslacht TEXT," +
"FOREIGN KEY(id) REFERENCES Personen(geslacht));";

SQLiteCommand command = new SQLiteCommand(sql, _sqliteconnection);
command.ExecuteNonQuery();
InsertTableGeslachten();
}
catch (SQLiteException sle)
{

}
}

private void InsertTableGeslachten()
{
string sql = "INSERT INTO Geslachten(geslacht) VALUES('Man'), ('Vrouw');";
SQLiteCommand command = new SQLiteCommand(sql, _sqliteconnection);
command.ExecuteNonQuery();
}

private void CreateTableLanden()
{
try
{
string sql = "CREATE TABLE Landen (" +
"id INTEGER PRIMARY KEY AUTOINCREMENT, " +
"land TEXT," +
"FOREIGN KEY(id) REFERENCES Personen(land));";

SQLiteCommand command = new SQLiteCommand(sql, _sqliteconnection);
command.ExecuteNonQuery();
InsertTableLanden();
}
catch (SQLiteException sle)
{
}
}

public void Insert(string name, string adres, bool gehuwd, int land, int geslacht, string telnr, string studies,
string geboorteDatum, byte[] foto)
{
var sql =
new StringBuilder(
"insert into Personen (name, adres, gehuwd, land, geslacht, telnr, studies, geboorteDatum) values ('");
sql.Append(name).Append("','")
.Append(adres).Append("','")
.Append(Convert.ToInt32(gehuwd)).Append("','")
.Append(land).Append("','")
.Append(geslacht).Append("','")
.Append(telnr).Append("','")
.Append(studies).Append("','")
.Append(geboorteDatum).Append("');");
// .Append(foto).Append(");");
SQLiteCommand command = new SQLiteCommand(sql.ToString(), _sqliteconnection);
command.ExecuteNonQuery();
}

public void FillDataSet()
{
SqLiteDataAccess.PersonenDataSet = new DataSet();
try
{
string query = "select * from Personen";
SQLiteDataAdapter da = new SQLiteDataAdapter(query, _sqliteconnection);
da.Fill(PersonenDataSet, "Personen");
string query2 = "select id, geslacht from geslachten";
SQLiteDataAdapter da2 = new SQLiteDataAdapter(query2, _sqliteconnection);
da2.Fill(PersonenDataSet, "Geslachten");
string query3 = "select id, land from Landen";
SQLiteDataAdapter da3 = new SQLiteDataAdapter(query3, _sqliteconnection);
da3.Fill(PersonenDataSet, "Landen");
}
catch (Exception ex)
{

}
}

public void UpdatePersonen(DataSet ds)
{
try
{
string query = "select * from Personen";
SQLiteDataAdapter da = new SQLiteDataAdapter(query, _sqliteconnection);
SQLiteCommandBuilder sqLiteCommandBuilder = new SQLiteCommandBuilder(da);
da.Update(ds, "Personen");
}
catch (Exception ex)
{
MessageBox.Show("Test");
}
}

Answer

You don't need to change the position, you just need to call EndCurrentEdit method of the BindingManagerBase before save:

this.BindingContext[datasource, "datamember"].EndCurrentEdit();

Some Notes

Use BindingSource.

I recommend using a BindingSource component as data source which you want to use for data-binding. Then set the DataSource and DataMember of the BindingSource to the values which you want:

  • It enables you to perform data-binding at design time.
  • It enables you to use its methods and properties to navigation, add, remove and so on.
  • You can use it's sorting and filtering properties.

Use BindingNavigator.

Also use BindingNavigator control as a navigation toolbar. It's enough to set its BindingSource property to the BindingSource component which you used for data-binding, then its navigation button will do the job of navigation for you.

Also take a look at: