MultiTable Dataset

First off let me say that some of this is very confusing as it would
appear that the MS SQL group and the ADO.NET group need to get together
to mesh their termonology a bit.
I have a program that uses ReportViewer. To this program I load up an
ADO.NET dataset that contains serveral tables. I also load in an RDLC
report file and display the resulting report.
Up until now this has worked great and I have gotten every report I
need worked out. To keep this simple lets go with something we all
know all so well. My ADO.NET dataset contains three tables:
Customers, Orders, Parts. Each one of them is related to the other.
How does one go about displaying a report that would appear as follows:
Customer 1
-Order 1
--Part 1
--Part 2
--Part 3
-Order 2
--Part 4
--Part 5
Customer 2
-Order3
etc...
I have tried numerous things and read a ton of different posts, but all
to no avail. I know I must be doing something wrong when it comes to
setting this up. There do not seem to be many examples with multiple
tables in a single ADO dataset. Unless I am just missing something.
Also, as an aside, why did MS decide to call each table in a Report a
dataset while a dataset in ADO.NET contains many different tables?
Any help would be greatly appreciated.
Thanks,
AdamYou have to made a Select woth Joins of Customer, Order, Part
So for each part you have one record with the data Customer and Order within.
Then you can group this records in the report and so on.
So you will have only one Resultset for this kind of Report.
OK, The naming-conventions in ReportViewer differs a little bit.
Maybe they will sit together for the next release ;-)
--
LG HOLAN
"akeroo" wrote:
> First off let me say that some of this is very confusing as it would
> appear that the MS SQL group and the ADO.NET group need to get together
> to mesh their termonology a bit.
> I have a program that uses ReportViewer. To this program I load up an
> ADO.NET dataset that contains serveral tables. I also load in an RDLC
> report file and display the resulting report.
> Up until now this has worked great and I have gotten every report I
> need worked out. To keep this simple lets go with something we all
> know all so well. My ADO.NET dataset contains three tables:
> Customers, Orders, Parts. Each one of them is related to the other.
> How does one go about displaying a report that would appear as follows:
> Customer 1
> -Order 1
> --Part 1
> --Part 2
> --Part 3
> -Order 2
> --Part 4
> --Part 5
> Customer 2
> -Order3
> etc...
> I have tried numerous things and read a ton of different posts, but all
> to no avail. I know I must be doing something wrong when it comes to
> setting this up. There do not seem to be many examples with multiple
> tables in a single ADO dataset. Unless I am just missing something.
> Also, as an aside, why did MS decide to call each table in a Report a
> dataset while a dataset in ADO.NET contains many different tables?
> Any help would be greatly appreciated.
> Thanks,
> Adam
>|||Although you can do it, this gets quite messy. The clean way (but it is more
difficult when operating in local mode with the viewer control) is to use
subreports.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"holan" <holan@.noemaol.noemail> wrote in message
news:FA17AF8F-0A21-4A92-BDB9-45A194502517@.microsoft.com...
> You have to made a Select woth Joins of Customer, Order, Part
> So for each part you have one record with the data Customer and Order
> within.
> Then you can group this records in the report and so on.
> So you will have only one Resultset for this kind of Report.
> OK, The naming-conventions in ReportViewer differs a little bit.
> Maybe they will sit together for the next release ;-)
> --
> LG HOLAN
>
> "akeroo" wrote:
>> First off let me say that some of this is very confusing as it would
>> appear that the MS SQL group and the ADO.NET group need to get together
>> to mesh their termonology a bit.
>> I have a program that uses ReportViewer. To this program I load up an
>> ADO.NET dataset that contains serveral tables. I also load in an RDLC
>> report file and display the resulting report.
>> Up until now this has worked great and I have gotten every report I
>> need worked out. To keep this simple lets go with something we all
>> know all so well. My ADO.NET dataset contains three tables:
>> Customers, Orders, Parts. Each one of them is related to the other.
>> How does one go about displaying a report that would appear as follows:
>> Customer 1
>> -Order 1
>> --Part 1
>> --Part 2
>> --Part 3
>> -Order 2
>> --Part 4
>> --Part 5
>> Customer 2
>> -Order3
>> etc...
>> I have tried numerous things and read a ton of different posts, but all
>> to no avail. I know I must be doing something wrong when it comes to
>> setting this up. There do not seem to be many examples with multiple
>> tables in a single ADO dataset. Unless I am just missing something.
>> Also, as an aside, why did MS decide to call each table in a Report a
>> dataset while a dataset in ADO.NET contains many different tables?
>> Any help would be greatly appreciated.
>> Thanks,
>> Adam
>>|||Thanks... I ended up going with subreports and it is working fine.
The joins were not quite going to work for me as my exact situation
wasn't an A to B to C relationship. It was an A-B A-C setup, which
means the joins would have ended up being A-B * C number of records and
that would have gotten really ugly.
thanks,
Adam

Multiple ToggleItems?

Hello:
I have a table, with a group that inserts group headers and details, each
header expands or collapses its details, but I'll also like the users to be
able to expand all the data with a single click.
Is it possible to have an element toggled by two different ways?
Any Ideas?
Thanks,
Daniel Bello UrizarriOn Mar 7, 10:32 am, "Daniel Bello" <dburiza...@.yahoo.es> wrote:
> Hello:
> I have a table, with a group that inserts group headers and details, each
> header expands or collapses its details, but I'll also like the users to be
> able to expand all the data with a single click.
> Is it possible to have an element toggled by two different ways?
> Any Ideas?
> Thanks,
> Daniel Bello Urizarri
You should have several options to accomplish this, if I'm
understanding you correctly. You should be able to set the whole table
to be toggled; as well as, setting a row, column or individual cells
to be toggled by one control (i.e., textbox). That said, you cannot
toggle visibility on a single cell more than one way: unless it's a
part of a larger group (mainly a row or column). Hope this is helpful.
Regards,
Enrique Martinez
Sr. SQL Server Developer

Multiple Tables Inside a List

I have 3 tables inside a list, all of which use the same dataset. The
layout is something like this:
List
Table1
Table2
Table3
The List has a group defined on it, lets assume the grouped column is
named Entity. What I want when the report renders is this:
Table1 for Entity 1
Table2 for Entity 1
Table3 for Entity 1
Table1 for Entity 2
Table2 for Entity 2
Table3 for Entity 2
...but instead I get this:
Table1 for Entity1
Table1 for Entity2
Table2 for Entity1
Table2 for Entity2
Table3 for Entity1
Table3 for Entity2
I checked the RDL and confirmed that all 3 tables are part of the
List's ReportItems collection. All of the data is displaying
correctly. The list is just rendering the tables as if there were
three separate lists, instead of just one. I tried putting all three
tables inside a Rectangle, but it had no effect. The problem occurs in
Visual Studio, IE, and PDF layout.
This looks like a bug to me. I think it is reasonable to expect that
if I nest three sequential tables in a list, I should get repeating
groups of Table1 / Table2 / Table3 for each entity in the list. Am I
correct in that assumption? If anyone can suggest a workaround, I
would appreciate it.
TIAWell I confirmed the rendering engine can handle a simple case of
tables inside a list. I created a simple report with 3 tables inside a
list using AdventureWorks, and it worked as expected. Unfortunately my
real report rdl is over 10,000 lines long with multiple layers of
nested tables inside the tables inside the lists... so this could take
a while to debug.|||For posterity, in case anyone runs into this type of issue... There
was a left-over Group in the table, which was causing data to repeat.
I had copied the table into the list, intending to use the list for
grouping instead of a group expression within the table. I never
deleted the group. I discovered what was going on by looking at the
raw RDL. The list appears to be working well now.
On Sep 28, 5:05 pm, Chris Durkin <durki...@.gmail.com> wrote:
> Well I confirmed the rendering engine can handle a simple case of
> tables inside a list. I created a simple report with 3 tables inside a
> list using AdventureWorks, and it worked as expected. Unfortunately my
> real report rdl is over 10,000 lines long with multiple layers of
> nested tables inside the tables inside the lists... so this could take
> a while to debug.

Multiple Step Stored Procedure

Hi,
I am writing a stored procedure in SQL Server 2000 the will require
multiple commits(?) during its operation. The first group of actions
that it must perform is to modify a table by adding some columns and
altering another. The next step reads data out of the table into a
temporary (in memory) table and does some processing on it and writes
it back to the original source table utilizing the recently added
columns. When I try to put this into a stored procedure, everything
after the first GO statement gets cut off, which in this case is the
guts of the procedure. Does anyone know how how to perform multiple
operatinos like this in one stored procedure?
Thanks in advance
Chris Lieb
CREATE TABLE [HRSC] (
[SEQ_NUM] [float] NULL ,
[HuReSC] [nvarchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
INSERT INTO [HRSC] ([SEQ_NUM], [HuReSC])
VALUES (255, 'TX750201TX769999')
INSERT INTO [HRSC] ([SEQ_NUM], [HuReSC])
VALUES (255, 'OK735001OK735002')
INSERT INTO [HRSC] ([SEQ_NUM], [HuReSC])
VALUES (255, 'LA714000')
INSERT INTO [HRSC] ([SEQ_NUM], [HuReSC])
VALUES (255, 'NM870001NM884001')
----
--
-- Add new columns to the table
ALTER TABLE
[HRSC]
ADD
[Low_State] nvarchar(3) NULL,
[Low_SLIC] nvarchar(3) NULL,
[Low_Building] nvarchar(1) NULL,
[Low_IBI] nvarchar(2) NULL,
[High_State] nvarchar(3) NULL,
[High_SLIC] nvarchar(3) NULL,
[High_Building] nvarchar(1) NULL,
[High_IBI] nvarchar(2) NULL
-- Alter the data type of the HuReSC column to be more space-efficient
ALTER TABLE
[HRSC]
ALTER COLUMN
[HuReSC] nvarchar(18)
GO
-- Prepare the temporary table
DECLARE @.hold table (
[SEQ_NUM] [float] NULL ,
[HuReSC] [nvarchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Low_State] [nvarchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Low_SLIC] [nvarchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Low_Building] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Low_IBI] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[High_State] [nvarchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[High_SLIC] [nvarchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[High_Building] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[High_IBI] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
-- Populate the temporary table with data
INSERT INTO
@.hold ([SEQ_NUM], [HuReSC])
SELECT
[SEQ_NUM],
[HuReSC]
FROM
[HRSC
-- Parse data for low end of range
UPDATE
@.hold
SET
[Low_State] = SUBSTRING([HuReSC], 1, 2),
[Low_SLIC] = SUBSTRING([HuReSC], 3, 3),
[Low_Building] = SUBSTRING([HuReSC], 6, 1),
[Low_IBI] = SUBSTRING([HuReSC], 7, 2)
-- Parse data for high end of range where needed
UPDATE
@.hold
SET
[High_State] = SUBSTRING([HuReSC], 9, 2),
[High_SLIC] = SUBSTRING([HuReSC], 11, 3),
[High_Building] = SUBSTRING([HuReSC], 14, 1),
[High_IBI] = SUBSTRING([HuReSC], 15, 2)
WHERE
LEN([HuReSC]) > 8
-- Clear the real table
TRUNCATE TABLE [HRSC]
-- Populate the table with the parsed data
INSERT INTO
[HRSC]
SELECT
*
FROM
@.hold
GOSince you know the table structure up front, why on earth are you doing
this:
CREATE TABLE
INSERT data
ALTER TABLE add columns
ALTER TABLE ALTER COLUMN but it really doesn't change
INSERT more data
Why not just
CREATE TABLE
INSERT data
The problem here is that you are saying
CREATE PROCEDURE
...do some stuff...
GO
...do more stuff...
The GO is a batch separator, and hence that is where your CREATE PROCEDURE
is completed. Everything after that has nothing to do with the stored
procedure at all. To avoid getting bitten by this, I always use the
following struct:
CREATE PROCEDURE dbo.whatever
AS
BEGIN
... code...
END
GO
Now, if I inadvertently have a GO inside the stored procedure, it won't run,
unless I have horrible code, because the BEGIN and END do not match up.
Of course, if you remove the GO, you will not be able to refer to
non-existant columns within the same batch because the parser will
read-ahead and try to match your column list to the original CREATE TABLE.
So maybe you could explain why you're creating a table, then inserting data,
then adding nullable columns, then altering a column to be the same as it
was before.
A
"Chris Lieb" <chris.lieb@.gmail.com> wrote in message
news:1133819619.878838.109740@.z14g2000cwz.googlegroups.com...
> Hi,
> I am writing a stored procedure in SQL Server 2000 the will require
> multiple commits(?) during its operation. The first group of actions
> that it must perform is to modify a table by adding some columns and
> altering another. The next step reads data out of the table into a
> temporary (in memory) table and does some processing on it and writes
> it back to the original source table utilizing the recently added
> columns. When I try to put this into a stored procedure, everything
> after the first GO statement gets cut off, which in this case is the
> guts of the procedure. Does anyone know how how to perform multiple
> operatinos like this in one stored procedure?
> Thanks in advance
> Chris Lieb
> CREATE TABLE [HRSC] (
> [SEQ_NUM] [float] NULL ,
> [HuReSC] [nvarchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> INSERT INTO [HRSC] ([SEQ_NUM], [HuReSC])
> VALUES (255, 'TX750201TX769999')
> INSERT INTO [HRSC] ([SEQ_NUM], [HuReSC])
> VALUES (255, 'OK735001OK735002')
> INSERT INTO [HRSC] ([SEQ_NUM], [HuReSC])
> VALUES (255, 'LA714000')
> INSERT INTO [HRSC] ([SEQ_NUM], [HuReSC])
> VALUES (255, 'NM870001NM884001')
> ----
--
> -- Add new columns to the table
> ALTER TABLE
> [HRSC]
> ADD
> [Low_State] nvarchar(3) NULL,
> [Low_SLIC] nvarchar(3) NULL,
> [Low_Building] nvarchar(1) NULL,
> [Low_IBI] nvarchar(2) NULL,
> [High_State] nvarchar(3) NULL,
> [High_SLIC] nvarchar(3) NULL,
> [High_Building] nvarchar(1) NULL,
> [High_IBI] nvarchar(2) NULL
> -- Alter the data type of the HuReSC column to be more space-efficient
> ALTER TABLE
> [HRSC]
> ALTER COLUMN
> [HuReSC] nvarchar(18)
> GO
> -- Prepare the temporary table
> DECLARE @.hold table (
> [SEQ_NUM] [float] NULL ,
> [HuReSC] [nvarchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Low_State] [nvarchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Low_SLIC] [nvarchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Low_Building] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Low_IBI] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [High_State] [nvarchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [High_SLIC] [nvarchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [High_Building] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [High_IBI] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> )
> -- Populate the temporary table with data
> INSERT INTO
> @.hold ([SEQ_NUM], [HuReSC])
> SELECT
> [SEQ_NUM],
> [HuReSC]
> FROM
> [HRSC
> -- Parse data for low end of range
> UPDATE
> @.hold
> SET
> [Low_State] = SUBSTRING([HuReSC], 1, 2),
> [Low_SLIC] = SUBSTRING([HuReSC], 3, 3),
> [Low_Building] = SUBSTRING([HuReSC], 6, 1),
> [Low_IBI] = SUBSTRING([HuReSC], 7, 2)
> -- Parse data for high end of range where needed
> UPDATE
> @.hold
> SET
> [High_State] = SUBSTRING([HuReSC], 9, 2),
> [High_SLIC] = SUBSTRING([HuReSC], 11, 3),
> [High_Building] = SUBSTRING([HuReSC], 14, 1),
> [High_IBI] = SUBSTRING([HuReSC], 15, 2)
> WHERE
> LEN([HuReSC]) > 8
> -- Clear the real table
> TRUNCATE TABLE [HRSC]
> -- Populate the table with the parsed data
> INSERT INTO
> [HRSC]
> SELECT
> *
> FROM
> @.hold
> GO
>|||Aaron Bertrand [SQL Server MVP] wrote:
> Since you know the table structure up front, why on earth are you doing
> this:
> CREATE TABLE
> INSERT data
> ALTER TABLE add columns
> ALTER TABLE ALTER COLUMN but it really doesn't change
> INSERT more data
> Why not just
> CREATE TABLE
> INSERT data
The CREATE TABLE and INSERT statements were jsut so you could get the
original table set up to play with. They are not part of the routine
that I want to perform. Also, the ALTER TABLE statement does actually
change the data type of the column, it's just that the CREATE TABLE
that I gave you had already been effected by the ALTER TABLE, so it was
incorrect. I forget the original data type, but the ALTER TABLE
statement does do something.

> The problem here is that you are saying
> CREATE PROCEDURE
> ...do some stuff...
> GO
> ...do more stuff...
> The GO is a batch separator, and hence that is where your CREATE PROCEDURE
> is completed. Everything after that has nothing to do with the stored
> procedure at all. To avoid getting bitten by this, I always use the
> following struct:
> CREATE PROCEDURE dbo.whatever
> AS
> BEGIN
> ... code...
> END
> GO
> Now, if I inadvertently have a GO inside the stored procedure, it won't ru
n,
> unless I have horrible code, because the BEGIN and END do not match up.
> Of course, if you remove the GO, you will not be able to refer to
> non-existant columns within the same batch because the parser will
> read-ahead and try to match your column list to the original CREATE TABLE.
That is exactly the point: I need to have the ALTER TABLE statements
executed before the rest so that I do not get errors. If the batch is
not committed midway, SQL Server tries to interpret the entire script
before it executes and errors saying that the columns do not exist.

> So maybe you could explain why you're creating a table, then inserting dat
a,
> then adding nullable columns, then altering a column to be the same as it
> was before.
> A
Chris Lieb|||> That is exactly the point: I need to have the ALTER TABLE statements
> executed before the rest so that I do not get errors. If the batch is
> not committed midway, SQL Server tries to interpret the entire script
> before it executes and errors saying that the columns do not exist.
Yep, and that's the way SQL Server works. There are a couple of
workarounds, such as dynamic SQL:
http://www.sommarskog.se/dynamic_sql.html
Were it my project, I would probably find a way to get the data structure
right in the first place. Ideally, I would not be changing a table's
structure every time I call a stored procedure.
A|||Do it in two simple steps:
1) fix schema - do it once, but do it right;
2) fix data - do it as many times as needed without changing the schema.
That way you can set the tables 'in concrete' once and for all and then
focus on transforming data. Or do you need to change the tables over and ove
r
again?
ML
http://milambda.blogspot.com/|||I come into this situation alot. (I do a lot of dynamic sql but don't find
that is my favourite solution.) This is a little clunky, but it has worked
for me.
I would create 4 stored procedures, for instance,
spr_DoSomething_Main
spr_DoSomething_1
spr_DoSomething_2
spr_DoSomething_3
where main just says:
exec spr_DoSomething_1
exec spr_DoSomething_2
exec spr_DoSomething_3
If I have parameters it might look like this:
Create Procedure spr_DoSomething_Main
@.Param1 int,
@.Param2 int,
@.Param3 int
AS
exec spr_DoSomething_1 @.Param1
exec spr_DoSomething_2 @.Param1, @.Param2, @.Param3
exec spr_DoSomething_3 @.Param1
go
Whatever procedure needs whatever parameters gets only them. I also
sometimes do this to use stored procedures like classes for possible code
re-use and all...
"ML" wrote:

> Do it in two simple steps:
> 1) fix schema - do it once, but do it right;
> 2) fix data - do it as many times as needed without changing the schema.
> That way you can set the tables 'in concrete' once and for all and then
> focus on transforming data. Or do you need to change the tables over and o
ver
> again?
>
> ML
> --
> http://milambda.blogspot.com/|||Aaron Bertrand [SQL Server MVP] wrote:
> Yep, and that's the way SQL Server works. There are a couple of
> workarounds, such as dynamic SQL:
> http://www.sommarskog.se/dynamic_sql.html
> Were it my project, I would probably find a way to get the data structure
> right in the first place. Ideally, I would not be changing a table's
> structure every time I call a stored procedure.
> A
OK, maybe a stored procedure isn't really the right route to have
chosen.
I am doing this to try to fix a schema that was created a few years ago
by another person who does not work here any more. I was making the
stored procedure so that once I figured out how to parse all of the
data that was on the development server, all I would have to do is have
the SP transferred to the live server and run and the data would be
fixed for me without making me go through and transform all of the data
manually. Having this on the development server also makes it easy to
fix the data when and if I need to mirror data off of the live server
in case something happens, like an error after a truncate statement
before the contents of the temp table are written back to the real
table.
Chris Lieb|||On 6 Dec 2005 06:50:39 -0800, Chris Lieb wrote:

>Aaron Bertrand [SQL Server MVP] wrote:
>OK, maybe a stored procedure isn't really the right route to have
>chosen.
>I am doing this to try to fix a schema that was created a few years ago
>by another person who does not work here any more. I was making the
>stored procedure so that once I figured out how to parse all of the
>data that was on the development server, all I would have to do is have
>the SP transferred to the live server and run and the data would be
>fixed for me without making me go through and transform all of the data
>manually. Having this on the development server also makes it easy to
>fix the data when and if I need to mirror data off of the live server
>in case something happens, like an error after a truncate statement
>before the contents of the temp table are written back to the real
>table.
>Chris Lieb
Hi Chris,
So this is a one-time job?
Instead of making a stored procedure, make an SQL script file. Store it
on your hard disk, tweak it until you're ready, then either copy and
paste into QA and execute against your prod DB or feed it through
osql.exe or a similar tool.
Unlike stored procedure's, SQL scripts can include "go", since a script
is just a load of commands sent to the server (just as if you were
typing very fast <g> ).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis wrote:
> On 6 Dec 2005 06:50:39 -0800, Chris Lieb wrote:
>
> Hi Chris,
> So this is a one-time job?
Once this is deployed, yes. Until then, it will be run many a time.

> Instead of making a stored procedure, make an SQL script file. Store it
> on your hard disk, tweak it until you're ready, then either copy and
> paste into QA and execute against your prod DB or feed it through
> osql.exe or a similar tool.
> Unlike stored procedure's, SQL scripts can include "go", since a script
> is just a load of commands sent to the server (just as if you were
> typing very fast <g> ).
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
I ended up making a DTS package out of the entire procedure, from
creating the tables, indexes, and triggers to transforming and
inserting the data.
Chris Lieb|||On 7 Dec 2005 14:35:28 -0800, Chris Lieb wrote:
(snip)
>Once this is deployed, yes. Until then, it will be run many a time.
Hi Chris,
In test, I hope <g>.
That's what I meant. A one-time job in the production DB.

>I ended up making a DTS package out of the entire procedure, from
>creating the tables, indexes, and triggers to transforming and
>inserting the data.
That's even better. My lack of experience with DTS is showing, I guess.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

multiple SQL commands at once

I'm running asp.net 2.0 and acessing MSSQL 2K. I am trying to run a query in which I need to set up variables first. I tried the following group of commands as shown, passing it to the SqlDataReader object, but it failed.. does anyone know how i can pass multiple SQL commands?

MainQuery = "declare @.MinGrades as Table(GradeID Bigint) " & _
" INSERT @.MinGrades SELECT MIN(CreditGrades.SplitID) AS Expr1" & _
" FROM CreditGrades INNER JOIN" & _
" CreditGradeSplits ON CreditGradeSplits.CreditGradeSplitID = CreditGrades.SplitID " & _
" WHERE (CreditGrades.x0x30 = - 1 OR " & _
" CreditGrades.x0x30 >= 0) AND (CreditGrades.x1x30 = - 1 OR " & _
" CreditGrades.x1x30 >= 0) AND (CreditGrades.x2x30 = - 1 OR " & _
" CreditGrades.x2x30 >= 0) AND (CreditGrades.x3x30 = - 1 OR " & _
" CreditGrades.x3xNOD >= 0) " & _
" GROUP BY CreditGradeSplits.CreditGradeGroupID" & _
" Select * from @.MinGrades "Looks good to me. What error are you getting? Please copy/paste the stack trace if possible as well.|||

Looks good to me. What error are you getting? Please copy/paste the stack trace if possible as well.

I suspect you realize that you don't really need to do that query in 3 commands, and can combine them into a single select.