There is a database with the following structure (simplified):

My program displays one of these tables with the ability to edit the content (I use WinForm). Nearby is a button that sends the modified table back to the database.

The problem is that if you use the generated commands (OleDbCommandBuilder), then when sending data to the database I get an error indicating the columns from other tables.

For example. If I change something in the "disease" table: In the DataTable "Table" there is no DataColumn "Patient number" for this SourceColumn "Patient number".

If I change something in the "visits" table: INSERT INTO syntax error. Or the same with UPDATE or DELETE commands.

Obviously, you need to make commands manually. But from the examples on MSDN it is not clear how to describe the relationships between the tables. Articles themselves:

https://msdn.microsoft.com/ru-ru/library/system.data.oledb.oledbdataadapter.insertcommand(v=vs.110).aspx https://msdn.microsoft.com/ru-ru/library/ system.data.oledb.oledbdataadapter.updatecommand (v = vs.110) .aspx https://msdn.microsoft.com/ru-ru/library/system.data.oledb.oledbdataadapter.deletecommand(v=vs.110) .aspx

Actually, help make the right commands for the tables "disease" and "visit". Or at least explain the principle.

Even better, if you know a simpler way.

If it is important, the preparation of the form:

protected string conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=db.mdb"; protected OleDbConnection connection = new OleDbConnection(); protected OleDbDataAdapter adapter; protected OleDbCommandBuilder cBuilder; DataSet dataSet; public Form1() { InitializeComponent(); connection.ConnectionString = conStr; adapter = new OleDbDataAdapter("SELECT * FROM TABLE_NAME", connection); dataSet = new DataSet(); cBuilder = new OleDbCommandBuilder(adapter); adapter.Fill(dataSet); connection.Open(); adapter.UpdateCommand = cBuilder.GetUpdateCommand(true); adapter.InsertCommand = cBuilder.GetInsertCommand(true); connection.Close(); }

I save the data from the table to the database:

 private void saveButton_Click(object sender, EventArgs e) { adapter.Update(dataSet); }

The boot method seems to be irrelevant.

It was a mistake to use MS Access. Since, in this situation, I cannot select another database, I will have to use another solution.

  • Use the Entity Framework (EF) is impossible? - cpp_user
  • @cpp_user, hmm, you can. I will check tomorrow. - D4C
  • @cpp_user, looks pretty troublesome. Do not share an example of use for a similar purpose? - D4C
  • one
    In your case, it will be faster to choose in EF - Database First - you will generate C # classes - and then see examples of CRUD operations in EF. Read more here: metanit.com/sharp/entityframework/index.php - cpp_user
  • one
    I do not even know what is the point of using MS Access in our time when there is MS SQL Server Express (with support LocalDB). But your business of course. - cpp_user

1 answer 1

But then it dawned on me:

  connection.Open(); OleDbCommand v_insertCommand = new OleDbCommand("INSERT INTO Посещения " + "([Номер больного], [Номер врача], [Номер болезни], Дата) " + "VALUES (?, ?, ?, ?)", connection); v_insertCommand.Parameters.Add("Номер больного", OleDbType.Integer, 10, "Номер больного"); v_insertCommand.Parameters.Add("Номер врача", OleDbType.Integer, 10, "Номер врача"); v_insertCommand.Parameters.Add("Номер болезни", OleDbType.Integer, 10, "Номер болезни"); v_insertCommand.Parameters.Add("Дата", OleDbType.DBDate, 10, "Дата"); adapter.InsertCommand = v_insertCommand; connection.Close(); adapter.Update(dataSet);

All other requests by analogy. For keys, you can specify the type OleDbType.Integer .