Reply to thread

Message

[QUOTE="primosteve, post: 162228, member: 47537"]
[b]code in a nicer/easier format i hope[/b]

[xcode=vb]'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[/xcode]
[/QUOTE]

Verification