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.Rea d = True) Then
Dim Part = dataReader("Par t")
Response.Write( "Part is "&Part)
dataReader.Next Result()
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.

--
Message posted via http://www.dotnetmonster.com

[I am Sam]

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

"Joe via DotNetMonster.c om" wrote:

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.Rea d = True) Then
Dim Part = dataReader("Par t")
Response.Write( "Part is "&Part)
dataReader.Next Result()
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.

--
Message posted via http://www.dotnetmonster.com

[Mike Douglas]

Joe,

Your sample below always would return the part in the first resultset.
After each datareader.Next Result() you would need to do
Part = dataReader("Par t")
You probably also need the If (dataReader.Rea d = 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

On Sat, 26 Mar 2005 18:03:24 GMT, "Joe via DotNetMonster.c om"
<fo***@DotNetMo nster.com> wrote:

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.Rea d = True) Then
Dim Part = dataReader("Par t")
Response.Write( "Part is "&Part)
dataReader.Next Result()
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.

[Jim via DotNetMonster.com]

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

--
Message posted via http://www.dotnetmonster.com