SqlCommandBuild's generated UpdateCommand with a view

[Edwin]

Hi,

I have a problem with the sql update command generated by
SqlCommandBuilder
when the select command's FROM clause specifies a view. It generates
the
update command with the table name that the view wrappers not the view
name.

This problem is easy to solve if the database is SQL Server 2000 by
adding WITH VIEW_METADATA when creating the View.

But what is the solution when the DB is 7.0?

Edwin

SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = conn.CreateCommand();
da.SelectCommand.CommandText = "select * from View1";

SqlCommandBuilder cb = new SqlCommandBuilder(da);
cb.QuotePrefix = "[";
cb.QuoteSuffix = "]";

DataSet ds = new DataSet();
da.FillSchema(ds, SchemaType.Source, "Data");
da.Fill(ds, "Data");
DataTable tbl = ds.Tables["Data"];
Console.WriteLine("Generated update command: " +
cb.GetUpdateCommand().CommandText);


The output:
Generated update command: UPDATE [dbo].[Table1] SET [Col1] = @p1 ,
[Col2] =
@p2 WHERE .....

If I add WITH VIEW_METADATA (in SQL 2000) when I am creating the view,
the generated command is UPDATE [dbo].[View1] SET [Col1] = @p1 ,
[Col2] =
@p2 WHERE....

How can I achieve the same with SQL 7.0 (VIEW_METADATA is not
supported in 7.0)

TIA
Edwin

 

[David Browne]

Hi,

I have a problem with the sql update command generated by
SqlCommandBuilder
when the select command's FROM clause specifies a view. It generates
the
update command with the table name that the view wrappers not the view
name.

This problem is easy to solve if the database is SQL Server 2000 by
adding WITH VIEW_METADATA when creating the View.

But what is the solution when the DB is 7.0?

Don't use the SqlCommandBuilder. Build your own command.

David