I was able to repro your problem if I created a view in the Northwind
"create view foo as\nselect * from Customers"
With further investigation I found that creating a view as
"create view foo as\nselect * from NORTHWIND.DBO.Customers"
solved the problem and the Insert command from CommandBuilder was prefixed
This posting is provided "AS IS" with no warranties, and confers no rights.
> In your example, the problem *should* occur if "foo" was defined like this
> "create view foo as\nselect * from Customers" (i.e. create the view
> NorthWind), and SqlCommandBuilder's DataAdapter uses a select text
> such as : "select * from NorthWind.dbo.Customers".
> > Hi Stephen,
> > I was not able to view your repro as it was blocked from Outlook. I did
> > the following scenario and it worked just fine
> > //Open Connection -- not connecting to NorthWind DataBase
> > sqlconnection1.Open();
> > SqlCommand sqlcommand1 = sqlconnection1.CreateCommand();
> > //Sql - Create View foo as select * from NorthWind.dbo.Customers
> > sqlcommand1.CommandText = "create view foo as\nselect * from
> > NorthWind.dbo.Customers";
> > Int32 int321 = sqlcommand1.ExecuteNonQuery(); // -1
> > //Fill DataSet and initialize command builder
> > DataSet dataset1 = new DataSet();
> > SqlDataAdapter sqldataadapter1 = new SqlDataAdapter();
> > sqldataadapter1.SelectCommand = sqlcommand1;
> > SqlCommandBuilder sqlcommandbuilder1 = new
> > SqlCommandBuilder(sqldataadapter1);
> > Int32 int324 = sqldataadapter1.Fill(dataset1, "Table"); // 91
> > SqlCommand sqlcommand2 = sqlcommandbuilder1.GetInsertCommand();
> > String string1 = sqlcommand2.CommandText;
> > The above returned --->
> > "INSERT INTO [NorthWind].[dbo].[Customers]([CustomerID], [CompanyName],
> > [ContactName], [ContactTitle], [Address], [City], [Region],
> > HTH,
> > Sushil..
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
> > > SqlCommandBuilder seems to generate inappropriate sql
> > > statements for views which do not belong to the SqlConnection's
> > > database.
> > > The generated sql does not seem to correctly prefix the names of the
> > > underlying
> > > tables of the view with the appropriate database name.
> > > See attached demo project for more.
> > > Any workarounds ? (Apart from "don't use SqlCommandBuilder :)
> > > Thanks,
> > > Stephen