Dean, thanks for the great info! Strange, I've successfully used "nextResult" with batched queries in the past, I don't know how i forgot that you need to call read again.
--- In [email protected], Dean Fiala <[EMAIL PROTECTED]> wrote: > 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/
