0

I cannot seem to find the answer to my problem anywhere on the internet. I have seen solutions for others such as adding keys to appsettings etc, none of which have worked for me.

Imports Oracle.DataAccess.Client
Imports Oracle.DataAccess.Types
Imports System.Data

Sub Button1Click(sender As Object, e As EventArgs)
Dim oradb As String = "DATA SOURCE=INITIATE;PASSWORD=pASS;PERSIST SECURITY INFO=True;USER ID=uSER"
    Dim conn As New OracleConnection(oradb)     
        Try
            Dim sql As String = "select MEMRECNO from INIT.MPI_MEMHEAD where MEMIDNUM = '" + txtMRN.Text + "'"
            Dim cmd As New OracleCommand(sql, conn)
            cmd.CommandType = CommandType.Text

            Dim dr As OracleDataReader = cmd.ExecuteReader()
            dr.Read()
            txt1.Text = dr.GetInt32(0)

        Catch ex As Exception

            richTextBox1.Text = richTextBox1.Text + vbCrLf + ex.Message
        End Try

    End Sub

Now, when I run this code, if I enter a value in the top 25 records (when visually looking at the table in Oracle) it returns the result. However, when I enter a value that might be record number 1 million, i get this error: "Operation is not valid due to the current state of the object."

Yes, the value does exist. Because if I run the exact same query in Oracle, I get the result.

This leads me to believe that the connection is timing out, closing, or there is a limit on how many rows can be returned using the Oracle Data Access Client.

BenMorel
  • 34,448
  • 50
  • 182
  • 322
You
  • 47
  • 1
  • 8
  • I don't see you opening the connection in your code before trying to read, but, since you were successful it getting values from the top 25 records, I assume that this is just an omission here in the code you posted while the code in your project is correct. – Racil Hilan Mar 19 '14 at 14:53
  • That aside, from your description it seems you're right in assuming that the connection is timing out, maybe the query takes a long time perhaps because your `MEMIDNUM` column is not indexed. To confirm this theory debug your code to see the status of your connection or add it to the message in your `richTextBox1`. Anyway, try adding an index to your `MEMIDNUM` column and see if that will fix it. – Racil Hilan Mar 19 '14 at 14:53
  • Racil, the connection is actually opened on form load, which should not matter. As far as adding an index to the column, I am not sure if you are referring to inside my project or in the db itself. I am using Sharp Develop (freeware) to write vb.net code, so I haven't actually added a dataset the normal way (how VB 2012+ does it). I am willing to try anything but I am not sure what you mean by adding an index. Debugging does nothing, other than give me the error. – You Mar 19 '14 at 15:02
  • While debugging, right-click on `conn` and select **QuickWatch** then expand `conn` and check the status. As for the index, you will have to add it in the database. I haven't used Sharp Develop, so I don't know if there is a way to add it from there. Without an index, you're query has to scan the entire table and that can be very slow if you have lots of records. – Racil Hilan Mar 19 '14 at 15:10
  • **WARNING:** Your code has a [**SQL Injection**](http://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work) vulnerability – ɐsɹǝʌ ǝɔıʌ Mar 19 '14 at 15:12
  • Racil, unfortunately, sharp develop does not have that quick watch debug feature. My boss also said that the oracle db tables are already indexed. We just have millions upon millions of records. – You Mar 19 '14 at 15:16
  • Equisde, there is no SQL Injection Vulnerability. If you read my original post, I can run other values that return fine, because they are some of the first records populated in the db table. If it works for one, it should work for all. But, it is broken. – You Mar 19 '14 at 15:18
  • @user3438105 It is totally vulnerable to SQL injection. Just write `X'); DROP TABLE INIT.MPI_MEMHEAD; --` at `txtMRN` and you'll lose your `MPI_MEMHEAD` table when clicking on `Button1` – ɐsɹǝʌ ǝɔıʌ Mar 19 '14 at 15:31
  • Ahh I see what you meant now. However, I will be the only one using this program. I promise you, I won't be dropping tables unintentionally. – You Mar 19 '14 at 15:32
  • @user3438105 I hope so :) – ɐsɹǝʌ ǝɔıʌ Mar 19 '14 at 15:34
  • Hmmm, interesting product. Good to know about it. Anyway, in debug mode, just point to `conn`, expand it by clicking on the `+` sign, and examine the value of its `State` property. Alternatively, you can change your last line to `richTextBox1.Text += vbCrLf + conn.State.ToString() + vbCrLf + ex.Message`. – Racil Hilan Mar 20 '14 at 07:01
  • So your boss said that "*the oracle db tables are already indexed*". I hope that includes an index on the `MEMIDNUM` which is all what matters for your query. OK, let him then run the query directly against the db with two values that you tried (i.e. the small one and big one) and let us know how long it is taking each of them to return the result. Also let him run the two queries but with `EXPLAIN PLAN FOR` at the beginning this time and let us know the results. This will show us how Oracle is executing the queries and whether it is using the index or not. – Racil Hilan Mar 20 '14 at 07:14
  • Racil, added connection state to RTB, and it says "Open". Using explain plan, all is says is plan successful. There is no output of the plan. However, the task executed (for the number that doesnt work in vb) in 0.004 seconds. – You Mar 20 '14 at 14:55
  • @user3438105 Ok, we're making a progress here. So the status is "Open" which is good, and the query is running in Oracle in 0.004 seconds which means it is indexed properly. Obviously, the problem is something else. Are you sure the value is an integer? It sucks that you don't have good debugging features. OK, change your last line to `richTextBox1.Text += vbCrLf + ex.Message + vbCrLf + ex.StackTrace`, run it, and post the value here in your question so we can have a look. – Racil Hilan Mar 20 '14 at 22:15
  • Racil, I am pretty sure it is an integer. When I run the query in oracle it returns a solid number, no letters etc. Here is the stacktrace: at Oracle.DataAccess.Client.OracleDataReader.GetInt32(Int32 i). – You Mar 22 '14 at 18:34
  • @user3438105 I cannot see the stack trace (I see only one line). You need to edit your question and post the stack trace there. – Racil Hilan Mar 23 '14 at 16:32

0 Answers0