Generate DataSet does not work for Select Stored Procedure

[Guest]

I am developing an ASP.NET application and heavily rely on Strongly Typed
DataSets.

I let Visual Studio generate these DataSets by right clicking on a
SqlDataAdapter and then selecting 'Generate DataSet...'

Today a query got a litle complex and decided to convert it to a Stored
Procedure (a View was not appropiate because the Query is parameterized). All
went well until I wanted to click on 'Genarate DataSet...': it does not show
up for the SqlAdapter!

Later a did a configure of de Adapter and it gives a warning 'Failed to get
schema for this Stored Procedure'. Is this the reason it goes wrong?

Thanks for any help!

Yours sincerely,

Evert Wiesenekker
Rookie ASP.NET Developer at www.alterra.nl


PS.
* Preview of the Data works fine
* I am using SQL Server 2000 and FrameWork 1.1xxxxx

 

[Guest]

I found the cause:

At the start of my Stored Procedure I use IF staments to check some things
in combination with RAISERROR.

I just deleted them and it works...

I think IF's are not allowed...

Evert

 

[Sahil Malik]

Were those IF's evaluating to true in the case you'd access the stored proc
via the IDE while generating the dataset? (You can check the exact query
being sent via a trace).

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik

 

[Guest]

Hee, you are right I am looking at the Trace and I see VS is executing the
trace and it is executing the pieces of code in the IF (I am checking if some
fields are empty and of course they are initially because I am using default
values).

How can I circumvent these things? I would have expected that SQL Server
queries system tables so get Meta Data, but it Executes the Stored Procedure

 

[Sahil Malik]

Well in order to be able to generate a strongly typed dataset for you,
Visual Studio needs to get some results back - which it can query upon and
create an XSD for you. It is doing a LOT of work under the scenes for you;
it is amazing it even works.

Anyway, to get around this problem, generate your strongly typed dataset
once by hook or by crook - in your case by disabling the RaiseErrors, so it
gets SOME data back. The data it is returning back should be a superset of
all the tables you might ever receive from the stored proc. Once the dataset
is generated, you can put the RaiseErrors back in. (You can even make this
programmatic).

Alternatively, you can write up the XSD yourself, and generate a strongly
typed ds yourself by hand.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik
http://blogs.apress.com/authors.php?author=Sahil Malik

 

[Guest]

Thanks you for your quick and thorough answer!

Well in order to be able to generate a strongly typed dataset for you,
Visual Studio needs to get some results back - which it can query upon and
create an XSD for you. It is doing a LOT of work under the scenes for you;
it is amazing it even works.

Anyway, to get around this problem, generate your strongly typed dataset
once by hook or by crook - in your case by disabling the RaiseErrors, so it
gets SOME data back. The data it is returning back should be a superset of
all the tables you might ever receive from the stored proc. Once the dataset
is generated, you can put the RaiseErrors back in. (You can even make this
programmatic).

Alternatively, you can write up the XSD yourself, and generate a strongly
typed ds yourself by hand.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik
http://blogs.apress.com/authors.php?author=Sahil Malik