We recently upgraded our MSDN Version of VS.NET 2002 to VS.NET 2003...
Things are all working well, except we've run across a new bug or issue
since our adopting of the new package.
Before I give the error message, let me preface it with a brief description
of how our 1-tierd data environment is setup. (Note: we do get the same
errors on multi-tiered apps as well and originally it was thought that our
error lied in the way we were having our dataaccess objects inherit
connections from base objects... but anyway, it's not ... ).
In a typical web application of ours, we have an appsetting called
"MainDBConnection" within the Web.Config file. The data w/in the variable
looks something like this:
<add key="MainDBConnection" value="data source=OurServerName;initial
catalog=OurDB;persist security info=False;user
id=mikej;password=mypassword;workstation id=MIKEJ;packet size=4096" />
The connectionString was one that was actually generated by VS.NET and then
copied/pasted into here. Anyway, in the designer, and connection that is
defined points to the value through the Dynamic Properties -->
ConnectionString area of the Properties Window... Everything looks good...
it retrieves the value from the Web.Config and any command object works
nicely w/ it... nicely meaning that I can define queries in the designer and
VS.NET can communicate w/ the DB for parameter generation and all that
jazz...
Here's where the problem comes in... Ok... so imagine I've defined a
connection object (pointing to my config data), a command object (pointing
to the connection) and a dataAdapter (using the command object as its
SelectCommmand Property) ... When I click on the button to define
TableMappings in my property window for the dataAdapter I get the following
error:
Unable to retrieve the schema from the database table. Source column
mapping information will not be available. Would you like to continue?
What!?!? This is where I'm confused... VS.NET can access the DB when I'm
defining my Select Command's CommandText property. Why can't it when I'm
defining my tableMappings? Another thing that confuses me is that
everything worked fine in the previous version of VS.NET... Bug addition? ;)
It's probably some bad convention I'm using in setting up a dynamic
connection, but if anyone has a way for this to work, pleeeeeeeease let me
know. Additionally, whenever I open up an object like this in the designer,
I get another error saying, "Exception has been thrown by the target of an
invocation" and then the designer erases my table mappings again and I have
to repeat the whole annoying process of having to fix it again... Funny
thing is that this is only new w/ version 2003 :( I shouldn't have upgraded
all my projects... Now I'm stuck with it and though I've developed many new
methodologies, I still have to figure out how to get this old stuff working
for maintenance purposes. I have figured out some workarounds for the
issue, but it's such a productivity killer to get it going again as I have
not found a consistent method for kickstarting it...
While I'm on the subject, I'll describe how I get around it. There are 2
ways I have taken care of this:
1. Add this to the dataadapter in the object constructor (This still doesn't
fix it working in the designer, but allows me to compile):
- MyDataAdapter.TableMappings.Add("Table", "MyNewTable")
2. If I really, really want my designer to work right, I can tinker around
w/ Visual Studio enough so that it prompts me for my SQLServer Username and
password (and I guess VS saves that info in memory 'til I close it)... after
that, the Designer's table mappings work again. How do you get that
user/password prompt you ask? I usually have to define a new connection for
the DB and then change it back after it has prompted me. This way works the
best, but is still just a big pain in the butt because everytime I *open* a
page in the designer it clears out my table mappings until I fix the
problem... again and again
Also, one thing that has already been suggested and tried is changing the
'persist security info=False' to True, but that didn't work either :)
Thanks for your time,
Mike Joseph
Hypersite.net