"Rune B" wrote:

> I use in my datalayer the following methods to fill and update data, and it
> works like a charm.
>
> But recently I had to add a dimension to the SqlServer table, - a new field
> that allowed more than one instance of the same customer (same customerID)
> in the table. And of cause that meant I had to remove the primarykey from
> the "CustomerID" column at SqlTable.
>
> So when I filled the table, the dimension "AppID" was always applied,
> "SELECT CustomerID, CompanyName FROM Customers WHERE (AppID = 2)";
> so in fact - in every Query the CustomerID was still unique, allowing me to
> use the below code... to fill the table anyway.
>
> When I try to update using the below UpdateCustomersData() with "WHERE
> (AppID = 2)" added to the CommandText I get the error:
>
> "Dynamic SQL generation for the UpdateCommand is not supported against a
> SelectCommand that does not return any key column information."
>
>
> - The Question:
> How do I manually specify to the key column information the DataAdapter or
> CommandBuilder needs?
>
>
>
>
> //---------------- code -----------------
>
>  private DbProviderFactory _providerFactory;
>  private DbConnection _connection;
>
>  public void FillCustomersData(DataTable customersTable)
>  {
>   DbCommand command = _providerFactory.CreateCommand();
>   command.CommandText = "SELECT CustomerID, CompanyName FROM Customers";
>   command.Connection = this._connection;
>
>   DbDataAdapter adapter = _providerFactory.CreateDataAdapter();
>   adapter.SelectCommand = command;
>   adapter.Fill(customersTable);
>   customersTable.PrimaryKey = new DataColumn[1] {
> customersTable.Columns["CustomerID"] }; ;
>  }
>
>  public void UpdateCustomersData(DataTable customersTable)
>  {
>
>   DbCommand command = _providerFactory.CreateCommand();
>   command.CommandText = "SELECT CustomerID, CompanyName FROM Customers";
>   command.Connection = this._connection;
>
>   DbDataAdapter adapter = _providerFactory.CreateDataAdapter();
>   adapter.SelectCommand = command;
>
>   DbCommandBuilder commandBuilder = _providerFactory.CreateCommandBuilder();
>   commandBuilder.DataAdapter = adapter;
>   commandBuilder.ConflictOption = ConflictOption.OverwriteChanges;
>   commandBuilder.SetAllValues = false;
>
>   adapter.Update(customersTable);
>
>  }
>
> //-----------------------------------
>
>
>