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
<*> 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/
