Question Reading data from a progress database

primosteve

Member
Joined
Jul 30, 2013
Messages
5
Programming Experience
5-10
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
 
Last edited by a moderator:

primosteve

Member
Joined
Jul 30, 2013
Messages
5
Programming Experience
5-10
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
 
Last edited by a moderator:

primosteve

Member
Joined
Jul 30, 2013
Messages
5
Programming Experience
5-10
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

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,148
Location
Sydney, Australia
Programming Experience
10+
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

Member
Joined
Jul 30, 2013
Messages
5
Programming Experience
5-10
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

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,148
Location
Sydney, Australia
Programming Experience
10+
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

Member
Joined
Jul 30, 2013
Messages
5
Programming Experience
5-10
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
 
Top Bottom