Results 1 to 8 of 8

Thread: Reading data from a progress database

  1. #1
    primosteve is offline VB.NET Forum Newbie
    .NET Framework
    .NET 4.5
    Join Date
    Jul 2013
    Posts
    5
    Reputation
    0

    Lightbulb Reading data from a progress database

    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 jmcilhinney; 07-31-2013 at 1:19 AM. Reason: Removed hard-to-read code

  2. #2
    primosteve is offline VB.NET Forum Newbie
    .NET Framework
    .NET 4.5
    Join Date
    Jul 2013
    Posts
    5
    Reputation
    0

    Talking 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 jmcilhinney; 07-31-2013 at 1:18 AM. Reason: Added [xcode=vb] tags for readability

  3. #3
    jmcilhinney's Avatar
    jmcilhinney is offline VB.NET Forum Moderator
    .NET Framework
    .NET 4.0
    Join Date
    Aug 2004
    Location
    Sydney, Australia
    Posts
    13,801
    Reputation
    1705
    You should be calling Close on each data reader when you finish reading the data it exposes.

  4. #4
    primosteve is offline VB.NET Forum Newbie
    .NET Framework
    .NET 4.5
    Join Date
    Jul 2013
    Posts
    5
    Reputation
    0
    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

  5. #5
    jmcilhinney's Avatar
    jmcilhinney is offline VB.NET Forum Moderator
    .NET Framework
    .NET 4.0
    Join Date
    Aug 2004
    Location
    Sydney, Australia
    Posts
    13,801
    Reputation
    1705
    Quote Originally Posted by primosteve View Post
    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?
    Quote Originally Posted by jmcilhinney View Post
    You should be calling Close on each data reader when you finish reading the data it exposes.

  6. #6
    primosteve is offline VB.NET Forum Newbie
    .NET Framework
    .NET 4.5
    Join Date
    Jul 2013
    Posts
    5
    Reputation
    0
    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

  7. #7
    jmcilhinney's Avatar
    jmcilhinney is offline VB.NET Forum Moderator
    .NET Framework
    .NET 4.0
    Join Date
    Aug 2004
    Location
    Sydney, Australia
    Posts
    13,801
    Reputation
    1705
    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.

  8. #8
    primosteve is offline VB.NET Forum Newbie
    .NET Framework
    .NET 4.5
    Join Date
    Jul 2013
    Posts
    5
    Reputation
    0
    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

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •