TableAdapters Created in Dataset (XSD) Issues

Hi,
I've been trying to work with the XSDs and ran into a few road blocks.
1. Stored procedures that uses temp tables, i.e. #TempTable. The
wizard complains that #TempTable is an invalid object. What gives?
2. How do you programatically change the connection string at runtime
when using these TableAdapters via databinding in the IDE GUI?
Thanks,
Nick HustakNick,
Is the stored procedure creating the temp table itself, as opposed to
using a temp table that is created on the same connection by another
procedure?
Also, the connection string handling within 2005 for datasets (XSD) is
pretty weak in my opinion. If you're using the dataset within your
application, then it can use the application or web config file to
store the string. But if you're using a common dataset in a class
library then it uses the connection string from the sql server you have
registered in your IDE and stores it *programmatically* within the
settings designer code. It's because it is a class library and so will
use the configuration from the parent application.
The only way that I know of to override this is to change the
InitConnection method within the dataset designer code to use the
application config connection string from the parent application. This
works, but the obvious problem is that if you then need to change
anything with the adapter from the designer, it will overwrite this
code.
So I guess what you (and I) have to do is wait until right before the
application is to be deployed, and change the InitConnection methods to
use the configuration file settings. This, in my opinion, is not an
acceptable solution, but I'm not sure how else they could implement the
functionality.
Thanks,
Gary
On 27 Jun 2006 06:42:41 -0700, "Gary" <ggalehouse@.neo.rr.com> wrote:

>Nick,
>Is the stored procedure creating the temp table itself, as opposed to
>using a temp table that is created on the same connection by another
>procedure?
>Also, the connection string handling within 2005 for datasets (XSD) is
>pretty weak in my opinion. If you're using the dataset within your
>application, then it can use the application or web config file to
>store the string. But if you're using a common dataset in a class
>library then it uses the connection string from the sql server you have
>registered in your IDE and stores it *programmatically* within the
>settings designer code. It's because it is a class library and so will
>use the configuration from the parent application.
>The only way that I know of to override this is to change the
>InitConnection method within the dataset designer code to use the
>application config connection string from the parent application. This
>works, but the obvious problem is that if you then need to change
>anything with the adapter from the designer, it will overwrite this
>code.
>So I guess what you (and I) have to do is wait until right before the
>application is to be deployed, and change the InitConnection methods to
>use the configuration file settings. This, in my opinion, is not an
>acceptable solution, but I'm not sure how else they could implement the
>functionality.
>Thanks,
>Gary
Gary,
First, thanks for a well 'typed (spoken)' reply. My stored procs use
temp tables for intermediate calcs and things ala CREATE TABLE
#Temp...
I've since seen from another thread that MS KNOWS about this and did
nothing about it. It would appear you can still use the proc in a
dataset, but forget getting a schema in the IDE GUI - it won't read
it. Kind of silly IMHO.
And unfortunately you have confirmed my fear - they let this out the
door without serious consideration for how it works. Why in the world
they would not provide an easy way to override the connection strings
to datasets is beyond me. Wow they dropped the ball on the designer
- I'm a bit suprised seeing as they grabbed Anders from Delphi, which
has excellent designer support for databases work.
Oh well - thanks for the detailed explanation. I really am not happy
with having to remember to swap around the init string in the web
config. I toyed around with trying to manipulate it on the fly, but
it would seem it's completely locked out from programmatic
modification. That would have solved a lot of these issues.
I wonder if DLINQ is going to have the same silly issues. So
close..but these are damn near brick walls. I have zero urge to hand
code all my table objects - such a waste of time.
Thanks again,
Nick