IfxCommandBuilder problem
Posted in 2008
Hi ! Im trying to use IfxCommandBuilder of the DotNet provider as the example in IBM Informix .NET Provider Reference Guide and i get this exception: System.InvalidOperationException was caught Message="La generación SQL dinámica para UpdateCommand no es compatible con SelectCommand, que no devuelve ninguna información sobre columnas clave." Source="System.Data" However the table has a Primary Key and is in the select... what other problem can be ? this code is a test for my Enterprise Library Data Access Application Block (DAAB) extension for Informix (IfxDatabase) public int UpdateProductsWithCommandBuilder() { // Create the Database object, using the default database service. The // default database service is determined through configuration. Database db = DatabaseFactory.CreateDatabase(); DataSet provinciasDataSet = new DataSet(); DbDataAdapter da = db.GetDataAdapter(); string sqlCommand = "select * " + "from base:provincias"; DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand); dbCommand.Connection = db.CreateConnection(); da.SelectCommand = dbCommand; DbCommandBuilder cb = db.DbProviderFactory.CreateCommandBuilder(); cb.DataAdapter = da; string provinciasTable = "provincias"; // Retrieve the initial data da.Fill(provinciasDataSet, provinciasTable); // Get the table that will be modified DataTable table = provinciasDataSet.Tables[provinciasTable]; // Add a new product to existing DataSet DataRow addedRow = table.Rows.Add(new object[] { "*", " La Atlantida", 1.1, 0.5 }); //// Modify an existing product table.Rows[0]["pvc_des"] = table.Rows[0] ["pvc_des"].ToString().Trim() + "."; //// Delete //table.Select("pvc_cod = '*'")[0].Delete(); //// Submit the DataSet, capturing the number of rows that were affected int rowsAffected = da.Update(provinciasDataSet, provinciasTable); //return rowsAffected; return rowsAffected; } The da.Update(provinciasDataSet, provinciasTable); causes: System.InvalidOperationException was caught Message="La generación SQL dinámica para UpdateCommand no es compatible con SelectCommand, que no devuelve ninguna información sobre columnas clave." Source="System.Data" StackTrace: en System.Data.Common.DbDataAdapter.UpdatingRowStatusErrors(RowUpdatingEventArgs rowUpdatedEvent, DataRow dataRow) en System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping) en System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping) en System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable) en DataAccessQuickStart.SalesData.UpdateProductsWithCommandBuilder() en C: \\Proyectos\\DotNet\\SandBox\\ConMega.DataCore\\DataAccessQuickStart \\SalesData.cs:línea 224 en DataAccessQuickStart.QuickStartForm.updateUsingDataSetButton_Click(Object sender, EventArgs e) en C:\\Proyectos\\DotNet\\SandBox\\ConMega.DataCore \\DataAccessQuickStart\\QuickStartForm.cs:línea 694 InnerException: Also trying to do:cb.GetUpdateCommand() raises: System.InvalidOperationException was unhandled Message="La generación SQL dinámica para UpdateCommand no es compatible con SelectCommand, que no devuelve ninguna información sobre columnas clave." Source="System.Data" StackTrace: en System.Data.Common.DbCommandBuilder.BuildWhereClause(DataTableMapping mappings, DataRow dataRow, StringBuilder builder, DbCommand command, Int32 parameterCount, Boolean isUpdate) en System.Data.Common.DbCommandBuilder.BuildUpdateCommand(DataTableMapping mappings, DataRow dataRow) en System.Data.Common.DbCommandBuilder.GetUpdateCommand() InnerException: