Also, you might want to note, calling NextResult skips past any rows
in the current set of results. Something like this is what you
probably want to do...
Do
While dr.Read()
Debug.WriteLine(dr(0))
End While
Loop While dr.NextResult()
On 7/15/05, Dean Fiala <[EMAIL PROTECTED]> wrote:
> Chris,
> NextResult just moves you to the next result, it doesn't go to the
> first record of the next result. You need to do a Read first.
>
> While dr.Read()
> s(0) = dr.GetString(0)
> If dr.NextResult() Then
> if dr.Read() Then
> s(1) = dr.GetString(0)
> End if
> End if
>
> End While
>
>
> On 7/15/05, Chris Mohan <[EMAIL PROTECTED]> wrote:
> > Hi, i've made the sample code more simple so that its easier to
> > read/ focus on the problem/question. (Not to mention that the code
> > in my original post contained errors introduced when i had made
> > some changes to it in order to post it here.)
> >
> > SO, here is the code that better demonstrates the issue:
> >
> > Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)Handles
> > MyBase.Load
> > Dim s() As String = GetTSqlObjectsFromDataBase()
> > Response.Write(s(0))
> > Response.Write("<BR>")
> > Response.Write(s(1))
> > End Sub
> >
> > Function GetTSqlObjectsFromDataBase() As String()
> > Dim conn As New SqlConnection(AppSettings("ConnectionString"))
> > Dim cmd As SqlCommand = conn.CreateCommand()
> > With cmd
> > .CommandType = CommandType.Text
> > .CommandText = "SELECT text FROM dbo.syscomments WHERE " & _
> > "(text LIKE '%CREATE FUNCTION dbo.khan_encrypt_pair%');" & _
> > "SELECT text FROM syscomments WHERE " & _
> > "(text LIKE '%EncryptUserDataAndInsert%')"
> > End With
> > Dim sb As New StringBuilder
> > Dim dr As SqlDataReader
> > Dim s(2) As String
> > Try
> > conn.Open()
> > dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
> > While dr.Read()
> > s(0) = dr.GetString(0)
> > If dr.NextResult() Then s(1) = dr.GetString(0)
> > End While
> > Return s
> > Finally
> > conn.Close()
> > conn.Dispose()
> > cmd.Dispose()
> > End Try
> > End Function
> >
> >
> >
> >
> >
> >
> >
> >
> > --- In [email protected], "Chris Mohan"
> > <[EMAIL PROTECTED]> wrote:
> > > Hi,
> > >
> > > I'm getting an error that (in my mind) shouldn't happen based on
> > the
> > > values of certain data reader properties.
> > >
> > > The Error: System.InvalidOperationException: Invalid attempt to
> > read
> > > when no data is present.
> > >
> > > My query consists of two batched sql statements delimited by a
> > semi-
> > > colon. I always get the error on the second dr.Read() regardless
> > of
> > > which of the two batched statements is listed first. Each query
> > > return results individually and when run together in Query
> > Analyzer.
> > >
> > > The error always happens after i call If Dr.NextResult() THEN
> > which
> > > always returns true AND the dataReader's field count is always the
> > > same as it was during the first read, 1.
> > >
> > > That tells me there's a result to read. So why am i getting this
> > > error??!
> > >
> > > The entire code chunk is below
> > > Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)Handles
> > > MyBase.Load
> > > Dim tSqlObjectNames() As String = {"%CREATE FUNCTION
> > > dbo.khan_encrypt_pair%", "%EncryptUserDataAndInsert%"}
> > > Me.txtSprocText.Text = GetTSqlObjectsFromDataBase(tSqlObjectNames)
> > > End Sub
> > >
> > > Function GetSqlObjs(ByVal names()As String) As String
> > > Dim conn As New SqlConnection(AppSettings("ConnectionString"))
> > > Dim cmd As SqlCommand = conn.CreateCommand()
> > > With cmd
> > > .CommandType = CommandType.Text
> > > For i As Int32 = 0 To tSqlObjectNames.Length - 1
> > > .CommandText += "SELECT text FROM syscomments WHERE (text LIKE
> > > @text" & i.ToString() & ")"
> > >
> > > If i <> tSqlObjectNames.Length - 1 Then .CommandText += ";"
> > > .Parameters.Add("@text" + i.ToString(),SqlDbType.NVarChar, names
> > > (i).Length).Value = tSqlObjectNames(i)
> > > Next
> > > End With
> > > Dim sb As New StringBuilder
> > > Dim dr As SqlDataReader
> > > Try
> > > conn.Open()
> > > dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
> > > While dr.Read()
> > > sb.Append(dr.GetString(0))
> > > If dr.NextResult() Then sb.Append(dr.GetString(0))
> > > End While
> > > Return sb.ToString()
> > > Catch ex As Exception
> > > ErrorMessage.Text = "error: " & ex.ToString()
> > > Return sb.ToString()
> > > Finally
> > > conn.Close()
> > > conn.Dispose()
> > > cmd.Dispose()
> > > End Try
> > > End Function
> > >
> > > I've even run the query without the use of SQL Parameters by
> > > hardcoding the batched statement and i still get the error.
> > > Here's the statement in one line:
> > > .CommandText = "SELECT text FROM dbo.syscomments WHERE (text
> > LIKE '%
> > > CREATE FUNCTION dbo.khan_encrypt_pair%'); SELECT text FROM
> > > syscomments WHERE (text LIKE '%EncryptUserDataAndInsert%');"
> >
> >
> >
> >
> >
> > Yahoo! Groups Links
> >
> >
> >
> >
> >
> >
> >
>
>
> --
> Dean Fiala
> Very Practical Software, Inc
> http://www.vpsw.com
>
--
Dean Fiala
Very Practical Software, Inc
http://www.vpsw.com
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/AspNetAnyQuestionIsOk/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
