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/
