"No data exists for the row/column." executing OLEDB Oracle OleDbDataReader

Question

I know this is very basic, and I've done this hundreds of times. But, for some strange reason I am executing this command on a database, and it fails when it tries to read a column from the result. If I execute this statement in SQL Plus logged in as the same credentials, the row (table has 1 row) is selected just fine. Any ideas what I am doing wrong? I tried accessing the columns by name, by index, and indeed any column - all of them give no data. I tried without the .NextResult() (just in case), same exception.

'...
' Determine if this database is Multisite enabled
Dim multisiteCmd As OleDbCommand = DbConnection.CreateCommand
multisiteCmd.CommandText = "SELECT * FROM TDM_DB_VERSION;"
Dim dbVersionReader As OleDbDataReader = multisiteCmd.ExecuteReader()
If dbVersionReader.HasRows Then
    dbVersionReader.NextResult()
    'If a ReplicaID was generated for the Database ID, then this is part of a
    'multisite implementation
    'Dim dbRepID As String = dbVersionReader("DB_REPLICID")
    Dim dbRepID As String = dbVersionReader(9)
    PluginSettings.UseMultisite = False
    If Not dbRepID Is Nothing Then
        If dbRepID.Length > 0 Then
            PluginSettings.UseMultisite = True
            PluginSettings.MultisiteReplicaId = dbRepID
        End If
    End If
End If
dbVersionReader.Close()

As you can see from these Immediate commands, the connection is open:

? DbConnection.Provider "OraOLEDB.Oracle" ? DbConnection.State Open {1}

score 1 · Accepted Answer · answered Sep 05 '14 at 15:57

NextResult() is for statements that have more than one result set. For example, if you had sent a command like this:

"SELECT * FROM TDM_DB_VERSION;SELECT * FROM dual;"

Note there are two queries in there. You can handle them both with a single call to the database and a single OleDbDataReader, and NextResult() is part of how you do that.

What you want instead is this:

Dim multisiteCmd As OleDbCommand = DbConnection.CreateCommand
multisiteCmd.CommandText = "SELECT * FROM TDM_DB_VERSION;"
Dim dbVersionReader As OleDbDataReader = multisiteCmd.ExecuteReader()
If dbVersionReader.Read() Then
    'If a ReplicaID was generated for the Database ID, then this is part of a
    'multisite implementation
    'Dim dbRepID As String = dbVersionReader("DB_REPLICID")
    Dim dbRepID As String = dbVersionReader(9)
    PluginSettings.UseMultisite = False
    If Not dbRepID Is Nothing Then ' Do you mean check for DbNull here? "Nothing" is not the same thing
        If dbRepID.Length > 0 Then
            PluginSettings.UseMultisite = True
            PluginSettings.MultisiteReplicaId = dbRepID
        End If
    End If
End If
dbVersionReader.Close()

Yeah, I figured that out and was just coming back to update it! Thanks a ton Joel! — Jay Imerman, Sep 05 '14 at 17:32
I didn't know about the multiple result set, that seems really powerful. So NextResult takes you to the next result set, and you use Read to iterate through the records in each set? — Jay Imerman, Sep 05 '14 at 17:34

"No data exists for the row/column." executing OLEDB Oracle OleDbDataReader

1 Answers1