No results from Oracle query in VB ASPX

Question

New Why would a VB/ASPX page not return results for a query which runs fine with other Oracle clients? (Same username)

Old

The following code should first output the query and then execute it outputting a single exclamation point per record. However, it outputs the query (as it should), but does not return any rows (nor error). If I copy the query to SQL Plus (or other editor) as the same user, the query returns results.

Why might my code not output any results?

Dim MyQuery As String = "...some query..."
Dim Factory As DbProviderFactory = DbProviderFactories.GetFactory("System.Data.OracleClient")
Dim myConn As DbConnection = Factory.CreateConnection
Dim myCommand As DbCommand = Factory.CreateCommand
Dim MyReader As DbDataReader

myConn.ConnectionString = LimsQuery.ConnectionString
myConn.Open()

myCommand.Connection = myConn
myCommand.CommandText = MyQuery
myCommand.CommandType = CommandType.Text

Response.Write(myCommand.CommandText)

MyReader = myCommand.ExecuteReader()

ResultsGrid.Columns.Clear()

While MyReader.Read()
    Response.Write("!")
End While

Answer 1

Are you using the same SQL Connection login to Oracle . I.e. ASPX and Toad/SQLDeveloper/Whatever use the same login.

When you login via a SQL Client, certain roles are granted to you when the SQL-client session is established .

But you want to check that.

**If ** its a different login, then that explains a lot of things.

Additionally, "all_tab_columns" is a system data dictionary table - can you try with "user_tab_columns" instead ? - It will give you the same thing.

Answer 2

The only thing I see wrong is your failure to implement "Using" blocks:

    Dim MyQuery As String = "...some query..."
    Dim Factory As DbProviderFactory = DbProviderFactories.GetFactory("System.Data.OracleClient")
    Using myConn As DbConnection = Factory.CreateConnection
        Using myCommand As DbCommand = Factory.CreateCommand

            myConn.ConnectionString = LimsQuery.ConnectionString
            myConn.Open()

            myCommand.Connection = myConn
            myCommand.CommandText = MyQuery
            myCommand.CommandType = CommandType.Text

            Response.Write(myCommand.CommandText)

            Using MyReader As DbDataReader = myCommand.ExecuteReader()
                ResultsGrid.Columns.Clear()

                While MyReader.Read()
                    Response.Write("!")
                End While
            End Using
        End Using
    End Using

Answer 3

There could be some typo and the query simply isn't executing. For example, SQL*PLus expects a semi-colon at the end of a statement, or a backslash on a new line, to execute it. The semi-colon isn't part of the SQL command so shouldn't be sent by VB

Answer 4

Maybe you are relying on something like an implicit date conversion in the SQL and the environments are setting the NLS values differently.

Answer 5

Oops, small typo caused the problem. I was referencing the wrong table.