Hi Charles, thanks for the info. I haven't heard that before and can't information on it. What are the negatives consequences of calling DataReader.NextResult() more than once. I tweaked my sample code so that it would get called three times.. and (at least on a surface level) it worked fine.
Is this a performance/memory allocation issue? --- In [email protected], Charles Carroll <[EMAIL PROTECTED]> wrote: > 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/
