SqlCommandBuilder Bug with SQL Server 2005?

[Marina]

Hi,

Running into an odd issue here. Only happens on a SQL Server 2005 database.

Consider this code:

Dim command As New SqlDataAdapter("Select Col1,Col2 FROM MyTable
WHERE Col1='abc'", conn)
Dim dt As New DataTable
Dim builder As New SqlCommandBuilder(command)

command.Fill(dt)
dt.Rows(0)("Col2") = "ADMIN"
command.Update(dt)

In this case Col1 is the primary key for MyTable.

If the database in question is on a SQL Server 200 machine, everything works
great.

If the database in question is on a SQL Server 2005 machine, there is a
'Dynamic SQL generation is not supported against a SelectCommand that does
not return any base table information.'
Now, not only is Col1 defined as the primary key, it is also being selected
here!

Here is the odd part, if I change the code to the following, everything
works great even on 2005:

Dim command As New SqlDataAdapter("Select Col1,Col2 FROM MyTable
WHERE Col1='abc'", conn)
Dim dt As New DataTable
Dim builder As New SqlCommandBuilder(command)

command.Fill(dt)
dt.Rows(0)("Col2") = "ADMIN"
command.SelectCommand.CommandText="Select Col1,Col2 FROM MyTable"
command.Update(dt)

Notice, that all I have done, is changed the SELECT statement right before
the update takes place, to not include the WHERE clause.

Now, everything is updated correctly.

I would like to reiterate, how everything works perfectly the way it
originally was as long as the database is on SQL Server 2000.

What gives?

Is this a known issue?

 

[William \(Bill\) Vaughn]

Ah Ha! I'm not crazy Pablo!
I just tripped on this too about 24 hours ago. I spent several hours working
on this with the dev team. They're still scratching their heads. For some
reason the DataReader.GetSchema method is not returning the correct metadata
from a Yukon server (at least not against the June CTP) when you have a
WHERE clause in the SelectCommand.

I'll post the fix on my blog when they tell us what's wrong.



--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Marina]

Ok, good to hear I'm not insane.

Ah Ha! I'm not crazy Pablo!
I just tripped on this too about 24 hours ago. I spent several hours
working on this with the dev team. They're still scratching their heads.
For some reason the DataReader.GetSchema method is not returning the
correct metadata from a Yukon server (at least not against the June CTP)
when you have a WHERE clause in the SelectCommand.

I'll post the fix on my blog when they tell us what's wrong.



--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
__________________________________