DR.nextResultSet should never be called more than once.
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
>
>
> Visit your group "AspNetAnyQuestionIsOk" on the web.
>
> To unsubscribe from this group, send an email to:
> [EMAIL PROTECTED]
>
> Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
>
> ________________________________
>
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/
