Multiple Select Statements in resultset

[Joe via DotNetMonster.com]

Hi,

I'm trying to use several select statements so that I don't need to call
the function several times. The next Result set always seems to read the
first select statement. I have the following:


Dim queryString As String = "SELECT TOP 1 ([tblPage].[Part]), [tblPage].
[PageNumber] FROM [tblPage] WHERE ([tblPage].[LessonID] = @LessonID) AND
[tblPage].[Part]='Motivate'; SELECT TOP 1 ([tblPage].[Part]), [tblPage].
[PageNumber] FROM [tblPage] WHERE ([tblPage].[LessonID] = @LessonID) AND
[tblPage].[Part]='Present'"

If (dataReader.Read = True) Then
Dim Part = dataReader("Part")
Response.Write("Part is "&Part)
dataReader.NextResult()
Response.Write("Part is "&Part)
End If

So, I always get the first statement where part=motivate. I don't want to
use a Do While because I'll be doing something different with each result
set. I've just simplified it in the above example.

Thanks so much.

 

[Guest]

Joe it is hard to see what your trying to do but if your trying to base one
query off another you might try setting up a DataRelation Collection with
multiple DataTable commands in a DataSet instead of building multiple
DataReaders or trying to join multiple tables within a Select Command. It
might be easier for you to handle your Data that way

 

[Mike Douglas]

Joe,

Your sample below always would return the part in the first resultset.
After each datareader.NextResult() you would need to do
Part = dataReader("Part")
You probably also need the If (dataReader.Read = True) Then before the
Part = ...

Another alternative might be to UNION your SELCT statement so you just
have one resultset.

I hope that helps.

Mike Douglas

 

[Jim via DotNetMonster.com]

Thanks for you help. I ended up using a DataView Object in the Dataset and
that seems to work.