Character String Query Doesnt Fill Dataset

I'm working in a ASP.NET 2.0 application with a SQL Server 2000 database on the back end. I have a strongly typed dataset in the application that calls a stored procedure for the select. I'm having trouble filling the dataset at runtime though.

I am trying to use a character string query because I setup different columns to be pulled from a table each time and in a different order so my T-SQL looks like this:

set @.FullQuery = 'Select ' + @.FieldsinOrder + ' from tblExample'
exec (@.FullQuery)

This works fine in query analyzer. The results return and display correctly. However, when I run the application, the dataset does not get filled. It is like the results do not output to the application.

If I change the query to be a normal select it works. For example:

select * from tblEmample

That works fine. What is it about a select query setup as a character string and then executed that ASP.NET doesn't like?

try to build your command in ASP and pass it to SQL command command object

like:

Dim

yourCommandAs SqlClient.SqlCommand =New SqlClient.SqlCommand(" 'Select '" + FieldsinOrder + '" from tblExample"', yourconnection)
yourCommand.CommandType = CommandType.Text

Maybe it will work.

When your use Exec to execute query inside query is possible that result from this exec is not visible to ASP.NET code as valid result.

Thanks