Hello,
I have an issue when trying to insert my generated data into my database. (MS SQL SERVER)
I have a database containing only two tables: "Test", which is the parent table, containing some header-data and "LOG", which is the child table containing "ID_Test" and some logging values.
I have an auto-generated DataSet from Visual Studio called "HSR_DataSet" and the resulting TableAdapters "TESTTableAdapter" and "LOGTableAdapter" as well as the TableAdapterManager.
The forein-key constraint in the HSR_DataSet ist set to "Relationship- as well as Foreign Key Constraints" (translated), UpdateRule: Cascade, DeleteRule: Cascade, Acceptance/RejectionRule: Cascade.
In the configuration of the TabeAdapters I have ticked the option "Refresh Datatable".
In my code I create 1-2 new rows for the "Test" table and n rows for the "LOG" table. I do not fill the DataSet via sql at any time.
To write the header-data and the logged values into the database I have tried different versions, which got me different errors:
Version 1:
try
{
HSR_DataSetTableAdapters.TableAdapterManager MGR = new HSR_DataSetTableAdapters.TableAdapterManager();
MGR.TESTTableAdapter = new HSR_DataSetTableAdapters.TESTTableAdapter();
MGR.LOGTableAdapter = new HSR_DataSetTableAdapters.LOGTableAdapter();
MGR.UpdateAll(HS_DATA);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
ERROR: "Concurrency violation: the UpdateCommand affected 0 of the expected 1 records."
Version 2:
try
{
HSR_DataSetTableAdapters.TESTTableAdapter TA_TEST = new HSR_DataSetTableAdapters.TESTTableAdapter();
HSR_DataSetTableAdapters.LOGTableAdapter TA_LOG = new HSR_DataSetTableAdapters.LOGTableAdapter();
TA_TEST.Update(HS_DATA.TEST);
TA_LOG.Update(HS_DATA.LOG);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
ERROR: No Error, but there are no rows added to the "LOG" table in the database.
Version 3:
using (SqlConnection CONN = new SqlConnection(Properties.Settings.Default.HSR_ConnStr))
{
SqlDataAdapter DA_TEST = new SqlDataAdapter("SELECT * FROM [dbo].[TEST];", CONN);
SqlDataAdapter DA_LOG = new SqlDataAdapter("SELECT * FROM [dbo].[LOG]", CONN);
DA_TEST.Fill(HS_DATA.TEST);
DA_LOG.Fill(HS_DATA.LOG);
SqlCommandBuilder B_TEST = new SqlCommandBuilder(DA_TEST);
SqlCommandBuilder B_LOG = new SqlCommandBuilder(DA_LOG);
B_TEST.GetInsertCommand();
B_LOG.GetInsertCommand();
CONN.Open();
DA_TEST.Update(HS_DATA.TEST);
DA_LOG.Update(HS_DATA.LOG);
CONN.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
ERROR: No Error, but there are no rows added to the "LOG" table in the database.
I do not know what else I could try, maybe you can help me out :-)
What I have tried:
Three different versions of inserting the data, see above.