none
Execution of query returns no resultSet nor Exception RRS feed

  • General discussion

  • Hello,
    I have an application in Net 3.5 written in VB. My underlying DB is Oracle 10g. So far everything works fine.
    However, I recently have problems with a page that USED TO WORK. All of the sudden, when I execute the query -which is in a try/catch block- the execution of the code stops. No error, not an empty resultSet, nothing. Sometimes however, it still works(!), especially in the production server which has more resources than my development PC.
    The query runs OK in the Oracle interface, so it is not a syntax error; after all it's a simple select from one table only. It seems more like a timeout, but why do I not get an exception error after the commandTimeout has elapsed? I also tried to execute the same query with the OracleDataAdapter/DataSet and I have the same problem. Any suggestions?

     Code is as follows:

    Dim myCon as New ADODB.Connection
    myCon.ConnectionString = "xxx" 'Demo

    Dim dr as ADODB.Recordset
    Dim myComm as ADODB.Command
    myComm.CommandTimeout = 60
    myComm.CommandText = "Select * from Table1 where Violation=1"

    Try

        try
            myCon.Open()
            myComm.ActiveConnection=myCon
            dr = myComm.Execute() ' CODE EXECUTION HALTS HERE
        catch ex as exception  'Code doesn't enter this catch
           
        End try

        if (dr.HasRows) then  
             while not dr.EOF
                    ....
             end while
         end if

    catch ex as exception 'Code doesn't reach this catch
    finally      'Finally is never executed
          dr.close()
          myCon.close()
          myCon.Dispose()
    end try

    Of course, connections stay open since code is halted before they close and after a while the IIS server does not respond anymore. All other pages that query the same DB, using DataSet mostly, work fine.
    Please help!
    Wednesday, July 15, 2009 9:59 AM

All replies

  • Have you tried executing the exact query against that server using a standalone query tool?

    If that doesn't work, this sounds like something you might need the help of your DBA to troubleshoot...

    This forum is for ADO.NET, so it would have been better if you posted the DataAdapter/DataSet code instead, but like you said, it didn't make a difference.
    Wednesday, July 15, 2009 11:53 PM
  • The query runs just fine from Oracle's SQL Plus (I guess that's the standalone query tool you mean?). So there is no problem with it. As I said, it's a simple SELECT statement from a single table and it most often than not, works when I run it from the production server. Granted, it returns quite a lot of data (about 3000 rows) but even that is not so much that it would cause the server to stop responding. My only guess is that it takes too long to return the results and the IIS signals a timeout. But shouldn't the Try/catch loop catch the timeout error anyway?
    Thursday, July 16, 2009 4:37 AM
  • I'm not sure that I understand your code. It looks like you are using Classic ADO (not ADO.NET) and I know for certain that the Recordset class does not have a HasRows method or property.

    I don't see how this code could possibly work and would expect an exception (I don't see where you display the exception messages).


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, July 16, 2009 2:06 PM
  • Ok, I wrote the classic ADO code above. I also tried ADO.NET and this one fails more often than classic ADO:

    sql = "Select * from Table1 where Violation=1"

    Dim myCon as New OracleClient.OracleConnection
    myCon.ConnectionString = "xxx" 'Demo
    Dim ds as New Dataset
    Dim DA as new OracleClient.OracleDataAdapter(sql, myCon)

        try
            DA.Fill(ds) ' CODE EXECUTION HALTS HERE
        catch ex as exception  'Code doesn't enter this catch
            Response.write("Error")
        finally      'Finally is never executed
          ds.dispose()
          dA.dispose()
          myCon.close()
          myCon.Dispose()
    end try


    I mean that even in debug, the debugger doesn't go inside catch. In run mode the user sees perpetually a blank window: NO error messages, no results. I put a Response.write inside the catch now; I hope it makes it clearer to you. When it runs -because as I said sometimes it does- it only takes 4-5 seconds, no more. But when it doesn't run once, then everytime after that, it will fail also.

    Any guesses?
    Friday, July 17, 2009 4:20 AM
  • It sounds like there's something happening outside of the actual .NET code, because the code is running but sometimes you get results back and sometimes you don't, in the absence of any exceptions.

    Are you using the same criteria or does it change each time you run it? Could there be a network problem? Has your Oracle DBA checked the Oracle logs?
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, July 17, 2009 1:33 PM
  • We are changing the issue type to “General Discussion” because you have not followed up with the necessary information. If you have more time to look at the issue and provide more information, please feel free to change the issue type back to “Question” by opening the Options list at the top of the post window, and changing the type. If the issue is resolved, we will appreciate it if you can share the solution so that the answer can be found and used by other community members having similar questions.

    Thank you!

    Best Regards,
    Lingzhi


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, August 7, 2009 5:07 AM
    Moderator
  • Can someone please tell me what was the solution to this? I'm having the exact same problem.

    Thanks,

    Neil

    Monday, March 19, 2012 9:06 PM
  • I would recommend posting a new question.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, March 19, 2012 10:24 PM
  • I have never used Oracle so this may be nothing but have seen similar issues with IBM-DB2 where there were issues which went away after adding an index for the column used in the where clause of an SQL statement.

    Also as other have suggested it seems prudent to consult with your DBA, look at logs, has there been any software updates for the backend database etc.


    KSG

    Wednesday, March 21, 2012 1:46 PM