SqlCommandBuild's generated UpdateCommand with a view

  • Thread starter Thread starter Edwin
  • Start date Start date
E

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
 
Edwin said:
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?
Click to expand...

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

David
 
Back
Top