Stack Overflow VB.net SQL Query

Question

I am getting through about 5000 queries before I get a stack overflow exception at the line where I call NewQuery2 again. Any ideas on what I can do to prevent this? I am trying to run about 40,000 queries.

Public Sub NewQuery2()
        Dim ID2 As String
        Try
            Dim ID As String
            ID = listBox1.Items(Next1)
            ID2 = ID
            Dim sql As String = "select INIT.MPI_MEMHEAD.MEMSTAT from INIT.MPI_MEMHEAD where INIT.MPI_MEMHEAD.MEMIDNUM = '" + ID + "'"
            Dim cmd As New OracleCommand(sql, conn)
            cmd.CommandType = CommandType.Text
            Dim dr As OracleDataReader = cmd.ExecuteReader()
            dr.Read()
            If dr.GetString(0) = "M" Then
                txtMerge.Text = txtMerge.Text + 1
            Else
                textBox1.Text = textBox1.Text + 1   
            End If
            dr.Dispose
            cmd.Dispose
        Catch ex As Exception   
            richTextBox1.Text = richTextBox1.Text + ex.Message + vbCrLf + ID2 
        End Try 
        Application.DoEvents
        If Next1 = listbox1.Items.Count - 1 Then
            conn.Dispose
        Else        
            Next1 = Next1 + 1
            NewQuery2()
        End If
    End SUb

why are you recursing instead of just looping on the listbox items? you also need to start accepting the answers you get — Ňɏssa Pøngjǣrdenlarp
– Ňɏssa Pøngjǣrdenlarp, Commented Apr 23, 2014 at 19:09
Never use Application.DoEvents blogs.msdn.com/b/jfoscoding/archive/2005/08/06/448560.aspx — rory.ap
– rory.ap, Commented Apr 23, 2014 at 19:14
I tried that originally. The problem is, there is a max connection limit and timeout on the oracle db which stops at 99. Thus I am recursing. — You
– You, Commented Apr 23, 2014 at 19:14
Nevermind. I see what you meant. I was originally looping in the connection. I have solved this issue based on your advice. Thank you! — You
– You, Commented Apr 23, 2014 at 19:19

rory.ap · Accepted Answer · 2014-04-23 19:10:09Z

1

This is happening because you're calling the method recursively from within itself and it's running out of stack space. Try creating an external method instead that loops through your listbox and calls this method (without the call to itself, of course).

answered Apr 23, 2014 at 19:10

rory.ap

35.7k15 gold badges104 silver badges199 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Stack Overflow VB.net SQL Query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related