Question Reading data from a progress database

[primosteve]

Hello all
I am using vb.net 201 2and i need to connect to a progress database via an odbc connection, read data and then save that data to a SQL database. I have suprised myself by being able to do this but i can only read a few records into the app then onto SQL before i get an error message. The message is as follows
" ERROR [HY000] [DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Number of open cursors exceeds limit (7612) ".

Not getting much help when i google the error message but i think i understand why i get it, and to stop it i need to close my connection for every go round my loop but now sure how to do that as i use code as follows

<code removed by moderator>

Hope that all makes sense and any help is very gratefully appreciated

Steve

 

[primosteve]

code in a nicer/easier format i hope

'Pecas connection string
    Public strPECAScon As String = ("DRIVER={Progress OpenEdge 10.2B Driver};HOST=10.0.11.4;PORT=4500;DB=vision;UID=vision;PWD=;DIL=READ UNCOMMITTED;")
    Public CONcompanyPECAS As New OdbcConnection(strPECAScon)
    'SQl connection string
    Public strSQLCon = ("Data Source= 10.0.11.3\SQLEXPRESS;Initial Catalog= PECASDATA;User Id= sa;Password=boston001;MultipleActiveResultSets=True;")
    Public CONcompanySQL As New SqlClient.SqlConnection(strSQLCon)

  'setting start time
        Dim INTcount As Integer = 0
        Dim DTstarttime As Date = Now
        Label10.Text = DTstarttime

        'reading sales order numbers one by one
        Dim STRsono As String = "SELECT Sordernum FROM PUB.PV_sorder"
        Dim cmdsono As New OdbcCommand(STRsono, CONcompanyPECAS)
        CONcompanyPECAS.Open()
        rdsonumb = cmdsono.ExecuteReader
        While rdsonumb.Read

            ' Reading sonumber from table
            STRsonum = rdsonumb.Item("Sordernum")

            ' doing a count
            INTcount = INTcount + 1
            Txtcurrcount.Text = CStr(INTcount)

            ' Reading sales order table in SQL to see if new order 
            Dim STRsqlsono As String = "SELECT * FROM SOPECAS where order_number = '" & STRsonum & "'"
            Dim cmdsqlsono As New SqlClient.SqlCommand(STRsqlsono, CONcompanySQL)
            If CONcompanySQL.State = ConnectionState.Closed Then
                CONcompanySQL.Open()
            End If
            rdsqlsono = cmdsqlsono.ExecuteReader
            If rdsqlsono.HasRows = False Then

                'If new line
                Dim STRitem As String = ""
        Dim STRitemshort As String = ""
        Dim STRitemlong As String = ""
        Dim STRclean1 As String = ""
        Dim STRclean2 As String = ""

        'Add a new line to the SQL table, STARTING WITH ITEM NO.
        Dim STRsonoitem As String = "SELECT itemcode FROM PUB.PV_sorderline where Sordernum = '" & STRsonum & "'"
        Dim cmdsonoitem As New OdbcCommand(STRsonoitem, CONcompanyPECAS)
        rditem = cmdsonoitem.ExecuteReader
        While rditem.Read
            ' Reading item from so table
            STRitem = rditem.Item("itemcode")
        End While
        cmdsonoitem.Dispose()

        ' adding item description
        Dim STRsonoitemdesc As String = "SELECT itemdesc, itemshortdesc FROM PUB.PM_item where itemcode = '" & STRitem & "'"
        Dim cmdsonoitemdesc As New OdbcCommand(STRsonoitemdesc, CONcompanyPECAS)
        rditemdesc = cmdsonoitemdesc.ExecuteReader
        While rditemdesc.Read
            ' Reading item from item table
            STRitemlong = rditemdesc.Item("itemdesc")
            STRclean1 = STRitemlong.Replace("'", "")
            STRitemshort = rditemdesc.Item("itemshortdesc")
            STRclean2 = STRitemshort.Replace("'", "")
        End While
        cmdsonoitemdesc.Dispose()

        'Adding so and item etc to table
        Dim STRnewitem As String = "INSERT INTO SOPECAS (ORDER_NUMBER, ITEM, SHORT_DESC, LONG_DESC) values ('" & STRsonum & "','" & STRitem & "','" & STRclean2 & "','" & STRclean1 & "')"
        Dim CMDnewitem As New SqlClient.SqlCommand(STRnewitem, CONcompanySQL)
        CMDnewitem.ExecuteNonQuery()
        INTnewcount = INTnewcount + 1
        Txtnewso.Text = CStr(INTnewcount)
        CMDnewitem.Dispose()
            Else

                'If already exist
                UPDATELINE()
            End If
            CONcompanySQL.Close()
            Txtlastcount.Text = CStr(INTcount)
        End While

        'Showing end of cycle time
        CONcompanyPECAS.Close()
        Dim DTendtime As Date = Now
        Label11.Text = DTendtime

 

[jmcilhinney]

You should be calling Close on each data reader when you finish reading the data it exposes.

 

[primosteve]

Hello jmcilhinney

Thanks for your reply

I would normally close the connection at the end of the read while procedure as shown.(Start line 16, end Line 83) . If i close it elsewhere i get the "cannot read while connection closed" error message on the next iteration. This will read, eventually , thousands of lines at a time.

Can you point me in the right direction of where to close the connection or is there another method of reading the data via an odbc connection that i havnt thought of. Otherwise the only solution i can think of is to read the data in a line at a time that would potentially affect overall performance

Many thanks

Steve

 

[jmcilhinney]

Hello jmcilhinney

Thanks for your reply

I would normally close the connection at the end of the read while procedure as shown.(Start line 16, end Line 83) . If i close it elsewhere i get the "cannot read while connection closed" error message on the next iteration. This will read, eventually , thousands of lines at a time.

Can you point me in the right direction of where to close the connection or is there another method of reading the data via an odbc connection that i havnt thought of. Otherwise the only solution i can think of is to read the data in a line at a time that would potentially affect overall performance

Many thanks

Steve

Hmmm... that's all well and good but where did I say that you should close the connection?

You should be calling Close on each data reader when you finish reading the data it exposes.

 

[primosteve]

Hello jmcilhinney

Thank you for taking the time to read and help. I know your pointing me at somthing but i just cant see it. As far as i can see i am closing the connection after finishing reading my main query/loop . My problem relates to reading to much data in one go. If i move where i call the close i get the reader cannot read connection error.

Im going to try and break it down further and see if i can see it but thank you for pointing me at 'somthing ' lol

Steve

 

[jmcilhinney]

It's like you're not even reading what I'm posting. I am NOT telling you to close the connection. OK? DO NOT close the connection. I am telling you to close the data reader. Do you see the difference? One is the connection and one is the data reader. Don't close the connection. Close the data reader.

 

[primosteve]

Sir

You are correct and damn my ignorance. I was so focused on the bloody connection that i couldnt see the woods for the trees

Many many thanks for your help withth is it is greatly appreciated and all is working well

Steve