I have 2 dataGridViews bound to a master/detail relationship. When I try to add a second row to the master dataGridView I get the following error.
system.data.constraintexception column "Project Customer UBF Id" is constrained to be unique. Value '' is already present Data relation.
I can add multi rows to the child DataGridView, and if I remove the DataRelation between the tables I can add multi rows to the master. Also if I manually enter in the primary key for the master with the next autoincrement value I can add multi rows, as well as add details againist those rows.
The tables are set up with autoincrement primary keys in SQL server.
Any help in overcoming this error would be appreciated
private void getData()
{
try
{
conn = new SqlConnection(connstr);
conn.Open();
// Create a DataSet.
data = new DataSet();
data.Locale = System.Globalization.CultureInfo.InvariantCulture;
string sqlStr = "SELECT [Project Customer UBF].* FROM [Project Customer UBF]; ";
// Add data from the Customers table to the DataSet.
masterDataAdapter = new
SqlDataAdapter(sqlStr, conn);
masterDataAdapter.Fill(data, "Customers");
// Add data from the Orders table to the DataSet.
detailsDataAdapter = new
SqlDataAdapter("SELECT [Project Customer Discount].* FROM [Project Customer Discount]", conn);
detailsDataAdapter.Fill(data, "Discounts");
// Establish a relationship between the two tables.
DataRelation relation = new DataRelation("CustDist",
data.Tables["Customers"].Columns["Project Customer UBF Id"],
data.Tables["Discounts"].Columns["Project Customer UBF Id"]);
data.Relations.Add(relation);
// Bind the master data connector to the Customers table.
masterBindingSource.DataSource = data;
masterBindingSource.DataMember = "Customers";
masterBindingSource.Filter = "[Project Id] =" + _projectID;
// Bind the details data connector to the master data connector,
// using the DataRelation name to filter the information in the
// details table based on the current row in the master table.
detailsBindingSource.DataSource = masterBindingSource;
detailsBindingSource.DataMember = "CustDist";
conn.Close();
}
catch (SqlException)
{
MessageBox.Show("To run this example, replace the value of the " +
"connectionString variable with a connection string that is " +
"valid for your system.");
}
}
private void ProjectEdit_Load(object sender, EventArgs e)
{
dataGridView1.DataSource = masterBindingSource;
dataGridView2.DataSource = detailsBindingSource;
getData();
}
Solved by generating own update, delete and add commannds for the master/parent datatables and the details/child datatables:
private void GenerateCommands() {
masterDataAdapter.InsertCommand = new SqlCommand();
masterDataAdapter.InsertCommand.CommandText = "INSERT INTO [Project Customer UBF]([Project Id], [Customer Id]," +
"[Host Support], Warehoused,[List Price per Case]) "
+ " VALUES (@ProjID, @CustID, 1, @Ware, 4); "
+ " SELECT [Project Customer UBF Id],[Project Id], [Customer Id]," +
"[Host Support], Warehoused,[List Price per Case]"
+ " FROM [Project Customer UBF] WHERE ([Project Customer UBF Id]= SCOPE_IDENTITY())";
masterDataAdapter.InsertCommand.Connection = conn;
masterDataAdapter.InsertCommand.Parameters.Add("@ProjID", SqlDbType.Int,4,"Project Id");
masterDataAdapter.InsertCommand.Parameters.Add("@CustID", SqlDbType.Int,4,"Customer Id");
masterDataAdapter.InsertCommand.Parameters.Add("@Host", SqlDbType.Bit,1,"Host Support");
masterDataAdapter.InsertCommand.Parameters.Add("@Ware", SqlDbType.Bit,1,"Warehoused");
masterDataAdapter.InsertCommand.Parameters.Add("@List", SqlDbType.Float,8,"List Price per Case");
masterDataAdapter.UpdateCommand = new SqlCommand();
masterDataAdapter.UpdateCommand.CommandText = "UPDATE [Project Customer UBF] SET [Project Id] = @ProjID, "
+ " [Customer Id] = @CustID, [Host Support] = @Host, Warehoused = @Ware, [List Price per Case] = @List "
+ "WHERE ([Project Customer UBF Id] = @PCID); ";
masterDataAdapter.UpdateCommand.Connection = conn;
masterDataAdapter.UpdateCommand.Parameters.Add("@ProjID", SqlDbType.Int,4,"Project Id");
masterDataAdapter.UpdateCommand.Parameters.Add("@CustID", SqlDbType.Int,4,"Customer Id");
masterDataAdapter.UpdateCommand.Parameters.Add("@Host", SqlDbType.Bit,1,"Host Support");
masterDataAdapter.UpdateCommand.Parameters.Add("@Ware", SqlDbType.Bit,1,"Warehoused");
masterDataAdapter.UpdateCommand.Parameters.Add("@List", SqlDbType.Float,8,"List Price per Case");
masterDataAdapter.UpdateCommand.Parameters.Add("@PCID", SqlDbType.Int,4,"Project Customer UBF Id");
masterDataAdapter.DeleteCommand = new SqlCommand();
masterDataAdapter.DeleteCommand.CommandText = "DELETE FROM [Project Customer UBF] "
+ " WHERE ([Project Customer UBF Id] = @PCID);";
masterDataAdapter.DeleteCommand.Connection = conn;
masterDataAdapter.DeleteCommand.Parameters.Add("@PCID", SqlDbType.Int,4,"Project Customer UBF Id");
detailsDataAdapter.InsertCommand = new SqlCommand();
detailsDataAdapter.InsertCommand.CommandText = "INSERT INTO [Project Customer Discount]([Project Customer UBF Id], "
+ " [Discount Type], [Discount Amt], [Discount UOM]) "
+ " VALUES (@PCID, @Type, @Amt, @UOM); "
+ " SELECT [Discount Id],[Project Customer UBF Id], "
+ " [Discount Type], [Discount Amt], [Discount UOM] "
+ " FROM [Project Customer Discount] WHERE ([Discount Id] = SCOPE_IDENTITY())";
detailsDataAdapter.InsertCommand.Connection = conn;
detailsDataAdapter.InsertCommand.Parameters.Add("@PCID", System.Data.SqlDbType.Int,4, "Project Customer UBF Id");
detailsDataAdapter.InsertCommand.Parameters.Add("@Type", SqlDbType.Int,4,"Discount Type");
detailsDataAdapter.InsertCommand.Parameters.Add("@Amt", SqlDbType.Float,8,"Discount Amt");
detailsDataAdapter.InsertCommand.Parameters.Add("@UOM", SqlDbType.NVarChar,2,"Discount UOM");
detailsDataAdapter.UpdateCommand = new SqlCommand();
detailsDataAdapter.UpdateCommand.CommandText = "UPDATE [Project Customer Discount] SET [Project Customer UBF Id] = @PCID, "
+ " [Discount Type] = @Type, [Discount Amt] = @Amt, [Discount UOM] = @UOM "
+ "WHERE ([Discount Id] = @DID); ";
detailsDataAdapter.UpdateCommand.Connection = conn;
detailsDataAdapter.UpdateCommand.Parameters.Add("@PCID", System.Data.SqlDbType.Int, 4, "Project Customer UBF Id");
detailsDataAdapter.UpdateCommand.Parameters.Add("@Type", SqlDbType.Int, 4, "Discount Type");
detailsDataAdapter.UpdateCommand.Parameters.Add("@Amt", SqlDbType.Float, 8, "Discount Amt");
detailsDataAdapter.UpdateCommand.Parameters.Add("@UOM", SqlDbType.NVarChar, 2, "Discount UOM");
detailsDataAdapter.InsertCommand.Parameters.Add("@DID", System.Data.SqlDbType.Int, 4, "Discount Id");
detailsDataAdapter.DeleteCommand = new SqlCommand();
detailsDataAdapter.DeleteCommand.CommandText = "DELETE FROM [Project Customer Discount] "
+ " WHERE ([Discount Id] = @DID);";
detailsDataAdapter.DeleteCommand.Connection = conn;
detailsDataAdapter.DeleteCommand.Parameters.Add("@DID", System.Data.SqlDbType.Int, 4, "Discount Id");
}
and creating an autoIncrement for the parent/master datatables primary key. The Autoincrement starts at -1 and increment by -1 to remain unique.
private void CreateRelations() {
DataRelation relation = new DataRelation("CustDist",
data.Tables["Customers"].Columns["Project Customer UBF Id"],
data.Tables["Discounts"].Columns["Project Customer UBF Id"]);
data.Relations.Add(relation);
data.Tables["Customers"].Columns["Project Customer UBF Id"].AutoIncrement = true;
data.Tables["Customers"].Columns["Project Customer UBF Id"].AutoIncrementSeed = -1;
data.Tables["Customers"].Columns["Project Customer UBF Id"].AutoIncrementStep = -1;
}
Code for creating the master/parent detail/child dataset:
private void GetData()
{
try
{
conn = new SqlConnection(connstr);
conn.Open();
// Create a DataSet.
data = new DataSet();
data.Locale = System.Globalization.CultureInfo.InvariantCulture;
string sqlStr = "SELECT [Project Customer UBF].* FROM [Project Customer UBF]; "; //WHERE [Project Customer UBF].[Project Id] = " +_projectID;
// Add data from the Customers table to the DataSet.
masterDataAdapter = new
SqlDataAdapter(sqlStr, conn);
masterDataAdapter.Fill(data, "Customers");
// Add data from the Orders table to the DataSet.
detailsDataAdapter = new
SqlDataAdapter("SELECT [Project Customer Discount].* FROM [Project Customer Discount]", conn);
detailsDataAdapter.Fill(data, "Discounts");
detailsDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
GenerateCommands();
CreateRelations();
// Bind the master data connector to the Customers table.
masterBindingSource.DataSource = data;
masterBindingSource.DataMember = "Customers";
masterBindingSource.Filter = "[Project Id] =" + _projectID;
// Bind the details data connector to the master data connector,
// using the DataRelation name to filter the information in the
// details table based on the current row in the master table.
detailsBindingSource.DataSource = masterBindingSource;
detailsBindingSource.DataMember = "CustDist";
conn.Close();
}
catch (SqlException)
{
MessageBox.Show("To run this example, replace the value of the " +
"connectionString variable with a connection string that is " +
"valid for your system.");
}
}
Saving the changes back to the database:
if (data.Tables["Discounts"].GetChanges(System.Data.DataRowState.Deleted) != null)
{
detailsBindingSource.EndEdit();
System.Data.DataTable DeletedChildRecords =
data.Tables["Discounts"].GetChanges(System.Data.DataRowState.Deleted);
try
{
if (DeletedChildRecords != null)
{
SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(detailsDataAdapter);
detailsDataAdapter.Update(data.Tables["Discounts"].Select(null, null, DataViewRowState.Deleted));
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
if (DeletedChildRecords != null)
{
DeletedChildRecords.Dispose();
}
}
}
if (data.Tables["Customers"].GetChanges(System.Data.DataRowState.Added) != null || data.Tables["Customers"].GetChanges(System.Data.DataRowState.Modified) != null ||
data.Tables["Customers"].GetChanges(System.Data.DataRowState.Deleted) != null)
{
masterBindingSource.EndEdit();
try
{
SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(masterDataAdapter);
masterDataAdapter.Update(data.Tables["Customers"].Select(null, null, DataViewRowState.Deleted));
masterDataAdapter.Update(data.Tables["Customers"].Select(null, null, DataViewRowState.ModifiedCurrent));
masterDataAdapter.Update(data.Tables["Customers"].Select(null, null, DataViewRowState.Added));
}
catch (System.Exception err)
{
MessageBox.Show(err.ToString());
}
}
if (data.Tables["Discounts"].GetChanges(System.Data.DataRowState.Added) != null || data.Tables["Discounts"].GetChanges(System.Data.DataRowState.Modified) != null)
{
detailsBindingSource.EndEdit();
System.Data.DataTable NewChildRecords =
data.Tables["Discounts"].GetChanges(System.Data.DataRowState.Added);
System.Data.DataTable ModifiedChildRecords =
data.Tables["Discounts"].GetChanges(System.Data.DataRowState.Modified);
try
{
if (ModifiedChildRecords != null)
{
SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(detailsDataAdapter);
detailsDataAdapter.Update(data.Tables["Discounts"].Select(null, null, DataViewRowState.ModifiedCurrent));
}
if (NewChildRecords != null)
{
SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(detailsDataAdapter);
detailsDataAdapter.Update(data.Tables["Discounts"].Select(null, null, DataViewRowState.Added));
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
if (NewChildRecords != null)
{
NewChildRecords.Dispose();
}
if (ModifiedChildRecords != null)
{
ModifiedChildRecords.Dispose();
}
}
}
Binding datasource to dataGridViews:
dataGridView1.DataSource = masterBindingSource;
dataGridView2.DataSource = detailsBindingSource;
GetData();