MARS and VS2003 vs. VS2005

[Guest]

Hi,
I have a query that returns 4 result sets. I pass a SubscriptionId and
it returns the subscription record along with the comments(multiple records),
A/R history (multiple records) and Shipping records (1 or more records).
Under VS2003, when I create an SQLTableAdapter using this query and then
generate a dataset, I get 4 tables. Under VS2005 (with
MultipleActiveResultSets=True), I get just one table in the dataset. How do
I get around this?
Also, as I tried to find my own way around this I came across the
following problem with the documentation. I was following the instructions
for:
"Walkthrough: Creating a TableAdapter with Multiple Queries"
When I get to the instructions...
_______________________________________--
The wizard created the dataset with a Customers data table and
CustomersTableAdapter. This section of the walkthrough adds a second query to
the CustomersTableAdapter.

To add a query to the CustomersTableAdapter
Drag a Query from the DataSet tab of the Toolbox onto the Customers table.

The TableAdapter Query Configuration Wizard opens.

Select Use SQL statements, and then click Next.

Select SELECT which returns rows, and then click Next.
________________________________________________

The problem is that the particular Radio Button it is telling me to select
is disabled and can not be selected.

Any thoughts?

 

[Lloyd Sheen]

Hi,
I have a query that returns 4 result sets. I pass a SubscriptionId and
it returns the subscription record along with the comments(multiple
records),
A/R history (multiple records) and Shipping records (1 or more records).
Under VS2003, when I create an SQLTableAdapter using this query and then
generate a dataset, I get 4 tables. Under VS2005 (with
MultipleActiveResultSets=True), I get just one table in the dataset. How
do
I get around this?
Also, as I tried to find my own way around this I came across the
following problem with the documentation. I was following the
instructions
for:
"Walkthrough: Creating a TableAdapter with Multiple Queries"
When I get to the instructions...
_______________________________________--
The wizard created the dataset with a Customers data table and
CustomersTableAdapter. This section of the walkthrough adds a second query
to
the CustomersTableAdapter.

To add a query to the CustomersTableAdapter
Drag a Query from the DataSet tab of the Toolbox onto the Customers table.

The TableAdapter Query Configuration Wizard opens.

Select Use SQL statements, and then click Next.

Select SELECT which returns rows, and then click Next.
________________________________________________

The problem is that the particular Radio Button it is telling me to select
is disabled and can not be selected.

Any thoughts?

MARS is multiple open at the same time. In your query while you have 4
results returned they are returned one at a time.

MARS would allow you to have an open connection returning results and then
to allow a new query using the same connection.

I would advise turning off MARS unless you need to use it.

LS

 

[Guest]

Hi Lloyd,
Ok then, how do I go about filling all 4 tables in the dataset with a
single query like I could do with VS2003? Also, why can't I define all 4
tables with the single query? As of right now it looks like I have to set up
4 different table adapters to define the tables which seems like a lot of
wasted overhead. Given that I have defined the tables with the 4 seperate
adapters, is there a way to fill them with the one query?

 

[WenYuan Wang [MSFT]]

Hello Terry,

According to your description, your main issue is that SQLTableAdapter can
only generate one dataTable in DataSet if you enable
MultipleActiveResultSets in VS 2005. Please correct me, if I misunderstood
anything.

As far as I know, TableAdapter class is a new feature in VS 2005. VS 2003
IDE cannot generate TableAdapter for us. I suspect what you meaned by
"SQLTableAdapter" is SQLDataAdatper. (If I misunderstood, please correct
me).

Would you please paste the query that returns four results sets in
newsgroup?

I have tried the following code snippet and enabled MARS. It works fine on
my side. After running the application , I can get two tables in DataSet.

System.Data.SqlClient.SqlConnection sc = new
System.Data.SqlClient.SqlConnection();
sc.ConnectionString = @"Data Source=.;Initial Catalog=testDB;Integrated
Security=True;MultipleActiveResultSets=True";
System.Data.SqlClient.SqlDataAdapter sda = new
System.Data.SqlClient.SqlDataAdapter("select * from Table_1;select * from
Table_2",sc);
System.Data.DataSet ds = new DataSet();
sda.Fill(ds);

Thanks. Please let me know if you have any more concern. It's my pleasure
to assist you.
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Guest]

Hi Wen,
Thank you for your reply. While I try to figure out a better way to ask
the question I need to ask, could you please address the second part of my
post? I have had a few occasions where my machine 'behaves' in a strange
way. So I am wondering if this is one of those occasions, or if the
documentation is wrong.
To make it easier, I have added the second part of my post again here:
I was following the instructions for:

"Walkthrough: Creating a TableAdapter with Multiple Queries"
When I get to the instructions...
_______________________________________--
The wizard created the dataset with a Customers data table and
CustomersTableAdapter. This section of the walkthrough adds a second query to
the CustomersTableAdapter.

To add a query to the CustomersTableAdapter
Drag a Query from the DataSet tab of the Toolbox onto the Customers table.

The TableAdapter Query Configuration Wizard opens.

Select Use SQL statements, and then click Next.

Select SELECT which returns rows, and then click Next.
________________________________________________

The problem is that the particular Radio Button it is telling me to select
is disabled and can not be selected.

 

[WenYuan Wang [MSFT]]

Hello Terry,
Thanks for your reply.

I assume your VS 2005 has applied sp1 and you follow the below document to
add multiply queries in TableAdapter. Please correct me if I misunderstood.
http://msdn2.microsoft.com/en-us/library/ms171907(VS.80).aspx
[Walkthrough: Creating a TableAdapter with Multiple Queries]

The options in TableAdapter Query Configuration Wizard are related to the
underlying database which you connect to.

For example: if we are connect to Access database, "Create new stored
procedure" option and "Use existing stored procedure" option are disabled.
We can only add query by "Use SQL statements".

I have tried the steps mentioned by MSDN document. I did not meet any
issue, added a second query by ("Use SQL statements"|"SELECT which returns
rows") successfully,
I'm using VS 2005 sp1, SQL Server 2005.

Would you please let me know which option ("Use SQL statements" option or
"SELECT which returns rows" option) is disabled on your side?

In addition, could you let me know which database is your TableAdapter
connects to?

Thereby, we can perform further analyze. Thanks.

Have a great day. We are glad to assist you.
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[WenYuan Wang [MSFT]]

Hello Terry,

How is the issue going on your side? Have you resolved it so far?
If it still persists, would you please let me know more detailed
information as below:
1) Which option is disabled on your side? ("Use SQL statements" or "SELECT
which returns rows")
2) What kind of database does your TableAdapter connect to? (SQL 2000, SQL
2005, Access)
Please feel free to let us know if you need further assistance. We are glad
to assist you.

Have a great day,
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.