Problems with DetailsView and SQLDataSource - changing data source

[Corey B]

I have a page with a DetailsView control and a SQLDataSource control.
The SQLDataSource control is connected to an Access database.
Everything works fine. Now I want to change the back end database from
Access to SQL Server. So I go in to the SQLDataSource and modify it to
connect to the SQL Server database. No problem. However, the
DetailsView control does not seem to pick up the changes. When I click
on the little shortcut menu (not sure what its proper name is) for the
DetailsView control and select Edit Fields, I get a dialog box where I
can configure the fields for the DetailsView. If I click on one of my
fields in the Selected Fields list and then look at the DataField
property in the BoundField properties list, there is nothing in the
drop down list. It's like the DetailsView control does not know what
all of the available bound fields are. So I cancel out of the dialog
box and click on Refresh Schema. However, when I do this I get an
error message that says the following: "Unable to retrieve schema.
Ensure that the ConnectionString and SelectCommand properties are
valid. An OLE DB Provider was not specified in the ConnectionString.
An example would be, 'Provider=SQLOLEDB;'."

Any ideas?

Thanks,
Corey

 

[Corey B]

I think I've figured out a solution but it seems really cruddy. If I
just create a new SQLDataSource on the page and then point it to the
SQL Server database it works fine. Then I have to modify the
DetailsView to use the new SQLDataSource instead of the old one. Then
I delete the old data source control.

But I'm starting to think that using these declarative data sources is
a gigantic mistake. I have an admin section for my site that has about
20 different pages. Each page has either a GridView or a DetailsView
control on it. And each page has at least one SQLDataSource control.
Some have more than one SQLDataSource control because of data drive
drop down lists. To switch from Access to SQL Server I have to modify
every page by adding a new SQLDataSource control and deleting the old
one. And I also have to modify the SQL because the Access parameters
used question marks and the SQL parameters use the @ symbol. What a
pain in the butt!

Is there a better way that I am missing? I read some Microsoft
articles about the ease and simplicity of using these new GridView and
DetailsView controls to create a quick and dirty admin section. And
the declarative data sources seemed easy enough. But it doesn't seem
very flexible.

Thanks,
Corey