CODEWITHSUNDEEP
vb.netoracle-databaseoledboledbdatareader
Jay Imerman
Jay Imerman

Reputation: 4600

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

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}

Upvotes: 0

Views: 847

Answers (1)

Joel Coehoorn
Joel Coehoorn

Reputation: 416149

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()

Upvotes: 1

Related Questions