ProTech's home page

ProTech-Online.com

Visual Studio form code example for EDI AS2 communication.

Imports System.IO
Imports System.Diagnostics
Imports System.Data
Imports System.Data.SqlClient
Imports System.Net.Mail
Imports Excel = Microsoft.Office.Interop.Excel
Imports nsoftware.IPWorksEDI
Imports IBM.Data.DB2
Public Class Form1
    Dim ACKS_CON As SqlConnection = New SqlConnection("Data Source=********; Initial Catalog=********; User Id=********; Password=********")
    Dim ACKS_DA As SqlDataAdapter = New SqlDataAdapter("SELECT TOP 1 MSG_ID, EDI_DATA, STATUS FROM SENT_ACKS", ACKS_CON)
    Dim ACKS_DS As DataSet = New DataSet
    Dim INVS_CON As SqlConnection = New SqlConnection("Data Source=********; Initial Catalog=********; User Id=********; Password=********")
    Dim INVS_DA As SqlDataAdapter = New SqlDataAdapter("SELECT TOP 1 MSG_ID, EDI_DATA, STATUS FROM SENT_ACKS", INVS_CON)
    Dim INVS_DS As DataSet = New DataSet
    Dim INVOICES_CON As SqlConnection = New SqlConnection("Data Source=********; Initial Catalog=********; User Id=********; Password=********")
    Dim INVOICES_DA As SqlDataAdapter = New SqlDataAdapter("SELECT TOP 1 * FROM INVOICES", INVOICES_CON)
    Dim INVOICES_DS As DataSet = New DataSet
    Dim INVOICE_LINE_CON As SqlConnection = New SqlConnection("Data Source=********; Initial Catalog=********; User Id=********; Password=********")
    Dim INVOICE_LINE_DA As SqlDataAdapter = New SqlDataAdapter("SELECT TOP 1 * FROM INVOICE_LINE", INVOICE_LINE_CON)
    Dim INVOICE_LINE_DS As DataSet = New DataSet
    Dim WPOT_CON As SqlConnection = New SqlConnection("Data Source=********; Initial Catalog=********; User Id=********; Password=********")
    Dim WPOT_DA As SqlDataAdapter = New SqlDataAdapter("SELECT TOP 1 * FROM WPOT", WPOT_CON)
    Dim WPOT_DS As DataSet = New DataSet
    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        'TODO: This line of code loads data into the 'WediDataSet7.WPOT' table. You can move, or remove it, as needed.
        'Me.WPOTTableAdapter.Fill(Me.WediDataSet7.WPOT)
        'TODO: This line of code loads data into the 'WediDataSet6.SENT_INVS' table. You can move, or remove it, as needed.
        'Me.SENT_INVSTableAdapter.Fill(Me.WediDataSet6.SENT_INVS)
        'TODO: This line of code loads data into the 'WediDataSet5.INVOICES' table. You can move, or remove it, as needed.
        'Me.INVOICESTableAdapter.Fill(Me.WediDataSet5.INVOICES)
        'TODO: This line of code loads data into the 'WediDataSet4.SENT_ACKS' table. You can move, or remove it, as needed.
        'Me.SENT_ACKSTableAdapter.Fill(Me.WediDataSet4.SENT_ACKS)
        'TODO: This line of code loads data into the 'WediDataSet2.TEXTMESSAGE' table. You can move, or remove it, as needed.
        'Me.TEXTMESSAGETableAdapter.Fill(Me.WediDataSet2.TEXTMESSAGE)
        'TODO: This line of code loads data into the 'WediDataSet1.ACKNOWLEDGMENT' table. You can move, or remove it, as needed.
        'Me.ACKNOWLEDGMENTTableAdapter.Fill(Me.WediDataSet1.ACKNOWLEDGMENT)
        'TODO: This line of code loads data into the 'WediDataSet.PO' table. You can move, or remove it, as needed.
            PO_DataGridView.AllowUserToDeleteRows = False
            SentInvoicesDataGridView.AllowUserToDeleteRows = False
            AcknowledgmentsDataGridView.AllowUserToDeleteRows = False
            TextMessagesDataGridView.AllowUserToDeleteRows = False
            SentAcksDataGridView.AllowUserToDeleteRows = False
            SentInvsDataGridView.AllowUserToDeleteRows = False
            WPOT_DataGridView.AllowUserToDeleteRows = False
        POTableAdapter.Fill(Me.WediDataSet.PO)
        PO_DataGridView.AutoResizeColumns()
        SentInvoicesDataGridView.AutoResizeColumns()
        AcknowledgmentsDataGridView.AutoResizeColumns()
        TextMessagesDataGridView.AutoResizeColumns()
        WPOT_DataGridView.AutoResizeColumns()
        as2.RuntimeLicense = "********"
        Dim Certificate1 As nsoftware.IPWorksEDI.Certificate = New nsoftware.IPWorksEDI.Certificate(nsoftware.IPWorksEDI.CertStoreTypes.cstPublicKeyFile, Path.Combine(System.Windows.Forms.Application.StartupPath, "********.cer"), "", "C=********, S=********, L=********, O=********, OU=********, CN=********")
        Dim Certificate2 As nsoftware.IPWorksEDI.Certificate = New nsoftware.IPWorksEDI.Certificate(nsoftware.IPWorksEDI.CertStoreTypes.cstPFXFile, Path.Combine(System.Windows.Forms.Application.StartupPath, "********.pfx"), "********", "CN=********")
        as2.ReceiptSignerCert = Certificate1
        as2.SigningCert = Certificate2
        PO_DataGridView.Sort(PO_DataGridView.Columns("DATERECEIVED"), System.ComponentModel.ListSortDirection.Descending)
        SentInvoicesDataGridView.Sort(SentInvoicesDataGridView.Columns("DATEADDED"), System.ComponentModel.ListSortDirection.Descending)
        AcknowledgmentsDataGridView.Sort(AcknowledgmentsDataGridView.Columns("DATERECEIVEDDataGridViewTextBoxColumn1"), System.ComponentModel.ListSortDirection.Descending)
        TextMessagesDataGridView.Sort(TextMessagesDataGridView.Columns("DATE_RECEIVED"), System.ComponentModel.ListSortDirection.Descending)
        setupAcks()
        setupInvs()
        setupInvoices()
        setupInvoice_line()
        setupWpot()
        myLog.WriteEntry("User " + System.Security.Principal.WindowsIdentity.GetCurrent.Name + " Started ********", System.Diagnostics.EventLogEntryType.Information)
    End Sub
    Private Sub MainTabs_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles MainTabs.Click
        Select Case MainTabs.SelectedTab.Name
            Case "PurchaseOrderTab"
                POTableAdapter.Fill(WediDataSet.PO)
                PO_DataGridView.Refresh()
            Case "AcknowledgementsTab"
                ACKNOWLEDGMENTTableAdapter.Fill(WediDataSet1.ACKNOWLEDGMENT)
                AcknowledgmentsDataGridView.Refresh()
            Case "TextMessagesTab"
                TEXTMESSAGETableAdapter.Fill(WediDataSet2.TEXTMESSAGE)
                TextMessagesDataGridView.Refresh()
            Case "SentAcksTab"
                SENT_ACKSTableAdapter.Fill(WediDataSet4.SENT_ACKS)
                SentAcksDataGridView.Refresh()
            Case "SentInvoicesTab"
                INVOICESTableAdapter.Fill(WediDataSet5.INVOICES)
                SentInvoicesDataGridView.Refresh()
            Case "SentInvsTab"
                SENT_INVSTableAdapter.Fill(WediDataSet6.SENT_INVS)
                SentInvsDataGridView.Refresh()
            Case "WPOT_tab"
                WPOTTableAdapter.Fill(WediDataSet7.WPOT)
                WPOT_DataGridView.Refresh()
        End Select
    End Sub
    Private Sub PO_DataGridView_CellContentClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles PO_DataGridView.CellContentClick
        If e.RowIndex > -1 Then
            If PO_DataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).OwningColumn.Name = "ACKNOWLEDGED_CHECKBOX" Then
                transmission.invoice = False
                If IsDBNull(PO_DataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).Value) Then
                    transmission.row = e.RowIndex
                    If transmission.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
                        PO_DataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).Value = True
                    End If
                ElseIf PO_DataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).Value Then
                    PO_DataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).Value = False
                Else
                    transmission.row = e.RowIndex
                    If transmission.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
                        PO_DataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).Value = True
                    End If
                End If
            ElseIf PO_DataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).OwningColumn.Name = "QUANTUS_CHECKBOX" Then
                If CheckPOTracked(PO_DataGridView.Rows(e.RowIndex).Cells("POTYPE").Value.ToString) Then
                    If IsDBNull(PO_DataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).Value) Then
                        WPOT_Acks.row = e.RowIndex
                        If WPOT_Acks.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
                            PO_DataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).Value = True
                        End If
                    ElseIf PO_DataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).Value Then
                        'WPOT_DelSO(e.RowIndex)
                        PO_DataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).Value = False
                    Else
                        WPOT_Acks.row = e.RowIndex
                        If WPOT_Acks.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
                            PO_DataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).Value = True
                        End If
                    End If
                Else
                    If IsDBNull(PO_DataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).Value) Then
                        PO_DataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).Value = True
                    ElseIf PO_DataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).Value Then
                        PO_DataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).Value = False
                    Else
                        PO_DataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).Value = True
                    End If
                End If
            End If
            POBindingSource.EndEdit()
            POTableAdapter.Update(WediDataSet.PO)
        End If
    End Sub
    Private Sub PO_DataGridView_CellDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles PO_DataGridView.CellDoubleClick
        If e.RowIndex > -1 Then
            If Not PO_DataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).OwningColumn.Name = "ACKNOWLEDGED_CHECKBOX" And Not PO_DataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).OwningColumn.Name = "QUANTUS_CHECKBOX" Then
                Dim tmpApp As New Excel.Application
                Dim tmpWorkbook As Excel.Workbook
                Dim tmpWorksheet As Excel.Worksheet
                tmpWorkbook = tmpApp.Workbooks.Add
                tmpWorksheet = tmpWorkbook.Sheets.Add
                Dim AddressListCON As SqlConnection = New SqlConnection("Data Source=********; Initial Catalog=********; User Id=********; Password=********")
                Dim AddressListDA As SqlDataAdapter = _
                    New SqlDataAdapter("SELECT GLN_ID, GLN, NAME, ADDRESS1, ADDRESS2, CITY, STATE, ZIP, COUNTRY, CONTACT, PHONE, DUNS, STORENUM, DISTRONUM, DATE FROM ADDRESSLIST WHERE GLN = @GLN", AddressListCON)
                Dim AddressListDS As DataSet = New DataSet
                Dim PO_LINE_CON As SqlConnection = New SqlConnection("Data Source=********; Initial Catalog=********; User Id=********; Password=********")
                Dim PO_LINE_DA As SqlDataAdapter = _
                    New SqlDataAdapter("SELECT LINE_ID, MSG_ID, LINE_NUM, QTY, UNITS, UNIT_PRICE, CUSTOMER_ITEM_NUM, VENDOR_ITEM_NUM, DESCRIPTION, " + _
                                       "AMOUNT, SDQ FROM PO_LINE WHERE MSG_ID = @MSG_ID", PO_LINE_CON)
                Dim PO_LINE_DS As DataSet = New DataSet
                Dim SDQ_CON As SqlConnection = New SqlConnection("Data Source=********; Initial Catalog=********; User Id=********; Password=********")
                Dim SDQ_DA As SqlDataAdapter = _
                    New SqlDataAdapter("SELECT LINE_ID, GLN, QTY FROM SDQ WHERE LINE_ID = @LINE_ID", SDQ_CON)
                Dim SDQ_DS As DataSet = New DataSet
                Dim storenum As String
                Dim distronum As String
                Dim division As String
                Dim items_SDQ As List(Of Line_items_SDQ) = New List(Of Line_items_SDQ)
                AddressListDA.SelectCommand.Parameters.Add("@GLN", SqlDbType.VarChar, 80, "GLN")
                PO_LINE_DA.SelectCommand.Parameters.Add("@MSG_ID", SqlDbType.Int, 4, "MSG_ID")
                SDQ_DA.SelectCommand.Parameters.Add("@LINE_ID", SqlDbType.Int, 4, "LINE_ID")
                With tmpWorksheet
                    .Range("a:i").Font.Size = 8
                    .Range("a:i").WrapText = False
                    .Rows.RowHeight = 14
                    .Range("1:2").RowHeight = 26
                    .Range("1:2").HorizontalAlignment = XlHAlign.xlHAlignCenter
                    .Range("1:2").VerticalAlignment = XlVAlign.xlVAlignCenter
                    .Range("1:2").Font.Bold = True
                    .Range("g3").Font.Bold = True
                    .Range("h3").Font.Bold = True
                    .Range("a16").Font.Bold = True
                    .Range("a17").Font.Bold = True
                    .Range("20:20").Font.Bold = True
                    .Range("20:20").HorizontalAlignment = XlHAlign.xlHAlignCenter
                    .Name = "WM PO#" + PO_DataGridView.Rows(e.RowIndex).Cells("PONUM").Value.ToString
                    .Range("a1:i1").Merge()
                    .Range("a1").Value = "******** PURCHASE ORDER"
                    .Range("a2:f2").Merge()
                    .Range("g2:i2").Merge()
                    .Range("a2").Value = "VENDOR INFORMATION"
                    .Range("a2").HorizontalAlignment = XlHAlign.xlHAlignCenter
                    .Range("g2").Value = "ORDER DETAILS"
                    .Range("g1").HorizontalAlignment = XlHAlign.xlHAlignCenter
                    For counter As Integer = 3 To 15
                        .Range("a" & counter & ":c" & counter).Merge()
                        .Range("d" & counter & ":f" & counter).Merge()
                        .Range("h" & counter & ":i" & counter).Merge()
                        .Range("h" & counter).HorizontalAlignment = XlHAlign.xlHAlignLeft
                    Next
                    .Range("a3").Value = PO_DataGridView.Rows(e.RowIndex).Cells("VENDORNAME").Value.ToString
                    .Range("d3").Value = "HOST Vendor # " + PO_DataGridView.Rows(e.RowIndex).Cells("VENDORNUM").Value.ToString
                    .Range("g3").Value = "PO Number: "
                    .Range("h3").Value = PO_DataGridView.Rows(e.RowIndex).Cells("PONUM").Value.ToString
                    .Range("g4").Value = "Order Type: "
                    .Range("h4").Value = PO_DataGridView.Rows(e.RowIndex).Cells("POTYPE").Value.ToString
                    .Range("g5").Value = "Order Date: "
                    .Range("h5").Value = PO_DataGridView.Rows(e.RowIndex).Cells("ORDERDATE").Value
                    .Range("g6").Value = "Arrival Date: "
                    .Range("h6").Value = PO_DataGridView.Rows(e.RowIndex).Cells("ARRIVALDATE").Value
                    .Range("a7").Value = "SHIP MERCHANDISE TO"
                    .Range("d7").Value = "SEND INVOICES TO"
                    .Range("g7").Value = "Ordered By: "
                    .Range("h7").Value = PO_DataGridView.Rows(e.RowIndex).Cells("BUYER").Value.ToString
                    If String.IsNullOrEmpty(PO_DataGridView.Rows(e.RowIndex).Cells("SHIPTOGLN").Value.ToString) Then
                        .Range("a8").Value = PO_DataGridView.Rows(e.RowIndex).Cells("SHIPTONAME").Value.ToString
                        .Range("a9").Value = PO_DataGridView.Rows(e.RowIndex).Cells("SHIPTOADDRESS1").Value.ToString
                        .Range("a10").Value = PO_DataGridView.Rows(e.RowIndex).Cells("SHIPTOADDRESS2").Value.ToString
                        .Range("a11").Value = PO_DataGridView.Rows(e.RowIndex).Cells("SHIPTOCITY").Value.ToString + " " + _
                            PO_DataGridView.Rows(e.RowIndex).Cells("SHIPTOSTATE").Value.ToString + " " + _
                            PO_DataGridView.Rows(e.RowIndex).Cells("SHIPTOZIP").Value.ToString
                        .Range("a12").Value = PO_DataGridView.Rows(e.RowIndex).Cells("SHIPTOCOUNTRY").Value.ToString
                        storenum = Nothing
                        distronum = Nothing
                    Else
                        AddressListDA.SelectCommand.Parameters.Item("@GLN").Value = PO_DataGridView.Rows(e.RowIndex).Cells("SHIPTOGLN").Value.ToString
                        AddressListDA.Fill(AddressListDS, "ADDRESS")
                        .Range("a8").Value = AddressListDS.Tables("ADDRESS").Rows(0).Item("NAME").ToString
                        .Range("a9").Value = AddressListDS.Tables("ADDRESS").Rows(0).Item("ADDRESS1").ToString
                        .Range("a10").Value = AddressListDS.Tables("ADDRESS").Rows(0).Item("ADDRESS2").ToString
                        .Range("a11").Value = AddressListDS.Tables("ADDRESS").Rows(0).Item("CITY").ToString + " " + _
                            AddressListDS.Tables("ADDRESS").Rows(0).Item("STATE").ToString + " " + _
                            AddressListDS.Tables("ADDRESS").Rows(0).Item("ZIP").ToString
                        .Range("a12").Value = AddressListDS.Tables("ADDRESS").Rows(0).Item("COUNTRY").ToString
                        storenum = AddressListDS.Tables("ADDRESS").Rows(0).Item("STORENUM").ToString
                        distronum = AddressListDS.Tables("ADDRESS").Rows(0).Item("DISTRONUM").ToString
                        AddressListDS.Clear()
                    End If
                    If String.IsNullOrEmpty(PO_DataGridView.Rows(e.RowIndex).Cells("BILLTOGLN").Value.ToString) Then
                        .Range("d8").Value = PO_DataGridView.Rows(e.RowIndex).Cells("BILLTONAME").Value.ToString
                    Else
                        AddressListDA.SelectCommand.Parameters.Item("@GLN").Value = PO_DataGridView.Rows(e.RowIndex).Cells("BILLTOGLN").Value.ToString
                        AddressListDA.Fill(AddressListDS, "ADDRESS")
                        .Range("d8").Value = AddressListDS.Tables("ADDRESS").Rows(0).Item("NAME").ToString
                        .Range("d9").Value = AddressListDS.Tables("ADDRESS").Rows(0).Item("ADDRESS1").ToString
                        .Range("d10").Value = AddressListDS.Tables("ADDRESS").Rows(0).Item("ADDRESS2").ToString
                        .Range("d11").Value = AddressListDS.Tables("ADDRESS").Rows(0).Item("CITY").ToString + " " + _
                            AddressListDS.Tables("ADDRESS").Rows(0).Item("STATE").ToString + " " + _
                            AddressListDS.Tables("ADDRESS").Rows(0).Item("ZIP").ToString
                        .Range("d12").Value = AddressListDS.Tables("ADDRESS").Rows(0).Item("COUNTRY").ToString
                        AddressListDS.Clear()
                    End If
                    If Not String.IsNullOrEmpty(PO_DataGridView.Rows(e.RowIndex).Cells("FINALDELIVERYLOCATION").Value.ToString) Then
                        .Range("a14").Value = "Final Destination:"
                        .Range("a15").Value = PO_DataGridView.Rows(e.RowIndex).Cells("FINALDELIVERYLOCATION").Value.ToString
                    End If
                    .Range("g8").Value = "Phone #: "
                    .Range("h8").Value = PO_DataGridView.Rows(e.RowIndex).Cells("BUYERPHONE").Value.ToString
                    .Range("g9").Value = "Revised By: "
                    .Range("h9").Value = PO_DataGridView.Rows(e.RowIndex).Cells("REVISEDBY").Value.ToString
                    .Range("g10").Value = "Phone #: "
                    .Range("h10").Value = PO_DataGridView.Rows(e.RowIndex).Cells("REVISEDPHONE").Value.ToString
                    .Range("g11").Value = "Terms:"
                    .Range("h11").Value = PO_DataGridView.Rows(e.RowIndex).Cells("TERMS").Value.ToString
                    .Range("g12").Value = "Currency:"
                    .Range("h12").Value = PO_DataGridView.Rows(e.RowIndex).Cells("CURRENCY").Value.ToString
                    .Range("a16:i16").Merge()
                    .Range("a16").RowHeight = 26
                    .Range("a16").HorizontalAlignment = XlHAlign.xlHAlignCenter
                    .Range("a16").VerticalAlignment = XlVAlign.xlVAlignCenter
                    .Range("a16").Value = "FACILITY INFORMATION"
                    .Range("a17:c17").Merge()
                    .Range("d17:f17").Merge()
                    .Range("g17:i17").Merge()
                    .Range("a17").Value = "Facility #: "
                    If Not String.IsNullOrEmpty(storenum) Then
                        .Range("a17").Value = .Range("a17").Value + storenum.TrimStart("0")
                    End If
                    If Not String.IsNullOrEmpty(distronum) Then
                        .Range("a17").Value = .Range("a17").Value + distronum.TrimStart("0")
                    End If
                    .Range("d17").Value = "Manager: " + PO_DataGridView.Rows(e.RowIndex).Cells("FACILITYCONTACT").Value.ToString
                    .Range("g17").Value = "Phone #: " + PO_DataGridView.Rows(e.RowIndex).Cells("FACILITYPHONE").Value.ToString
                    .Range("a18:i18").Merge()
                    .Range("a18").RowHeight = 26
                    .Range("a18").VerticalAlignment = XlVAlign.xlVAlignCenter
                    .Range("a18").Value = PO_DataGridView.Rows(e.RowIndex).Cells("NOTESLABEL").Value.ToString
                    Dim count As Integer = 19
                    .Range("a" & count & ":i" & count).Merge()
                    .Range("a" & count).Value = PO_DataGridView.Rows(e.RowIndex).Cells("NOTES").Value.ToString.Replace(vbCrLf, vbLf)
                    Dim tmplist As List(Of String) = PO_DataGridView.Rows(e.RowIndex).Cells("NOTES").Value.ToString.Split(vbLf).ToList
                    .Range("a" & count).RowHeight = 51 '12.75 * tmplist.Count
                    count = count + 1
                    .Range("d" & count & ":f" & count).Merge()
                    .Range("a" & count).Value = "DIV"
                    .Range("b" & count).Value = "LINE ID"
                    .Range("c" & count).Value = "PART NUMBER"
                    .Range("d" & count).Value = "DESCRIPTION"
                    .Range("g" & count).Value = "QUANTITY"
                    .Range("h" & count).Value = "UNIT PRICE"
                    .Range("i" & count).Value = "EXTENDED PRICE"
                    division = PO_DataGridView.Rows(e.RowIndex).Cells("DIVISION").Value.ToString
                    PO_LINE_DA.SelectCommand.Parameters.Item("@MSG_ID").Value = PO_DataGridView.Rows(e.RowIndex).Cells("MSGID").Value.ToString
                    PO_LINE_DA.Fill(PO_LINE_DS, "PO_LINE")
                    For Each dr As DataRow In PO_LINE_DS.Tables("PO_LINE").Rows
                        count = count + 2
                        .Range("a" & count).HorizontalAlignment = XlHAlign.xlHAlignCenter
                        .Range("a" & count).Value = division
                        .Range("b" & count).HorizontalAlignment = XlHAlign.xlHAlignCenter
                        .Range("b" & count).Value = dr.Item("LINE_NUM").ToString
                        .Range("c" & count).Value = dr.Item("CUSTOMER_ITEM_NUM").ToString
                        .Range("g" & count).HorizontalAlignment = XlHAlign.xlHAlignRight
                        .Range("g" & count).Value = dr.Item("QTY").ToString + " " + dr.Item("UNITS").ToString
                        .Range("h" & count).NumberFormat = "$#,##0.00"
                        .Range("h" & count).Value = dr.Item("UNIT_PRICE").ToString
                        .Range("i" & count).NumberFormat = "$#,##0.00"
                        .Range("i" & count).Value = dr.Item("AMOUNT").ToString
                        count = count + 1
                        .Range("d" & count & ":h" & count).Merge()
                        .Range("d" & count).Value = dr.Item("DESCRIPTION").ToString
                        count = count + 1
                        .Range("c" & count & ":f" & count).Merge()
                        .Range("c" & count).Value = "Supplier Item Number: " + dr.Item("VENDOR_ITEM_NUM").ToString
                        If Not String.IsNullOrEmpty(dr.Item("SDQ").ToString) Then
                            Dim tmpLine_items_SDQ As Line_items_SDQ = New Line_items_SDQ
                            tmpLine_items_SDQ.partnum = dr.Item("CUSTOMER_ITEM_NUM").ToString
                            SDQ_DA.SelectCommand.Parameters.Item("@LINE_ID").Value = dr.Item("LINE_ID").ToString
                            SDQ_DA.Fill(SDQ_DS, "SDQ")
                            For Each tmpdr As DataRow In SDQ_DS.Tables("SDQ").Rows
                                Dim tmpSDQ As SDQ = New SDQ
                                storenum = Nothing
                                distronum = Nothing
                                AddressListDA.SelectCommand.Parameters.Item("@GLN").Value = tmpdr.Item("GLN").ToString
                                AddressListDA.Fill(AddressListDS, "ADDRESS")
                                storenum = AddressListDS.Tables("ADDRESS").Rows(0).Item("STORENUM").ToString
                                distronum = AddressListDS.Tables("ADDRESS").Rows(0).Item("DISTRONUM").ToString
                                If String.IsNullOrEmpty(storenum) Then
                                    tmpSDQ.storenum = distronum
                                Else
                                    tmpSDQ.storenum = storenum
                                End If
                                tmpSDQ.quantity = tmpdr.Item("QTY").ToString
                                tmpLine_items_SDQ.part_sdq.Add(tmpSDQ)
                                AddressListDS.Clear()
                            Next
                            items_SDQ.Add(tmpLine_items_SDQ)
                            SDQ_DS.Clear()
                        End If
                    Next
                    count = count + 1
                    .Range("c" & count & ":d" & count).Merge()
                    .Range("e" & count & ":f" & count).Merge()
                    .Range("h" & count).HorizontalAlignment = XlHAlign.xlHAlignRight
                    .Range("h" & count).Value = "TOTAL"
                    .Range("i" & count).NumberFormat = "$#,##0.00"
                    .Range("i" & count).Value = PO_DataGridView.Rows(e.RowIndex).Cells("AMOUNT").Value.ToString
                    count = count + 1
                    If items_SDQ.Count > 0 Then
                        .Range("a" & count & ":i" & count).Merge()
                        .Range("a" & count).RowHeight = 26
                        .Range("a" & count).HorizontalAlignment = XlHAlign.xlHAlignCenter
                        .Range("a" & count).VerticalAlignment = XlVAlign.xlVAlignCenter
                        .Range("a" & count).Font.Bold = True
                        .Range("a" & count).Value = "SDQ List"
                        For Each tmpItemListing As Line_items_SDQ In items_SDQ
                            count = count + 1
                            .Range("a" & count).RowHeight = 26
                            .Range(count & ":" & count).Font.Bold = True
                            .Range(count & ":" & count).VerticalAlignment = XlVAlign.xlVAlignCenter
                            .Range("a" & count & ":c" & count).Merge()
                            .Range("d" & count & ":f" & count).Merge()
                            .Range("a" & count).Value = "Item Number"
                            .Range("d" & count).HorizontalAlignment = XlHAlign.xlHAlignLeft
                            .Range("d" & count).Value = tmpItemListing.partnum
                            count = count + 1
                            .Range("a" & count).RowHeight = 26
                            .Range(count & ":" & count).Font.Bold = True
                            .Range(count & ":" & count).VerticalAlignment = XlVAlign.xlVAlignCenter
                            .Range(count & ":" & count).HorizontalAlignment = XlHAlign.xlHAlignCenter
                            .Range("a" & count & ":c" & count).Merge()
                            .Range("d" & count & ":f" & count).Merge()
                            .Range("a" & count).Value = "Store No."
                            .Range("d" & count).Value = "Quantity."
                            For Each tmpItemListingSDQ As SDQ In tmpItemListing.part_sdq
                                count = count + 1
                                .Range(count & ":" & count).HorizontalAlignment = XlHAlign.xlHAlignCenter
                                .Range("a" & count & ":c" & count).Merge()
                                .Range("d" & count & ":f" & count).Merge()
                                .Range("a" & count).Value = tmpItemListingSDQ.storenum
                                .Range("d" & count).Value = tmpItemListingSDQ.quantity
                            Next
                        Next
                    End If
                    .Range("A2:I2").Select()
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.Constants.xlNone
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.Constants.xlNone
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeLeft)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeTop)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeBottom)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeRight)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideVertical)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    .Range("A3:C15").Select()
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.Constants.xlNone
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.Constants.xlNone
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeLeft)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeTop)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeBottom)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeRight)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.Constants.xlNone
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideHorizontal).LineStyle = Excel.Constants.xlNone
                    .Range("D3:F15").Select()
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.Constants.xlNone
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.Constants.xlNone
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeLeft)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeTop)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeBottom)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeRight)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.Constants.xlNone
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideHorizontal).LineStyle = Excel.Constants.xlNone
                    .Range("G3:I6").Select()
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.Constants.xlNone
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.Constants.xlNone
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeLeft)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeTop)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeBottom)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeRight)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.Constants.xlNone
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideHorizontal).LineStyle = Excel.Constants.xlNone
                    .Range("G7:I10").Select()
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.Constants.xlNone
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.Constants.xlNone
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeLeft)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeTop)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeBottom)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeRight)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.Constants.xlNone
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideHorizontal).LineStyle = Excel.Constants.xlNone
                    .Range("G11:I15").Select()
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.Constants.xlNone
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.Constants.xlNone
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeLeft)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeTop)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeBottom)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeRight)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.Constants.xlNone
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideHorizontal).LineStyle = Excel.Constants.xlNone
                    .Range("A16:I16").Select()
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.Constants.xlNone
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.Constants.xlNone
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeLeft)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeTop)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeBottom)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeRight)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.Constants.xlNone
                    .Range("A17:I17").Select()
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.Constants.xlNone
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.Constants.xlNone
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeLeft)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeTop)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeBottom)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeRight)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.Constants.xlNone
                    .Range("A18:I19").Select()
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.Constants.xlNone
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.Constants.xlNone
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeLeft)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeTop)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeBottom)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeRight)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.Constants.xlNone
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideHorizontal).LineStyle = Excel.Constants.xlNone
                    .Range("A20:I20").Select()
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.Constants.xlNone
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.Constants.xlNone
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeLeft)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeTop)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeBottom)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeRight)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideVertical)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    .Range("A7:F7").Select()
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.Constants.xlNone
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.Constants.xlNone
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeLeft)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeTop)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeBottom)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeRight)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideVertical)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    .Range("a1").Select()
                    .Range("a:a").ColumnWidth = 4.71
                    .Range("b:b").ColumnWidth = 7
                    .Range("c:c").ColumnWidth = 20.16
                    .Range("d:f").ColumnWidth = 11.29
                    .Range("g:g").ColumnWidth = 9.71
                    .Range("h:h").ColumnWidth = 9.86
                    .Range("i:i").ColumnWidth = 14.14
                    .PageSetup.LeftMargin = 9.0
                    .PageSetup.RightMargin = 9.0
                    .PageSetup.TopMargin = 9.0
                    .PageSetup.BottomMargin = 9.0
                    .PageSetup.HeaderMargin = 9.0
                    .PageSetup.FooterMargin = 9.0
                End With
                tmpApp.Visible = True
            End If
        End If
    End Sub
    Private Sub PO_DataGridView_UserDeletedRow(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowEventArgs) Handles PO_DataGridView.UserDeletedRow
        POTableAdapter.Update(WediDataSet)
    End Sub
    Private Sub AcknowledgmentsDataGridView_CellDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles AcknowledgmentsDataGridView.CellDoubleClick
        If e.RowIndex > -1 Then
            Acknowledgement.TextBox1.Text = AcknowledgmentsDataGridView.Rows(e.RowIndex).Cells("DataGridViewTextBoxColumn2").Value.ToString()
            Acknowledgement.Show()
        End If
    End Sub
    Private Sub AcknowledgmentsDataGridView_UserDeletedRow(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowEventArgs) Handles AcknowledgmentsDataGridView.UserDeletedRow
        ACKNOWLEDGMENTTableAdapter.Update(WediDataSet1)
    End Sub
    Private Sub TextMessagesDataGridView_CellDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles TextMessagesDataGridView.CellDoubleClick
        If e.RowIndex > -1 Then
            TextMessage.TextBox1.Text = TextMessagesDataGridView.Rows(e.RowIndex).Cells("MESSAGE").Value.ToString()
            TextMessage.Show()
        End If
    End Sub
    Private Sub TextMessagesDataGridView_UserDeletedRow(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowEventArgs) Handles TextMessagesDataGridView.UserDeletedRow
        TEXTMESSAGETableAdapter.Update(WediDataSet2)
    End Sub
    Public Function acknowledge(ByVal row As Integer) As Boolean
        Dim transmitted As Boolean = False
        Dim dr As DataRow
        Dim isa As String
        Dim gs As String
        Dim st As String
        Dim ak1 As String
        Dim ak2 As String
        Dim ak5 As String
        Dim ak9 As String
        Dim se As String
        Dim ge As String
        Dim iea As String
        Dim isa_controlnum As String
        Dim gs_controlnum As String
        Dim transString As String
        dr = ACKS_DS.Tables("SENT_ACKS").NewRow
        dr.Item("EDI_DATA") = "Initialized"
        dr.Item("STATUS") = "Failed"
        ACKS_DS.Tables("SENT_ACKS").Rows.Add(dr)
        ACKS_DA.Update(ACKS_DS, "SENT_ACKS")
        isa_controlnum = (dr.Item("MSG_ID") + 997000000).ToString
        gs_controlnum = (dr.Item("MSG_ID") + 850000000).ToString
        isa = "ISA*00*          *00*          *ZZ*********         *08*********     *" + _
            Today.ToString("yyMMdd") + "*" + Date.Now.ToString("HHmm") + "*:**********" + isa_controlnum + "*0*P*>~"
        gs = "GS*FA*******************" + Today.ToString("yyyyMMdd") + "*" + Date.Now.ToString("HHmm") + _
            "*" + gs_controlnum + "*X*********~"
        st = "ST*997*" + String.Format("{0:D4}", dr.Item("MSG_ID")) + "~"
        ak1 = "AK1*PO*" + PO_DataGridView.Rows(row).Cells("GS_CONTROLNUM").Value.ToString + "~"
        ak2 = "AK2*850*" + PO_DataGridView.Rows(row).Cells("ST_CONTROLNUM").Value.ToString + "~"
        ak5 = "AK5*A~"
        ak9 = "AK9*A*1*1*1~"
        se = "SE*6*" + String.Format("{0:D4}", dr.Item("MSG_ID")) + "~"
        ge = "GE*1*" + gs_controlnum + "~"
        iea = "IEA*1*" + isa_controlnum + "~"
        transString = isa + gs + st + ak1 + ak2 + ak5 + ak9 + se + ge + iea
        dr.Item("EDI_DATA") = isa + vbCrLf + gs + vbCrLf + st + vbCrLf + ak1 + vbCrLf + ak2 + vbCrLf + ak5 + vbCrLf + ak9 + vbCrLf + se + vbCrLf + ge + vbCrLf + iea
        as2.EDIData = New EDIData()
        as2.EDIData.EDIType = "application/edi-x12"
        as2.EDIData.Data = transString
        Try
            as2.Post()
            ' If the call to post returns without throwing an exception, then the
            ' component was able to post the data and verify the response. In particular,
            ' if you requested a synchronous MDN, it will automatically be validated,
            ' and an exception will be thrown if there are any problems.
            ' If you requested an asynchronous MDN, you will need to save the values
            ' of MessageId, OriginalContentMIC, and MDNOptions, so they can be looked
            ' up based on the MessageId. Then, when you process the asynchronous MDN,
            ' you will need to load these values into the component to verify the
            ' MDN. See the Async MDN demo or the AS2 Connector application for more
            ' information on how to do this.
            dr.Item("STATUS") = "Success"
            transmitted = True
        Catch ipwee As nsoftware.IPWorksEDI.IPWorksEDIException
            myLog.WriteEntry("Transmission was unsuccessful: " + ipwee.Message)
        Finally
            If Not as2.MDNReceipt Is Nothing Then
                TextBox5.Text = transString
                TextBox1.Text = as2.MDNReceipt.Headers
                TextBox2.Text = as2.MDNReceipt.Content
                TextBox3.Text = as2.MDNReceipt.Message
                TextBox4.Text = as2.MDNReceipt.MDN
            End If
        End Try
        ACKS_DA.Update(ACKS_DS, "SENT_ACKS")
        ACKS_DS.Tables("SENT_ACKS").Clear()
        ACKS_DS.AcceptChanges()
        Return transmitted
    End Function
    Public Function invoice(ByVal row As Integer) As Boolean
        Dim INVOICELINE_CON As SqlConnection = New SqlConnection("Data Source=********; Initial Catalog=********; User Id=********; Password=********")
        Dim INVOICELINE_DA As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM INVOICE_LINE WHERE MSG_ID = @MSG_ID", INVOICELINE_CON)
        Dim INVOICELINE_DS As DataSet = New DataSet
        INVOICELINE_DA.SelectCommand.Parameters.Add("@MSG_ID", SqlDbType.Int, 4, "MSG_ID")
        Dim transmitted As Boolean = False
        Dim dr As DataRow
        Dim isa As String
        Dim gs As String
        Dim st As String
        Dim big As String
        Dim ref_mr As String
        Dim n1_st As String
        Dim n3 As String
        Dim n4 As String
        Dim n1_su As String
        Dim ref_ia As String
        Dim itd As String
        Dim dtm As String
        Dim it1 As String = ""
        Dim tds As String
        Dim txi As String
        Dim cad As String
        Dim ctt As String
        Dim se As String
        Dim ge As String
        Dim iea As String
        Dim isa_controlnum As String
        Dim gs_controlnum As String
        Dim transString As String
        Dim dtInvoiced As Date
        Dim dtOrdered As Date
        Dim dtShipped As Date
        Dim line_total As Double
        Dim total As Double = 0
        Dim line_count As Integer = 0
        dr = INVS_DS.Tables("SENT_INVS").NewRow
        dr.Item("EDI_DATA") = "Initialized"
        dr.Item("STATUS") = "Failed"
        INVS_DS.Tables("SENT_INVS").Rows.Add(dr)
        INVS_DA.Update(INVS_DS, "SENT_INVS")
        isa_controlnum = (dr.Item("MSG_ID") + 810000000).ToString
        gs_controlnum = (dr.Item("MSG_ID") + 100000000).ToString
        isa = "ISA*00*          *00*          *ZZ*********         *08*********     *" + _
            Today.ToString("yyMMdd") + "*" + Date.Now.ToString("HHmm") + "*:*00501*" + isa_controlnum + "*0*P*>~"
        gs = "GS*IN*******************" + Today.ToString("yyyyMMdd") + "*" + Date.Now.ToString("HHmm") + _
            "*" + gs_controlnum + "*X*********~"
        st = "ST*810*" + String.Format("{0:D4}", dr.Item("MSG_ID")) + "~"
        dtInvoiced = SentInvoicesDataGridView.Rows(row).Cells("INVOICEDATE").Value
        dtOrdered = SentInvoicesDataGridView.Rows(row).Cells("ORDER_DATE").Value
        dtShipped = SentInvoicesDataGridView.Rows(row).Cells("SHIPPED_DATE").Value
        big = "BIG*" + dtInvoiced.ToString("yyyyMMdd") + "*" + SentInvoicesDataGridView.Rows(row).Cells("INVOICE_NUM").Value.ToString + _
                "*" + dtOrdered.ToString("yyyyMMdd") + "*" + SentInvoicesDataGridView.Rows(row).Cells("PO_NUM").Value.ToString + "~"
        If SentInvoicesDataGridView.Rows(row).Cells("PO_TYPE").Value.ToString.Contains("_S") Then
            Dim tmpStr As String = SentInvoicesDataGridView.Rows(row).Cells("PO_TYPE").Value.ToString.Substring(0, SentInvoicesDataGridView.Rows(row).Cells("PO_TYPE").Value.ToString.IndexOf("_S"))
            ref_mr = "REF*MR*" + tmpStr + "~"
        Else
            ref_mr = "REF*MR*" + SentInvoicesDataGridView.Rows(row).Cells("PO_TYPE").Value.ToString + "~"
        End If
        n1_st = "N1*ST*" + SentInvoicesDataGridView.Rows(row).Cells("ST_NAME").Value.ToString
        If String.IsNullOrEmpty(SentInvoicesDataGridView.Rows(row).Cells("SHIP_TO_GLN").Value.ToString) Then
            n1_st = n1_st + "**~"
        Else
            n1_st = n1_st + "*UL*" + SentInvoicesDataGridView.Rows(row).Cells("SHIP_TO_GLN").Value.ToString + "~"
        End If
        n3 = "N3*" + SentInvoicesDataGridView.Rows(row).Cells("ADDRESS1").Value.ToString + "*" + SentInvoicesDataGridView.Rows(row).Cells("ADDRESS2").Value.ToString + "~"
        n4 = "N4*" + SentInvoicesDataGridView.Rows(row).Cells("CITY").Value.ToString + "*" + SentInvoicesDataGridView.Rows(row).Cells("STATE").Value.ToString + _
                "*" + SentInvoicesDataGridView.Rows(row).Cells("ZIP").Value.ToString + "*" + SentInvoicesDataGridView.Rows(row).Cells("COUNTRY").Value.ToString + "~"
        n1_su = "N1*SU*********~"
        ref_ia = "REF*IA*********~"
        If SentInvoicesDataGridView.Rows(row).Cells("DISCRATE").Value > 0 Then
            itd = "ITD*08*3*" + (SentInvoicesDataGridView.Rows(row).Cells("DISCRATE").Value * 100).ToString + "**" + SentInvoicesDataGridView.Rows(row).Cells("DISCDAYS").Value.ToString + _
            "**" + SentInvoicesDataGridView.Rows(row).Cells("NETDAYS").Value.ToString + "*****" + (SentInvoicesDataGridView.Rows(row).Cells("DISCRATE").Value * 100).ToString + "% " + SentInvoicesDataGridView.Rows(row).Cells("DISCDAYS").Value.ToString + "NET " + SentInvoicesDataGridView.Rows(row).Cells("NETDAYS").Value.ToString + "~"
        Else
            itd = "ITD*05******" + SentInvoicesDataGridView.Rows(row).Cells("NETDAYS").Value.ToString + "*****" + "NET " + SentInvoicesDataGridView.Rows(row).Cells("NETDAYS").Value.ToString + "~"
        End If
        dtm = "DTM*011*" + dtShipped.ToString("yyyyMMdd") + "~"
        INVOICELINE_DA.SelectCommand.Parameters.Item("@MSG_ID").Value = SentInvoicesDataGridView.Rows(row).Cells("MSG_ID").Value.ToString()
        INVOICELINE_DA.Fill(INVOICELINE_DS, "INVOICE_LINE")
        For Each tmpdr As DataRow In INVOICELINE_DS.Tables("INVOICE_LINE").Rows
            line_total = 0
            it1 = it1 + "IT1*" + tmpdr.Item("LINE_NUM").ToString() + "*" + tmpdr.Item("QTY").ToString() + "*" + tmpdr.Item("UNITS").ToString() + "*" + _
                    tmpdr.Item("UNIT_PRICE").ToString() + "*LE*IN*" + tmpdr.Item("CUSTOMER_ITEMNUM").ToString() + "*VN*" + _
                    tmpdr.Item("VENDOR_ITEMNUM").ToString() + "~"
            line_total = tmpdr.Item("UNIT_PRICE") * tmpdr.Item("QTY")
            total = total + line_total
            line_count = line_count + 1
        Next
        total = total + SentInvoicesDataGridView.Rows(row).Cells("TAX").Value
        tds = "TDS*" + total.ToString("F").Replace(".", "") + "~"
        txi = "TXI*TX*" + SentInvoicesDataGridView.Rows(row).Cells("TAX").Value.ToString + "~"
        cad = "CAD*" + SentInvoicesDataGridView.Rows(row).Cells("TRANS_TYPE").Value.ToString() + "***" + _
            SentInvoicesDataGridView.Rows(row).Cells("CARRIER").Value.ToString() + "*" + _
            SentInvoicesDataGridView.Rows(row).Cells("CARRIER_DESCRIPTION").Value.ToString + "**" + _
            SentInvoicesDataGridView.Rows(row).Cells("REF_CODE").Value.ToString() + "*" + _
            SentInvoicesDataGridView.Rows(row).Cells("REFNUM").Value.ToString() + "~"
        ctt = "CTT*" + line_count.ToString + "~"
        se = "SE*" + (line_count + 15).ToString + "*" + String.Format("{0:D4}", dr.Item("MSG_ID")) + "~"
        ge = "GE*1*" + gs_controlnum + "~"
        iea = "IEA*1*" + isa_controlnum + "~"
        transString = isa + gs + st + big + ref_mr + n1_st + n3 + n4 + n1_su + ref_ia + itd + dtm + it1 + tds + txi + cad + ctt + se + ge + iea
        dr.Item("EDI_DATA") = isa + vbCrLf + gs + vbCrLf + st + vbCrLf + big + vbCrLf + ref_mr + vbCrLf + n1_st + vbCrLf + n3 + vbCrLf + _
                              n4 + vbCrLf + n1_su + vbCrLf + ref_ia + vbCrLf + itd + vbCrLf + dtm + vbCrLf + it1 + vbCrLf + tds + vbCrLf + _
                              txi + vbCrLf + cad + vbCrLf + ctt + vbCrLf + se + vbCrLf + ge + vbCrLf + iea
        as2.EDIData = New EDIData()
        as2.EDIData.EDIType = "application/edi-x12"
        as2.EDIData.Data = transString
        Try
            as2.Post()
            ' If the call to post returns without throwing an exception, then the
            ' component was able to post the data and verify the response. In particular,
            ' if you requested a synchronous MDN, it will automatically be validated,
            ' and an exception will be thrown if there are any problems.
            ' If you requested an asynchronous MDN, you will need to save the values
            ' of MessageId, OriginalContentMIC, and MDNOptions, so they can be looked
            ' up based on the MessageId. Then, when you process the asynchronous MDN,
            ' you will need to load these values into the component to verify the
            ' MDN. See the Async MDN demo or the AS2 Connector application for more
            ' information on how to do this.
            dr.Item("STATUS") = "Success"
            transmitted = True
        Catch ipwee As nsoftware.IPWorksEDI.IPWorksEDIException
            myLog.WriteEntry("Transmission was unsuccessful: " + ipwee.Message)
        Finally
            If Not as2.MDNReceipt Is Nothing Then
                TextBox5.Text = transString
                TextBox1.Text = as2.MDNReceipt.Headers
                TextBox2.Text = as2.MDNReceipt.Content
                TextBox3.Text = as2.MDNReceipt.Message
                TextBox4.Text = as2.MDNReceipt.MDN
            End If
        End Try
        INVS_DA.Update(INVS_DS, "SENT_INVS")
        INVS_DS.Tables("SENT_INVS").Clear()
        INVS_DS.AcceptChanges()
        Return transmitted
    End Function
    Private Sub setupAcks()
        Dim DAInsertCmd As SqlCommand
        Dim DAUpdateCmd As SqlCommand
        Try
            DAInsertCmd = New SqlCommand("InsertAcksMSG_ID", ACKS_DA.SelectCommand.Connection)
            DAInsertCmd.CommandType = CommandType.StoredProcedure
            DAInsertCmd.Parameters.Add("@MSG_ID", SqlDbType.Int, 4, "MSG_ID")
            DAInsertCmd.Parameters("@MSG_ID").Direction = ParameterDirection.Output
            DAInsertCmd.Parameters.Add("@EDI_DATA", SqlDbType.VarChar, 1000, "EDI_DATA")
            DAInsertCmd.Parameters.Add("@STATUS", SqlDbType.VarChar, 50, "STATUS")
            ACKS_DA.InsertCommand = DAInsertCmd
            DAUpdateCmd = New SqlCommand("UPDATE SENT_ACKS SET EDI_DATA = @EDI_DATA, STATUS = @STATUS WHERE MSG_ID = @MSG_ID", ACKS_DA.SelectCommand.Connection)
            DAUpdateCmd.Parameters.Add("@MSG_ID", SqlDbType.Int, 4, "MSG_ID")
            DAUpdateCmd.Parameters.Add("@EDI_DATA", SqlDbType.VarChar, 1000, "EDI_DATA")
            DAUpdateCmd.Parameters.Add("@STATUS", SqlDbType.VarChar, 50, "STATUS")
            ACKS_DA.UpdateCommand = DAUpdateCmd
            ACKS_DA.Fill(ACKS_DS, "SENT_ACKS")
            ACKS_DS.Tables("SENT_ACKS").Clear()
            ACKS_DS.AcceptChanges()
        Catch ex As Exception
            myLog.WriteEntry("setupAcks failed!" + vbCrLf + ex.ToString, System.Diagnostics.EventLogEntryType.Error)
        End Try
    End Sub
    Private Sub setupInvs()
        Dim DAInsertCmd As SqlCommand
        Dim DAUpdateCmd As SqlCommand
        Try
            DAInsertCmd = New SqlCommand("InsertInvsMSG_ID", INVS_DA.SelectCommand.Connection)
            DAInsertCmd.CommandType = CommandType.StoredProcedure
            DAInsertCmd.Parameters.Add("@MSG_ID", SqlDbType.Int, 4, "MSG_ID")
            DAInsertCmd.Parameters("@MSG_ID").Direction = ParameterDirection.Output
            DAInsertCmd.Parameters.Add("@EDI_DATA", SqlDbType.VarChar, 4096, "EDI_DATA")
            DAInsertCmd.Parameters.Add("@STATUS", SqlDbType.VarChar, 50, "STATUS")
            INVS_DA.InsertCommand = DAInsertCmd
            DAUpdateCmd = New SqlCommand("UPDATE SENT_INVS SET EDI_DATA = @EDI_DATA, STATUS = @STATUS WHERE MSG_ID = @MSG_ID", INVS_DA.SelectCommand.Connection)
            DAUpdateCmd.Parameters.Add("@MSG_ID", SqlDbType.Int, 4, "MSG_ID")
            DAUpdateCmd.Parameters.Add("@EDI_DATA", SqlDbType.VarChar, 4096, "EDI_DATA")
            DAUpdateCmd.Parameters.Add("@STATUS", SqlDbType.VarChar, 50, "STATUS")
            INVS_DA.UpdateCommand = DAUpdateCmd
            INVS_DA.Fill(INVS_DS, "SENT_INVS")
            INVS_DS.Tables("SENT_INVS").Clear()
            INVS_DS.AcceptChanges()
        Catch ex As Exception
            myLog.WriteEntry("setupInvs failed!" + vbCrLf + ex.ToString, System.Diagnostics.EventLogEntryType.Error)
        End Try
    End Sub
    Private Sub SentAcksDataGridView_CellDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles SentAcksDataGridView.CellDoubleClick
        If e.RowIndex > -1 Then
            sent_acks.TextBox1.Text = SentAcksDataGridView.Rows(e.RowIndex).Cells("EDIDATADataGridViewTextBoxColumn").Value.ToString()
            sent_acks.Show()
        End If
    End Sub
    Private Sub SentAcksDataGridView_UserDeletedRow(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowEventArgs) Handles SentAcksDataGridView.UserDeletedRow
        SENT_ACKSTableAdapter.Update(WediDataSet4)
    End Sub
    Private Sub Timer1_Tick(ByVal sender As Object, ByVal e As System.EventArgs) Handles Timer1.Tick
        POTableAdapter.Fill(WediDataSet.PO)
        PO_DataGridView.Refresh()
    End Sub
    Private Sub CheckBox1_CheckStateChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles CheckBox1.CheckStateChanged
        If CheckBox1.Checked Then
            Timer1.Start()
        Else
            Timer1.Stop()
        End If
    End Sub
    Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Select Case MainTabs.SelectedTab.Name
            Case "PurchaseOrderTab"
                POTableAdapter.Fill(WediDataSet.PO)
                PO_DataGridView.Refresh()
            Case "AcknowledgementsTab"
                ACKNOWLEDGMENTTableAdapter.Fill(WediDataSet1.ACKNOWLEDGMENT)
                AcknowledgmentsDataGridView.Refresh()
            Case "TextMessagesTab"
                TEXTMESSAGETableAdapter.Fill(WediDataSet2.TEXTMESSAGE)
                TextMessagesDataGridView.Refresh()
            Case "SentAcksTab"
                SENT_ACKSTableAdapter.Fill(WediDataSet4.SENT_ACKS)
                SentAcksDataGridView.Refresh()
            Case "SentInvoicesTab"
                INVOICESTableAdapter.Fill(WediDataSet5.INVOICES)
                SentInvoicesDataGridView.Refresh()
            Case "SentInvsTab"
                SENT_INVSTableAdapter.Fill(WediDataSet6.SENT_INVS)
                SentInvsDataGridView.Refresh()
            Case "WPOT_tab"
                WPOTTableAdapter.Fill(WediDataSet7.WPOT)
                WPOT_DataGridView.Refresh()
        End Select
    End Sub
    Private Sub PurchaseOrdersToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PurchaseOrdersToolStripMenuItem.Click
        edi_purchase_orders.Show()
    End Sub
    Private Sub PurposeToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PurposeToolStripMenuItem.Click
        purposecodes.Show()
    End Sub
    Private Sub PurchaseOrderTypesToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PurchaseOrderTypesToolStripMenuItem.Click
        purchaseOrderTypes.Show()
    End Sub
    Private Sub ********EDIToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ********EDIToolStripMenuItem.Click
        VEDIHelp.Show()
    End Sub
    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Dim QDB_Con As DB2Connection = New DB2Connection("Database=********;User ID=********;Password=********;Server=********.********.com:50000;Persist Security Info=True")
        Dim mySelectText As String = "SELECT * FROM QUANTUS.EMPLOYEE"
        Dim QDB_DA As DB2DataAdapter = New DB2DataAdapter(mySelectText, QDB_Con)
        Dim QDB_DS As DataSet = New DataSet()
        Dim SelectShippedBLText As String
        Dim dr As DataRow
        Dim msg_id As Integer
        Dim total_tax As Double = 0
        SelectShippedBLText = "SELECT QUANTUS.ORDER_LINE.DTINVOICED, RTRIM(QUANTUS.ORDER_LINE.REFERNUM1) AS INVOICENUM, QUANTUS.ORDER_LINE.LINESEQNO AS LINENUM, QUANTUS.SALES_ORDER.DTORDER, QUANTUS.ORDER_LINE.DTSHIPPED, " + _
            "RTRIM(QUANTUS.SALES_ORDER.CUSTPONUM) AS PONUM, RTRIM(QUANTUS.SALES_ORDER.MISCINFO) AS PO_TYPE, RTRIM(QUANTUS.SALES_ORDER.CARRIER) AS CARRIER, RTRIM(QUANTUS.TAILOR_CODES.DESCRIPTION) AS CARRIER_DESCRIPTION, " + _
            "RTRIM(QUANTUS.CUSTOMER.NAME1) AS NAME, RTRIM(QUANTUS.CUSTOMER.MAILADDRESS1) AS ADDRESS1, RTRIM(QUANTUS.CUSTOMER.MAILADDRESS2) AS ADDRESS2, RTRIM(QUANTUS.CUSTOMER.MAILCITY) AS CITY, " + _
            "RTRIM(QUANTUS.CUSTOMER.MAILPROV) AS STATE, RTRIM(QUANTUS.CUSTOMER.MAILPOSTAL) AS ZIP, RTRIM(QUANTUS.CUSTOMER.MAILCOUNTRY) AS COUNTRY, " + _
            "QUANTUS.SALES_ORDER.DTREQUESTED, QUANTUS.SALES_ORDER.DTPROMISED, " + _
            "QUANTUS.SALES_ORDER.DISCTYPE, QUANTUS.SALES_ORDER.DISCDAYS, QUANTUS.SALES_ORDER.DISCRATE, QUANTUS.SALES_ORDER.DUETYPE, QUANTUS.SALES_ORDER.DUEDAYS, QUANTUS.ORDER_LINE.TAXAMT1, QUANTUS.ORDER_LINE.TAXAMT2, QUANTUS.ORDER_LINE.TAXAMT3, QUANTUS.ORDER_LINE.TAXAMT4, " + _
            "QUANTUS.ORDER_LINE.INVOICEQTY, RTRIM(QUANTUS.ORDER_LINE.ORDERUOFM) AS UNITS, QUANTUS.ORDER_LINE.UNITPRICE, LTRIM(QUANTUS.ORDER_LINE.ALTITEMNUM) AS CUSTITEMNUM, " + _
            "LTRIM(QUANTUS.ORDER_LINE.ITEMNUM) AS ITEMNUM, LTRIM(QUANTUS.SALES_ORDER.ORDERNUM) AS ORDERNUM, LTRIM(QUANTUS.SALES_ORDER.RELEASE) AS RELEASE " + _
            "FROM QUANTUS.SALES_ORDER LEFT OUTER JOIN QUANTUS.TAILOR_CODES ON QUANTUS.SALES_ORDER.CARRIER = QUANTUS.TAILOR_CODES.CODE AND QUANTUS.TAILOR_CODES.TYPE = 'C', QUANTUS.ORDER_LINE, QUANTUS.CUSTOMER " + _
            "WHERE QUANTUS.SALES_ORDER.BRANCH = QUANTUS.ORDER_LINE.BRANCH " + _
            "AND QUANTUS.SALES_ORDER.ORDERNUM = QUANTUS.ORDER_LINE.ORDERNUM " + _
            "AND QUANTUS.SALES_ORDER.RELEASE = QUANTUS.ORDER_LINE.RELEASE " + _
            "AND QUANTUS.SALES_ORDER.SHIPBRANCH = QUANTUS.CUSTOMER.BRANCH " + _
            "AND QUANTUS.SALES_ORDER.SHIPACCT = QUANTUS.CUSTOMER.CUSTOMERACCT " + _
            "AND LTRIM(QUANTUS.ORDER_LINE.BRANCH) = '02' " + _
            "AND LTRIM(QUANTUS.CUSTOMER.BILLINGACCT) = '********' " + _
            "AND QUANTUS.ORDER_LINE.OPPARENTUNIQUE = 0 " + _
            "AND LTRIM(QUANTUS.ORDER_LINE.REFERNUM1) = '" + tbInvoicenum.Text.ToUpper + "' " + _
            "ORDER BY QUANTUS.ORDER_LINE.LINESEQNO ASC"
        QDB_DA.SelectCommand.CommandText = SelectShippedBLText
        If QDB_DA.Fill(QDB_DS, "InvoiceLines") > 0 Then
            If CheckPOTracked(QDB_DS.Tables("InvoiceLines").Rows(0).Item("PO_TYPE").ToString) Then
                WPOT_DA.SelectCommand.CommandText = "SELECT * FROM WPOT WHERE ORDERNUM = '" + QDB_DS.Tables("InvoiceLines").Rows(0).Item("ORDERNUM").ToString + _
                                                    " " + QDB_DS.Tables("InvoiceLines").Rows(0).Item("RELEASE").ToString + "'"
                If WPOT_DA.Fill(WPOT_DS, "WPOT") > 0 Then
                    If ShippingInfo.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
                        For Each tmp2dr As DataRow In WPOT_DS.Tables("WPOT").Rows
                            For i As Integer = 0 To QDB_DS.Tables("InvoiceLines").Rows.Count - 1
                                If tmp2dr.Item("LINE_NUM").ToString = QDB_DS.Tables("InvoiceLines").Rows(i).Item("LINENUM").ToString Then
                                    If ShippingInfo.ComboBox2.Text = "2I" Then
                                        tmp2dr.Item("PRONUM") = ShippingInfo.TextBox1.Text
                                        tmp2dr.Item("WAYBILLNUM") = ""
                                    ElseIf ShippingInfo.ComboBox2.Text = "BM" Then
                                        tmp2dr.Item("WAYBILLNUM") = ShippingInfo.TextBox1.Text
                                        tmp2dr.Item("PRONUM") = ""
                                    ElseIf ShippingInfo.ComboBox2.Text = "WT" Then
                                        tmp2dr.Item("PRONUM") = ShippingInfo.TextBox1.Text
                                        tmp2dr.Item("WAYBILLNUM") = ""
                                    End If
                                    tmp2dr.Item("ALTITEMNUM") = QDB_DS.Tables("InvoiceLines").Rows(i).Item("CUSTITEMNUM").ToString
                                    tmp2dr.Item("ITEMNUM") = QDB_DS.Tables("InvoiceLines").Rows(i).Item("ITEMNUM").ToString
                                    tmp2dr.Item("DTSHIPPED") = QDB_DS.Tables("InvoiceLines").Rows(i).Item("DTSHIPPED").ToString
                                    tmp2dr.Item("DTREQUESTED") = QDB_DS.Tables("InvoiceLines").Rows(i).Item("DTREQUESTED").ToString
                                    tmp2dr.Item("DTPROMISED") = QDB_DS.Tables("InvoiceLines").Rows(i).Item("DTPROMISED").ToString
                                    If QDB_DS.Tables("InvoiceLines").Rows(i).Item("CARRIER").ToString = "CALL" Then
                                        tmp2dr.Item("CARRIER") = "CALL1"
                                    Else
                                        tmp2dr.Item("CARRIER") = QDB_DS.Tables("InvoiceLines").Rows(i).Item("CARRIER").ToString
                                    End If
                                    tmp2dr.Item("SHIPQTY") = QDB_DS.Tables("InvoiceLines").Rows(i).Item("INVOICEQTY").ToString
                                    tmp2dr.Item("STATUS") = 1
                                    tmp2dr.Item("SENT") = 0
                                    tmp2dr.Item("ETA") = ShippingInfo.TextBox3.Text
                                    Exit For
                                End If
                            Next
                        Next
                        WPOT_DA.Update(WPOT_DS, "WPOT")
                        WPOT_DS.Tables("WPOT").Clear()
                        WPOT_DS.AcceptChanges()
                        dr = INVOICES_DS.Tables("INVOICES").NewRow
                        dr.Item("DATE_ADDED") = Date.Now.ToString
                        dr.Item("INVOICE_DATE") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("DTINVOICED")
                        dr.Item("INVOICENUM") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("INVOICENUM").ToString
                        dr.Item("ORDER_DATE") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("DTORDER")
                        dr.Item("SHIPPED_DATE") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("DTSHIPPED")
                        dr.Item("PONUM") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("PONUM").ToString
                        dr.Item("PO_TYPE") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("PO_TYPE").ToString
                        dr.Item("CARRIER") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("CARRIER").ToString
                        dr.Item("CARRIER_DESCRIPTION") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("CARRIER_DESCRIPTION").ToString
                        dr.Item("SHIP_TO_NAME") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("NAME").ToString
                        dr.Item("SHIP_TO_ADDRESS1") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("ADDRESS1").ToString
                        dr.Item("SHIP_TO_ADDRESS2") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("ADDRESS2").ToString
                        dr.Item("SHIP_TO_CITY") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("CITY").ToString
                        dr.Item("SHIP_TO_STATE") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("STATE").ToString
                        dr.Item("SHIP_TO_ZIP") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("ZIP").ToString
                        dr.Item("SHIP_TO_COUNTRY") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("COUNTRY").ToString
                        dr.Item("DISCTYPE") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("DISCTYPE").ToString
                        dr.Item("DISCDAYS") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("DISCDAYS").ToString
                        dr.Item("DISCRATE") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("DISCRATE").ToString
                        dr.Item("NETDAYS") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("DUEDAYS").ToString
                        dr.Item("TERMS") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("DUETYPE").ToString
                        dr.Item("TRANS_TYPE") = ShippingInfo.ComboBox1.Text
                        If ShippingInfo.ComboBox2.Text = "WT" Then
                            dr.Item("REF_CODE") = "BM"
                        Else
                            dr.Item("REF_CODE") = ShippingInfo.ComboBox2.Text
                        End If
                        dr.Item("REFNUM") = ShippingInfo.TextBox1.Text
                        dr.Item("SHIP_TO_GLN") = ShippingInfo.TextBox2.Text
                        For i As Integer = 0 To QDB_DS.Tables("InvoiceLines").Rows.Count - 1
                            total_tax = total_tax + (QDB_DS.Tables("InvoiceLines").Rows(i).Item("TAXAMT1") + _
                                                QDB_DS.Tables("InvoiceLines").Rows(i).Item("TAXAMT2") + _
                                                QDB_DS.Tables("InvoiceLines").Rows(i).Item("TAXAMT3") + _
                                                QDB_DS.Tables("InvoiceLines").Rows(i).Item("TAXAMT4"))
                        Next
                        dr.Item("TAX") = total_tax.ToString
                        INVOICES_DS.Tables("INVOICES").Rows.Add(dr)
                        INVOICES_DA.Update(INVOICES_DS, "INVOICES")
                        msg_id = dr.Item("MSG_ID")
                        For Each tmpdr As DataRow In QDB_DS.Tables("InvoiceLines").Rows
                            dr = INVOICE_LINE_DS.Tables("INVOICE_LINE").NewRow
                            dr.Item("MSG_ID") = msg_id
                            dr.Item("LINE_NUM") = tmpdr.Item("LINENUM")
                            dr.Item("QTY") = tmpdr.Item("INVOICEQTY")
                            dr.Item("UNITS") = "EA"                                                     'tmpdr.Item("UNITS")
                            dr.Item("UNIT_PRICE") = tmpdr.Item("UNITPRICE")
                            dr.Item("CUSTOMER_ITEMNUM") = tmpdr.Item("CUSTITEMNUM")
                            dr.Item("VENDOR_ITEMNUM") = tmpdr.Item("ITEMNUM")
                            INVOICE_LINE_DS.Tables("INVOICE_LINE").Rows.Add(dr)
                        Next
                        INVOICE_LINE_DA.Update(INVOICE_LINE_DS, "INVOICE_LINE")
                        INVOICE_LINE_DS.Tables("INVOICE_LINE").Clear()
                        INVOICE_LINE_DS.AcceptChanges()
                        INVOICES_DS.Tables("INVOICES").Clear()
                        INVOICES_DS.AcceptChanges()
                        INVOICESTableAdapter.Fill(WediDataSet5.INVOICES)
                        SentInvoicesDataGridView.Refresh()
                    End If
                Else
                    MsgBox("******** PO is tracked but not found in PO tracking table." + vbCrLf + vbCrLf + _
                           "Contact your ******** EDI Administrator", MsgBoxStyle.Critical, "******** PO Tracking")
                End If
            Else
                If ShippingInfo.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
                    dr = INVOICES_DS.Tables("INVOICES").NewRow
                    dr.Item("DATE_ADDED") = Date.Now.ToString
                    dr.Item("INVOICE_DATE") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("DTINVOICED")
                    dr.Item("INVOICENUM") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("INVOICENUM").ToString
                    dr.Item("ORDER_DATE") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("DTORDER")
                    dr.Item("SHIPPED_DATE") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("DTSHIPPED")
                    dr.Item("PONUM") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("PONUM").ToString
                    dr.Item("PO_TYPE") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("PO_TYPE").ToString
                    dr.Item("CARRIER") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("CARRIER").ToString
                    dr.Item("CARRIER_DESCRIPTION") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("CARRIER_DESCRIPTION").ToString
                    dr.Item("SHIP_TO_NAME") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("NAME").ToString
                    dr.Item("SHIP_TO_ADDRESS1") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("ADDRESS1").ToString
                    dr.Item("SHIP_TO_ADDRESS2") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("ADDRESS2").ToString
                    dr.Item("SHIP_TO_CITY") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("CITY").ToString
                    dr.Item("SHIP_TO_STATE") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("STATE").ToString
                    dr.Item("SHIP_TO_ZIP") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("ZIP").ToString
                    dr.Item("SHIP_TO_COUNTRY") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("COUNTRY").ToString
                    dr.Item("DISCTYPE") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("DISCTYPE").ToString
                    dr.Item("DISCDAYS") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("DISCDAYS").ToString
                    dr.Item("DISCRATE") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("DISCRATE").ToString
                    dr.Item("NETDAYS") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("DUEDAYS").ToString
                    dr.Item("TERMS") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("DUETYPE").ToString
                    dr.Item("TRANS_TYPE") = ShippingInfo.ComboBox1.Text
                    If ShippingInfo.ComboBox2.Text = "WT" Then
                        dr.Item("REF_CODE") = "BM"
                    Else
                        dr.Item("REF_CODE") = ShippingInfo.ComboBox2.Text
                    End If
                    dr.Item("REFNUM") = ShippingInfo.TextBox1.Text
                    dr.Item("SHIP_TO_GLN") = ShippingInfo.TextBox2.Text
                    For i As Integer = 0 To QDB_DS.Tables("InvoiceLines").Rows.Count - 1
                        total_tax = total_tax + (QDB_DS.Tables("InvoiceLines").Rows(i).Item("TAXAMT1") + _
                                            QDB_DS.Tables("InvoiceLines").Rows(i).Item("TAXAMT2") + _
                                            QDB_DS.Tables("InvoiceLines").Rows(i).Item("TAXAMT3") + _
                                            QDB_DS.Tables("InvoiceLines").Rows(i).Item("TAXAMT4"))
                    Next
                    dr.Item("TAX") = total_tax.ToString
                    INVOICES_DS.Tables("INVOICES").Rows.Add(dr)
                    INVOICES_DA.Update(INVOICES_DS, "INVOICES")
                    msg_id = dr.Item("MSG_ID")
                    For Each tmpdr As DataRow In QDB_DS.Tables("InvoiceLines").Rows
                        dr = INVOICE_LINE_DS.Tables("INVOICE_LINE").NewRow
                        dr.Item("MSG_ID") = msg_id
                        dr.Item("LINE_NUM") = tmpdr.Item("LINENUM")
                        dr.Item("QTY") = tmpdr.Item("INVOICEQTY")
                        dr.Item("UNITS") = "EA"                                             'tmpdr.Item("UNITS")
                        dr.Item("UNIT_PRICE") = tmpdr.Item("UNITPRICE")
                        dr.Item("CUSTOMER_ITEMNUM") = tmpdr.Item("CUSTITEMNUM")
                        dr.Item("VENDOR_ITEMNUM") = tmpdr.Item("ITEMNUM")
                        INVOICE_LINE_DS.Tables("INVOICE_LINE").Rows.Add(dr)
                    Next
                    INVOICE_LINE_DA.Update(INVOICE_LINE_DS, "INVOICE_LINE")
                    INVOICE_LINE_DS.Tables("INVOICE_LINE").Clear()
                    INVOICE_LINE_DS.AcceptChanges()
                    INVOICES_DS.Tables("INVOICES").Clear()
                    INVOICES_DS.AcceptChanges()
                    INVOICESTableAdapter.Fill(WediDataSet5.INVOICES)
                    SentInvoicesDataGridView.Refresh()
            End If
            End If
        Else
            MsgBox("Invoice Error", MsgBoxStyle.Exclamation, "******** EDI")
        End If
    End Sub
    Public Sub setupInvoices()
        Dim DAInsertCmd As SqlCommand
        Dim DAUpdateCmd As SqlCommand
        Try
            DAInsertCmd = New SqlCommand("InsertInvoicesMSG_ID", INVOICES_DA.SelectCommand.Connection)
            DAInsertCmd.CommandType = CommandType.StoredProcedure
            DAInsertCmd.Parameters.Add("@MSG_ID", SqlDbType.Int, 4, "MSG_ID")
            DAInsertCmd.Parameters("@MSG_ID").Direction = ParameterDirection.Output
            DAInsertCmd.Parameters.Add("@DATE_ADDED", SqlDbType.DateTime, 8, "DATE_ADDED")
            DAInsertCmd.Parameters.Add("@DATE_SENT", SqlDbType.DateTime, 8, "DATE_SENT")
            DAInsertCmd.Parameters.Add("@INVOICE_DATE", SqlDbType.Date, 3, "INVOICE_DATE")
            DAInsertCmd.Parameters.Add("@INVOICENUM", SqlDbType.VarChar, 22, "INVOICENUM")
            DAInsertCmd.Parameters.Add("@ORDER_DATE", SqlDbType.Date, 3, "ORDER_DATE")
            DAInsertCmd.Parameters.Add("@SHIPPED_DATE", SqlDbType.Date, 3, "SHIPPED_DATE")
            DAInsertCmd.Parameters.Add("@PONUM", SqlDbType.VarChar, 22, "PONUM")
            DAInsertCmd.Parameters.Add("@PO_TYPE", SqlDbType.VarChar, 50, "PO_TYPE")
            DAInsertCmd.Parameters.Add("@CARRIER", SqlDbType.VarChar, 4, "CARRIER")
            DAInsertCmd.Parameters.Add("@CARRIER_DESCRIPTION", SqlDbType.VarChar, 35, "CARRIER_DESCRIPTION")
            DAInsertCmd.Parameters.Add("@SHIP_TO_NAME", SqlDbType.VarChar, 60, "SHIP_TO_NAME")
            DAInsertCmd.Parameters.Add("@SHIP_TO_ADDRESS1", SqlDbType.VarChar, 55, "SHIP_TO_ADDRESS1")
            DAInsertCmd.Parameters.Add("@SHIP_TO_ADDRESS2", SqlDbType.VarChar, 55, "SHIP_TO_ADDRESS2")
            DAInsertCmd.Parameters.Add("@SHIP_TO_CITY", SqlDbType.VarChar, 30, "SHIP_TO_CITY")
            DAInsertCmd.Parameters.Add("@SHIP_TO_STATE", SqlDbType.VarChar, 2, "SHIP_TO_STATE")
            DAInsertCmd.Parameters.Add("@SHIP_TO_ZIP", SqlDbType.VarChar, 15, "SHIP_TO_ZIP")
            DAInsertCmd.Parameters.Add("@SHIP_TO_COUNTRY", SqlDbType.VarChar, 3, "SHIP_TO_COUNTRY")
            DAInsertCmd.Parameters.Add("@DISCTYPE", SqlDbType.VarChar, 2, "DISCTYPE")
            DAInsertCmd.Parameters.Add("@DISCDAYS", SqlDbType.VarChar, 3, "DISCDAYS")
            DAInsertCmd.Parameters.Add("@DISCRATE", SqlDbType.VarChar, 6, "DISCRATE")
            DAInsertCmd.Parameters.Add("@NETDAYS", SqlDbType.VarChar, 3, "NETDAYS")
            DAInsertCmd.Parameters.Add("@TERMS", SqlDbType.VarChar, 80, "TERMS")
            DAInsertCmd.Parameters.Add("@TAX", SqlDbType.VarChar, 18, "TAX")
            DAInsertCmd.Parameters.Add("@SENT", SqlDbType.Bit, 1, "SENT")
            DAInsertCmd.Parameters.Add("@TRANS_TYPE", SqlDbType.VarChar, 2, "TRANS_TYPE")
            DAInsertCmd.Parameters.Add("@REF_CODE", SqlDbType.VarChar, 3, "REF_CODE")
            DAInsertCmd.Parameters.Add("@REFNUM", SqlDbType.VarChar, 50, "REFNUM")
            DAInsertCmd.Parameters.Add("@SHIP_TO_GLN", SqlDbType.VarChar, 80, "SHIP_TO_GLN")
            INVOICES_DA.InsertCommand = DAInsertCmd
            DAUpdateCmd = New SqlCommand("UPDATE INVOICES SET DATE_ADDED = @DATE_ADDED, DATE_SENT = @DATE_SENT, INVOICE_DATE = @INVOICE_DATE, INVOICENUM = @INVOICENUM, ORDER_DATE = @ORDER_DATE, SHIPPED_DATE = @SHIPPED_DATE, PONUM = @PONUM, PO_TYPE = @PO_TYPE, CARRIER = @CARRIER, " + _
                                         "CARRIER_DESCRIPTION = @CARRIER_DESCRIPTION, SHIP_TO_NAME = @SHIP_TO_NAME, SHIP_TO_ADDRESS1 = @SHIP_TO_ADDRESS1, SHIP_TO_ADDRESS2 = @SHIP_TO_ADDRESS2, SHIP_TO_CITY = @SHIP_TO_CITY, SHIP_TO_STATE = @SHIP_TO_STATE, SHIP_TO_ZIP = @SHIP_TO_ZIP, SHIP_TO_COUNTRY = @SHIP_TO_COUNTRY, " + _
                                         "DISCTYPE = @DISCTYPE, DISCDAYS = @DISCDAYS, DISCRATE = @DISCRATE, NETDAYS = @NETDAYS, TERMS = @TERMS, TAX = @TAX, SENT = @SENT, TRANS_TYPE = @TRANS_TYPE, REF_CODE = @REF_CODE, " + _
                                         "REFNUM = @REFNUM, SHIP_TO_GLN = @SHIP_TO_GLN WHERE MSG_ID = @MSG_ID", INVOICES_DA.SelectCommand.Connection)
            DAUpdateCmd.Parameters.Add("@MSG_ID", SqlDbType.Int, 4, "MSG_ID")
            DAUpdateCmd.Parameters.Add("@DATE_ADDED", SqlDbType.DateTime, 8, "DATE_ADDED")
            DAUpdateCmd.Parameters.Add("@DATE_SENT", SqlDbType.DateTime, 8, "DATE_SENT")
            DAUpdateCmd.Parameters.Add("@INVOICE_DATE", SqlDbType.Date, 3, "INVOICE_DATE")
            DAUpdateCmd.Parameters.Add("@INVOICENUM", SqlDbType.VarChar, 22, "INVOICENUM")
            DAUpdateCmd.Parameters.Add("@ORDER_DATE", SqlDbType.Date, 3, "ORDER_DATE")
            DAUpdateCmd.Parameters.Add("@SHIPPED_DATE", SqlDbType.Date, 3, "SHIPPED_DATE")
            DAUpdateCmd.Parameters.Add("@PONUM", SqlDbType.VarChar, 22, "PONUM")
            DAUpdateCmd.Parameters.Add("@PO_TYPE", SqlDbType.VarChar, 50, "PO_TYPE")
            DAUpdateCmd.Parameters.Add("@CARRIER", SqlDbType.VarChar, 4, "CARRIER")
            DAUpdateCmd.Parameters.Add("@CARRIER_DESCRIPTION", SqlDbType.VarChar, 35, "CARRIER_DESCRIPTION")
            DAUpdateCmd.Parameters.Add("@SHIP_TO_NAME", SqlDbType.VarChar, 60, "SHIP_TO_NAME")
            DAUpdateCmd.Parameters.Add("@SHIP_TO_ADDRESS1", SqlDbType.VarChar, 55, "SHIP_TO_ADDRESS1")
            DAUpdateCmd.Parameters.Add("@SHIP_TO_ADDRESS2", SqlDbType.VarChar, 55, "SHIP_TO_ADDRESS2")
            DAUpdateCmd.Parameters.Add("@SHIP_TO_CITY", SqlDbType.VarChar, 30, "SHIP_TO_CITY")
            DAUpdateCmd.Parameters.Add("@SHIP_TO_STATE", SqlDbType.VarChar, 2, "SHIP_TO_STATE")
            DAUpdateCmd.Parameters.Add("@SHIP_TO_ZIP", SqlDbType.VarChar, 15, "SHIP_TO_ZIP")
            DAUpdateCmd.Parameters.Add("@SHIP_TO_COUNTRY", SqlDbType.VarChar, 3, "SHIP_TO_COUNTRY")
            DAUpdateCmd.Parameters.Add("@DISCTYPE", SqlDbType.VarChar, 2, "DISCTYPE")
            DAUpdateCmd.Parameters.Add("@DISCDAYS", SqlDbType.VarChar, 3, "DISCDAYS")
            DAUpdateCmd.Parameters.Add("@DISCRATE", SqlDbType.VarChar, 6, "DISCRATE")
            DAUpdateCmd.Parameters.Add("@NETDAYS", SqlDbType.VarChar, 3, "NETDAYS")
            DAUpdateCmd.Parameters.Add("@TERMS", SqlDbType.VarChar, 80, "TERMS")
            DAUpdateCmd.Parameters.Add("@TAX", SqlDbType.VarChar, 18, "TAX")
            DAUpdateCmd.Parameters.Add("@SENT", SqlDbType.Bit, 1, "SENT")
            DAUpdateCmd.Parameters.Add("@TRANS_TYPE", SqlDbType.VarChar, 2, "TRANS_TYPE")
            DAUpdateCmd.Parameters.Add("@REF_CODE", SqlDbType.VarChar, 3, "REF_CODE")
            DAUpdateCmd.Parameters.Add("@REFNUM", SqlDbType.VarChar, 50, "REFNUM")
            DAUpdateCmd.Parameters.Add("@SHIP_TO_GLN", SqlDbType.VarChar, 80, "SHIP_TO_GLN")
            INVOICES_DA.UpdateCommand = DAUpdateCmd
            INVOICES_DA.Fill(INVOICES_DS, "INVOICES")
            INVOICES_DS.Tables("INVOICES").Clear()
            INVOICES_DS.AcceptChanges()
        Catch ex As Exception
            myLog.WriteEntry("setupInvoices failed!" + vbCrLf + ex.ToString, System.Diagnostics.EventLogEntryType.Error)
        End Try
    End Sub
    Public Sub setupInvoice_line()
        Dim DAInsertCmd As SqlCommand
        Dim DAUpdateCmd As SqlCommand
        Try
            DAInsertCmd = New SqlCommand("InsertInvoice_lineLINE_ID", INVOICE_LINE_DA.SelectCommand.Connection)
            DAInsertCmd.CommandType = CommandType.StoredProcedure
            DAInsertCmd.Parameters.Add("@LINE_ID", SqlDbType.Int, 4, "LINE_ID")
            DAInsertCmd.Parameters("@LINE_ID").Direction = ParameterDirection.Output
            DAInsertCmd.Parameters.Add("@MSG_ID", SqlDbType.Int, 4, "MSG_ID")
            DAInsertCmd.Parameters.Add("@LINE_NUM", SqlDbType.VarChar, 20, "LINE_NUM")
            DAInsertCmd.Parameters.Add("@QTY", SqlDbType.VarChar, 15, "QTY")
            DAInsertCmd.Parameters.Add("@UNITS", SqlDbType.VarChar, 2, "UNITS")
            DAInsertCmd.Parameters.Add("@UNIT_PRICE", SqlDbType.VarChar, 17, "UNIT_PRICE")
            DAInsertCmd.Parameters.Add("@CUSTOMER_ITEMNUM", SqlDbType.VarChar, 48, "CUSTOMER_ITEMNUM")
            DAInsertCmd.Parameters.Add("@VENDOR_ITEMNUM", SqlDbType.VarChar, 48, "VENDOR_ITEMNUM")
            INVOICE_LINE_DA.InsertCommand = DAInsertCmd
            DAUpdateCmd = New SqlCommand("UPDATE INVOICE_LINE SET MSG_ID = @MSG_ID, LINE_NUM = @LINE_NUM, QTY = @QTY, UNITS = @UNITS, UNIT_PRICE = @UNIT_PRICE, CUSTOMER_ITEMNUM = @CUSTOMER_ITEMNUM, VENDOR_ITEMNUM = @VENDOR_ITEMNUM " + _
                                         "WHERE LINE_ID = @LINE_ID", INVOICE_LINE_DA.SelectCommand.Connection)
            DAUpdateCmd.Parameters.Add("@LINE_ID", SqlDbType.Int, 4, "LINE_ID")
            DAUpdateCmd.Parameters.Add("@MSG_ID", SqlDbType.Int, 4, "MSG_ID")
            DAUpdateCmd.Parameters.Add("@LINE_NUM", SqlDbType.VarChar, 20, "LINE_NUM")
            DAUpdateCmd.Parameters.Add("@QTY", SqlDbType.VarChar, 15, "QTY")
            DAUpdateCmd.Parameters.Add("@UNITS", SqlDbType.VarChar, 2, "UNITS")
            DAUpdateCmd.Parameters.Add("@UNIT_PRICE", SqlDbType.VarChar, 17, "UNIT_PRICE")
            DAUpdateCmd.Parameters.Add("@CUSTOMER_ITEMNUM", SqlDbType.VarChar, 48, "CUSTOMER_ITEMNUM")
            DAUpdateCmd.Parameters.Add("@VENDOR_ITEMNUM", SqlDbType.VarChar, 48, "VENDOR_ITEMNUM")
            INVOICE_LINE_DA.UpdateCommand = DAUpdateCmd
            INVOICE_LINE_DA.Fill(INVOICE_LINE_DS, "INVOICE_LINE")
            INVOICE_LINE_DS.Tables("INVOICE_LINE").Clear()
            INVOICE_LINE_DS.AcceptChanges()
        Catch ex As Exception
            myLog.WriteEntry("setupInvoice_line failed!" + vbCrLf + ex.ToString, System.Diagnostics.EventLogEntryType.Error)
        End Try
    End Sub
    Public Sub setupWpot()
        Dim DAInsertCmd As SqlCommand
        Dim DAUpdateCmd As SqlCommand
        Dim DADeleteCmd As SqlCommand
        Try
            DAInsertCmd = New SqlCommand("InsertWPOT_LINE_ID", WPOT_DA.SelectCommand.Connection)
            DAInsertCmd.CommandType = CommandType.StoredProcedure
            DAInsertCmd.Parameters.Add("@LINE_ID", SqlDbType.Int, 4, "LINE_ID")
            DAInsertCmd.Parameters("@LINE_ID").Direction = ParameterDirection.Output
            DAInsertCmd.Parameters.Add("@DATE_SENT", SqlDbType.DateTime, 8, "DATE_SENT")
            DAInsertCmd.Parameters.Add("@PONUM", SqlDbType.VarChar, 30, "PONUM")
            DAInsertCmd.Parameters.Add("@STORENUM", SqlDbType.VarChar, 10, "STORENUM")
            DAInsertCmd.Parameters.Add("@ORDERNUM", SqlDbType.VarChar, 20, "ORDERNUM")
            DAInsertCmd.Parameters.Add("@DTREQUESTED", SqlDbType.DateTime, 8, "DTREQUESTED")
            DAInsertCmd.Parameters.Add("@DTSHIPPED", SqlDbType.DateTime, 8, "DTSHIPPED")
            DAInsertCmd.Parameters.Add("@DTPROMISED", SqlDbType.DateTime, 8, "DTPROMISED")
            DAInsertCmd.Parameters.Add("@SHIP_TO_CODE", SqlDbType.VarChar, 3, "SHIP_TO_CODE")
            DAInsertCmd.Parameters.Add("@ADDRESS1", SqlDbType.VarChar, 45, "ADDRESS1")
            DAInsertCmd.Parameters.Add("@ADDRESS2", SqlDbType.VarChar, 45, "ADDRESS2")
            DAInsertCmd.Parameters.Add("@CITY", SqlDbType.VarChar, 30, "CITY")
            DAInsertCmd.Parameters.Add("@STATE", SqlDbType.VarChar, 30, "STATE")
            DAInsertCmd.Parameters.Add("@CARRIER", SqlDbType.VarChar, 5, "CARRIER")
            DAInsertCmd.Parameters.Add("@PRONUM", SqlDbType.VarChar, 30, "PRONUM")
            DAInsertCmd.Parameters.Add("@WAYBILLNUM", SqlDbType.VarChar, 30, "WAYBILLNUM")
            DAInsertCmd.Parameters.Add("@ALTITEMNUM", SqlDbType.VarChar, 30, "ALTITEMNUM")
            DAInsertCmd.Parameters.Add("@ITEMNUM", SqlDbType.VarChar, 30, "ITEMNUM")
            DAInsertCmd.Parameters.Add("@ALTITEMDESC", SqlDbType.VarChar, 61, "ALTITEMDESC")
            DAInsertCmd.Parameters.Add("@QTY", SqlDbType.VarChar, 15, "QTY")
            DAInsertCmd.Parameters.Add("@SHIPQTY", SqlDbType.VarChar, 15, "SHIPQTY")
            DAInsertCmd.Parameters.Add("@LINE_NUM", SqlDbType.VarChar, 20, "LINE_NUM")
            DAInsertCmd.Parameters.Add("@PALLETID", SqlDbType.VarChar, 30, "PALLETID")
            DAInsertCmd.Parameters.Add("@STATUS", SqlDbType.Bit, 1, "STATUS")
            DAInsertCmd.Parameters.Add("@SENT", SqlDbType.Bit, 1, "SENT")
            DAInsertCmd.Parameters.Add("@LABELS", SqlDbType.Bit, 1, "LABELS")
            DAInsertCmd.Parameters.Add("@BATCHID", SqlDbType.VarChar, 65, "BATCHID")
            DAInsertCmd.Parameters.Add("@COMPANY", SqlDbType.VarChar, 45, "COMPANY")
            DAInsertCmd.Parameters.Add("@COUNTRY", SqlDbType.VarChar, 4, "COUNTRY")
            DAInsertCmd.Parameters.Add("@ZIP", SqlDbType.VarChar, 12, "ZIP")
            DAInsertCmd.Parameters.Add("@ETA", SqlDbType.VarChar, 4, "ETA")
            WPOT_DA.InsertCommand = DAInsertCmd
            DAUpdateCmd = New SqlCommand("UPDATE WPOT SET DATE_SENT = @DATE_SENT, PONUM = @PONUM, STORENUM = @STORENUM, ORDERNUM = @ORDERNUM, DTREQUESTED = @DTREQUESTED, DTSHIPPED = @DTSHIPPED, DTPROMISED = @DTPROMISED, SHIP_TO_CODE = @SHIP_TO_CODE, ADDRESS1 = @ADDRESS1, " + _
                                         "ADDRESS2 = @ADDRESS2, CITY = @CITY, STATE = @STATE, CARRIER = @CARRIER, PRONUM = @PRONUM, WAYBILLNUM = @WAYBILLNUM, ALTITEMNUM = @ALTITEMNUM, ITEMNUM = @ITEMNUM, ALTITEMDESC = @ALTITEMDESC, " + _
                                         "QTY = @QTY, SHIPQTY = @SHIPQTY, LINE_NUM = @LINE_NUM, PALLETID = @PALLETID, STATUS = @STATUS, SENT = @SENT, LABELS = @LABELS, BATCHID = @BATCHID, COMPANY = @COMPANY, COUNTRY = @COUNTRY, ZIP = @ZIP, ETA = @ETA WHERE LINE_ID = @LINE_ID", WPOT_DA.SelectCommand.Connection)
            DAUpdateCmd.Parameters.Add("@LINE_ID", SqlDbType.Int, 4, "LINE_ID")
            DAUpdateCmd.Parameters("@LINE_ID").SourceVersion = DataRowVersion.Original
            DAUpdateCmd.Parameters.Add("@DATE_SENT", SqlDbType.DateTime, 8, "DATE_SENT")
            DAUpdateCmd.Parameters.Add("@PONUM", SqlDbType.VarChar, 30, "PONUM")
            DAUpdateCmd.Parameters.Add("@STORENUM", SqlDbType.VarChar, 10, "STORENUM")
            DAUpdateCmd.Parameters.Add("@ORDERNUM", SqlDbType.VarChar, 20, "ORDERNUM")
            DAUpdateCmd.Parameters.Add("@DTREQUESTED", SqlDbType.DateTime, 8, "DTREQUESTED")
            DAUpdateCmd.Parameters.Add("@DTSHIPPED", SqlDbType.DateTime, 8, "DTSHIPPED")
            DAUpdateCmd.Parameters.Add("@DTPROMISED", SqlDbType.DateTime, 8, "DTPROMISED")
            DAUpdateCmd.Parameters.Add("@SHIP_TO_CODE", SqlDbType.VarChar, 3, "SHIP_TO_CODE")
            DAUpdateCmd.Parameters.Add("@ADDRESS1", SqlDbType.VarChar, 45, "ADDRESS1")
            DAUpdateCmd.Parameters.Add("@ADDRESS2", SqlDbType.VarChar, 45, "ADDRESS2")
            DAUpdateCmd.Parameters.Add("@CITY", SqlDbType.VarChar, 30, "CITY")
            DAUpdateCmd.Parameters.Add("@STATE", SqlDbType.VarChar, 30, "STATE")
            DAUpdateCmd.Parameters.Add("@CARRIER", SqlDbType.VarChar, 5, "CARRIER")
            DAUpdateCmd.Parameters.Add("@PRONUM", SqlDbType.VarChar, 30, "PRONUM")
            DAUpdateCmd.Parameters.Add("@WAYBILLNUM", SqlDbType.VarChar, 30, "WAYBILLNUM")
            DAUpdateCmd.Parameters.Add("@ALTITEMNUM", SqlDbType.VarChar, 30, "ALTITEMNUM")
            DAUpdateCmd.Parameters.Add("@ITEMNUM", SqlDbType.VarChar, 30, "ITEMNUM")
            DAUpdateCmd.Parameters.Add("@ALTITEMDESC", SqlDbType.VarChar, 61, "ALTITEMDESC")
            DAUpdateCmd.Parameters.Add("@QTY", SqlDbType.VarChar, 15, "QTY")
            DAUpdateCmd.Parameters.Add("@SHIPQTY", SqlDbType.VarChar, 15, "SHIPQTY")
            DAUpdateCmd.Parameters.Add("@LINE_NUM", SqlDbType.VarChar, 20, "LINE_NUM")
            DAUpdateCmd.Parameters.Add("@PALLETID", SqlDbType.VarChar, 30, "PALLETID")
            DAUpdateCmd.Parameters.Add("@STATUS", SqlDbType.Bit, 1, "STATUS")
            DAUpdateCmd.Parameters.Add("@SENT", SqlDbType.Bit, 1, "SENT")
            DAUpdateCmd.Parameters.Add("@LABELS", SqlDbType.Bit, 1, "LABELS")
            DAUpdateCmd.Parameters.Add("@BATCHID", SqlDbType.VarChar, 30, "BATCHID")
            DAUpdateCmd.Parameters.Add("@COMPANY", SqlDbType.VarChar, 45, "COMPANY")
            DAUpdateCmd.Parameters.Add("@COUNTRY", SqlDbType.VarChar, 4, "COUNTRY")
            DAUpdateCmd.Parameters.Add("@ZIP", SqlDbType.VarChar, 12, "ZIP")
            DAUpdateCmd.Parameters.Add("@ETA", SqlDbType.VarChar, 4, "ETA")
            WPOT_DA.UpdateCommand = DAUpdateCmd
            DADeleteCmd = New SqlCommand("DELETE FROM WPOT WHERE LINE_ID = @LINE_ID", WPOT_DA.SelectCommand.Connection)
            DADeleteCmd.Parameters.Add("@LINE_ID", SqlDbType.VarChar, 20, "LINE_ID")
            WPOT_DA.DeleteCommand = DADeleteCmd
            WPOT_DA.Fill(WPOT_DS, "WPOT")
            WPOT_DS.Tables("WPOT").Clear()
            WPOT_DS.AcceptChanges()
        Catch ex As Exception
            myLog.WriteEntry("setupWpot failed!" + vbCrLf + ex.ToString, System.Diagnostics.EventLogEntryType.Error)
        End Try
    End Sub
    Private Sub SentInvoicesDataGridView_CellContentClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles SentInvoicesDataGridView.CellContentClick
        If e.RowIndex > -1 Then
            If SentInvoicesDataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).OwningColumn.Name = "SENTINVOICE_CHECKBOX" Then
                transmission.invoice = True
                If IsDBNull(SentInvoicesDataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).Value) Then
                    transmission.row = e.RowIndex
                    If transmission.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
                        SentInvoicesDataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).Value = True
                        SentInvoicesDataGridView.Rows(e.RowIndex).Cells("DATESENT").Value = Date.Now
                    End If
                ElseIf SentInvoicesDataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).Value Then
                    SentInvoicesDataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).Value = False
                Else
                    transmission.row = e.RowIndex
                    If transmission.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
                        SentInvoicesDataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).Value = True
                        SentInvoicesDataGridView.Rows(e.RowIndex).Cells("DATESENT").Value = Date.Now
                    End If
                End If
                INVOICESBindingSource.EndEdit()
                INVOICESTableAdapter.Update(WediDataSet5.INVOICES)
            End If
        End If
    End Sub
    Private Sub SentInvoicesDataGridView_CellDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles SentInvoicesDataGridView.CellDoubleClick
        If e.RowIndex > -1 Then
            If Not SentInvoicesDataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).OwningColumn.Name = "SENTINVOICE_CHECKBOX" Then
                Dim tmpApp As New Excel.Application
                Dim tmpWorkbook As Excel.Workbook
                Dim tmpWorksheet As Excel.Worksheet
                tmpWorkbook = tmpApp.Workbooks.Add
                tmpWorksheet = tmpWorkbook.Sheets.Add
                Dim INVOICELINE2_CON As SqlConnection = New SqlConnection("Data Source=********; Initial Catalog=********; User Id=********; Password=********")
                Dim INVOICELINE2_DA As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM INVOICE_LINE WHERE MSG_ID=@MSG_ID", INVOICELINE2_CON)
                Dim INVOICELINE2_DS As DataSet = New DataSet
                Dim line_total As Double
                Dim total As Double = 0
                INVOICELINE2_DA.SelectCommand.Parameters.Add("@MSG_ID", SqlDbType.Int, 4, "MSG_ID")
                With tmpWorksheet
                    .Range("a:i").Font.Size = 8
                    .Range("a:i").WrapText = False
                    .Rows.RowHeight = 14
                    .Range("1:2").RowHeight = 26
                    .Range("1:2").HorizontalAlignment = XlHAlign.xlHAlignCenter
                    .Range("1:2").VerticalAlignment = XlVAlign.xlVAlignCenter
                    .Range("1:2").Font.Bold = True
                    .Range("g3").Font.Bold = True
                    .Range("h3").Font.Bold = True
                    .Range("g7").Font.Bold = True
                    .Range("h7").Font.Bold = True
                    .Range("a8").Font.Bold = True
                    .Range("a16").Font.Bold = True
                    .Range("a17").Font.Bold = True
                    .Range("d17").Font.Bold = True
                    .Range("d19").Font.Bold = True
                    .Range("20:20").Font.Bold = True
                    .Range("20:20").HorizontalAlignment = XlHAlign.xlHAlignCenter
                    .Name = "VI INVOICE#" + SentInvoicesDataGridView.Rows(e.RowIndex).Cells("INVOICE_NUM").Value.ToString
                    .Range("a1:i1").Merge()
                    .Range("a1").Value = "******** EDI INVOICE"
                    .Range("a2:f2").Merge()
                    .Range("g2:i2").Merge()
                    .Range("a2").Value = "VENDOR INFORMATION"
                    .Range("a2").HorizontalAlignment = XlHAlign.xlHAlignCenter
                    .Range("g2").Value = "INVOICE DETAILS"
                    .Range("g1").HorizontalAlignment = XlHAlign.xlHAlignCenter
                    For counter As Integer = 3 To 15
                        .Range("a" & counter & ":c" & counter).Merge()
                        .Range("d" & counter & ":f" & counter).Merge()
                        .Range("h" & counter & ":i" & counter).Merge()
                        .Range("h" & counter).HorizontalAlignment = XlHAlign.xlHAlignLeft
                    Next
                    .Range("a3").Value = "********"
                    .Range("d3").Value = "HOST Vendor # ********"
                    .Range("g3").Value = "PO Number: "
                    .Range("h3").Value = SentInvoicesDataGridView.Rows(e.RowIndex).Cells("PO_NUM").Value.ToString
                    .Range("g4").Value = "PO Type: "
                    .Range("h4").Value = SentInvoicesDataGridView.Rows(e.RowIndex).Cells("PO_TYPE").Value.ToString
                    .Range("a7").Value = "SOLD TO"
                    .Range("a8").Value = "********"
                    .Range("d7").Value = "SHIP TO"
                    .Range("g7").Value = "Invoice #: "
                    .Range("h7").Value = SentInvoicesDataGridView.Rows(e.RowIndex).Cells("INVOICE_NUM").Value.ToString
                    .Range("d8").Value = SentInvoicesDataGridView.Rows(e.RowIndex).Cells("ST_NAME").Value.ToString
                    .Range("d9").Value = SentInvoicesDataGridView.Rows(e.RowIndex).Cells("ADDRESS1").Value.ToString
                    .Range("d10").Value = SentInvoicesDataGridView.Rows(e.RowIndex).Cells("ADDRESS2").Value.ToString
                    .Range("d11").Value = SentInvoicesDataGridView.Rows(e.RowIndex).Cells("CITY").Value.ToString + " " + _
                                            SentInvoicesDataGridView.Rows(e.RowIndex).Cells("STATE").Value.ToString + " " + _
                                            SentInvoicesDataGridView.Rows(e.RowIndex).Cells("ZIP").Value.ToString
                    .Range("d12").Value = SentInvoicesDataGridView.Rows(e.RowIndex).Cells("COUNTRY").Value.ToString
                    .Range("g8").Value = "Invoice Date: "
                    .Range("h8").Value = Date.Parse(SentInvoicesDataGridView.Rows(e.RowIndex).Cells("INVOICEDATE").Value.ToString)
                    .Range("g9").Value = "Order Date: "
                    .Range("h9").Value = Date.Parse(SentInvoicesDataGridView.Rows(e.RowIndex).Cells("ORDER_DATE").Value.ToString)
                    .Range("g10").Value = "Shipped Date: "
                    .Range("h10").Value = Date.Parse(SentInvoicesDataGridView.Rows(e.RowIndex).Cells("SHIPPED_DATE").Value.ToString)
                    .Range("g11").Value = "Terms:"
                    If SentInvoicesDataGridView.Rows(e.RowIndex).Cells("DISCRATE").Value > 0 Then
                        .Range("h11").Value = (SentInvoicesDataGridView.Rows(e.RowIndex).Cells("DISCRATE").Value * 100).ToString + "% " + SentInvoicesDataGridView.Rows(e.RowIndex).Cells("DISCDAYS").Value.ToString + ", NET " + SentInvoicesDataGridView.Rows(e.RowIndex).Cells("NETDAYS").Value.ToString
                    End If
                    .Range("a16:i16").Merge()
                    .Range("a16").RowHeight = 26
                    .Range("a16").HorizontalAlignment = XlHAlign.xlHAlignCenter
                    .Range("a16").VerticalAlignment = XlVAlign.xlVAlignCenter
                    .Range("a16").Value = "CARRIER INFORMATION"
                    .Range("a17:c17").Merge()
                    .Range("d17:f17").Merge()
                    .Range("g17:i17").Merge()
                    .Range("a17").Value = "SCAC: " + SentInvoicesDataGridView.Rows(e.RowIndex).Cells("CARRIER").Value.ToString
                    .Range("d17").Value = "DESCRIPTION: " + SentInvoicesDataGridView.Rows(e.RowIndex).Cells("CARRIER_DESCRIPTION").Value.ToString
                    .Range("a18:c18").Merge()
                    .Range("d18:f18").Merge()
                    .Range("g18:i18").Merge()
                    .Range("a19:c19").Merge()
                    .Range("d19:f19").Merge()
                    .Range("g19:i19").Merge()
                    .Range("a18").Value = "Transportation Method: " + SentInvoicesDataGridView.Rows(e.RowIndex).Cells("TRANS_TYPE").Value.ToString()
                    .Range("a19").Value = "Tracking Type: " + SentInvoicesDataGridView.Rows(e.RowIndex).Cells("REF_CODE").Value.ToString()
                    .Range("d19").Value = "Tracking Number: " + SentInvoicesDataGridView.Rows(e.RowIndex).Cells("REFNUM").Value.ToString()
                    Dim count As Integer = 21
                    .Range("d" & count & ":f" & count).Merge()
                    .Range("b" & count).Value = "LINE ID"
                    .Range("c" & count).Value = "PART NUMBER"
                    .Range("d" & count).Value = "CUSTOMER PART NUMBER"
                    .Range("g" & count).Value = "QUANTITY"
                    .Range("h" & count).Value = "UNIT PRICE"
                    .Range("i" & count).Value = "EXTENDED PRICE"
                    INVOICELINE2_DA.SelectCommand.Parameters.Item("@MSG_ID").Value = SentInvoicesDataGridView.Rows(e.RowIndex).Cells("MSG_ID").Value.ToString()
                    INVOICELINE2_DA.Fill(INVOICELINE2_DS, "INVOICE_LINE")
                    For Each dr As DataRow In INVOICELINE2_DS.Tables("INVOICE_LINE").Rows
                        line_total = 0
                        count = count + 2
                        .Range("d" & count & ":f" & count).Merge()
                        .Range("b" & count).HorizontalAlignment = XlHAlign.xlHAlignCenter
                        .Range("b" & count).Value = dr.Item("LINE_NUM").ToString
                        .Range("c" & count).NumberFormat = "@"
                        .Range("c" & count).Value = dr.Item("VENDOR_ITEMNUM").ToString
                        .Range("d" & count).NumberFormat = "@"
                        .Range("d" & count).Value = dr.Item("CUSTOMER_ITEMNUM").ToString
                        .Range("g" & count).HorizontalAlignment = XlHAlign.xlHAlignRight
                        .Range("g" & count).Value = dr.Item("QTY").ToString + " " + dr.Item("UNITS").ToString
                        .Range("h" & count).NumberFormat = "$#,##0.00"
                        .Range("h" & count).Value = dr.Item("UNIT_PRICE").ToString
                        .Range("i" & count).NumberFormat = "$#,##0.00"
                        line_total = dr.Item("UNIT_PRICE") * dr.Item("QTY")
                        .Range("i" & count).Value = line_total
                        total = total + line_total
                    Next
                    count = count + 2
                    .Range("h" & count).Value = "SUB-TOTAL"
                    .Range("i" & count).NumberFormat = "$#,##0.00"
                    .Range("i" & count).Font.Underline = True
                    .Range("i" & count).Value = total
                    count = count + 1
                    .Range("h" & count).Value = "TAX"
                    .Range("i" & count).NumberFormat = "$#,##0.00"
                    .Range("i" & count).Font.Underline = True
                    .Range("i" & count).Value = SentInvoicesDataGridView.Rows(e.RowIndex).Cells("TAX").Value.ToString()
                    total = total + SentInvoicesDataGridView.Rows(e.RowIndex).Cells("TAX").Value
                    count = count + 2
                    .Range("h" & count).Value = "TOTAL"
                    .Range("i" & count).NumberFormat = "$#,##0.00"
                    .Range("i" & count).Value = total
                    .Range("A2:I2").Select()
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.Constants.xlNone
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.Constants.xlNone
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeLeft)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeTop)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeBottom)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeRight)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideVertical)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    .Range("A3:C15").Select()
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.Constants.xlNone
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.Constants.xlNone
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeLeft)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeTop)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeBottom)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeRight)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.Constants.xlNone
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideHorizontal).LineStyle = Excel.Constants.xlNone
                    .Range("D3:F15").Select()
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.Constants.xlNone
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.Constants.xlNone
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeLeft)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeTop)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeBottom)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeRight)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.Constants.xlNone
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideHorizontal).LineStyle = Excel.Constants.xlNone
                    .Range("G3:I6").Select()
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.Constants.xlNone
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.Constants.xlNone
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeLeft)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeTop)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeBottom)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeRight)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.Constants.xlNone
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideHorizontal).LineStyle = Excel.Constants.xlNone
                    .Range("G7:I10").Select()
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.Constants.xlNone
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.Constants.xlNone
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeLeft)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeTop)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeBottom)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeRight)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.Constants.xlNone
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideHorizontal).LineStyle = Excel.Constants.xlNone
                    .Range("G11:I15").Select()
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.Constants.xlNone
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.Constants.xlNone
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeLeft)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeTop)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeBottom)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeRight)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.Constants.xlNone
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideHorizontal).LineStyle = Excel.Constants.xlNone
                    .Range("A16:I16").Select()
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.Constants.xlNone
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.Constants.xlNone
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeLeft)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeTop)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeBottom)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeRight)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.Constants.xlNone
                    .Range("A17:i19").Select()
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.Constants.xlNone
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.Constants.xlNone
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeLeft)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeTop)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeBottom)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeRight)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.Constants.xlNone
                    .Range("A21:I21").Select()
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.Constants.xlNone
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.Constants.xlNone
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeLeft)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeTop)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeBottom)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeRight)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideVertical)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    .Range("A7:F7").Select()
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.Constants.xlNone
                    tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.Constants.xlNone
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeLeft)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeTop)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeBottom)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeRight)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideVertical)
                        .LineStyle = Excel.XlLineStyle.xlContinuous
                        .Weight = Excel.XlBorderWeight.xlMedium
                        .ColorIndex = Excel.Constants.xlAutomatic
                    End With
                    .Range("a1").Select()
                    .Range("a:a").ColumnWidth = 4.71
                    .Range("b:b").ColumnWidth = 7
                    .Range("c:c").ColumnWidth = 20.16
                    .Range("d:f").ColumnWidth = 11.29
                    .Range("g:g").ColumnWidth = 9.71
                    .Range("h:h").ColumnWidth = 9.86
                    .Range("i:i").ColumnWidth = 14.14
                    .PageSetup.LeftMargin = 9.0
                    .PageSetup.RightMargin = 9.0
                    .PageSetup.TopMargin = 9.0
                    .PageSetup.BottomMargin = 9.0
                    .PageSetup.HeaderMargin = 9.0
                    .PageSetup.FooterMargin = 9.0
                End With
                tmpApp.Visible = True
            End If
        End If
    End Sub
    Private Sub SentInvoicesDataGridView_UserDeletedRow(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowEventArgs) Handles SentInvoicesDataGridView.UserDeletedRow
        INVOICESTableAdapter.Update(WediDataSet5)
    End Sub
    Private Sub SentInvsDataGridView_CellDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles SentInvsDataGridView.CellDoubleClick
        If e.RowIndex > -1 Then
            sent_invs.TextBox1.Text = SentInvsDataGridView.Rows(e.RowIndex).Cells("EDIDATADataGridViewTextBoxColumn1").Value.ToString()
            sent_invs.Show()
        End If
    End Sub
    Private Sub SentInvsDataGridView_UserDeletedRow(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowEventArgs) Handles SentInvsDataGridView.UserDeletedRow
        SENT_INVSTableAdapter.Update(WediDataSet6)
    End Sub
    Private Sub ShippingMethodsToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ShippingMethodsToolStripMenuItem.Click
        shippingcodes.Show()
    End Sub
    Private Sub InvoicingToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles InvoicingToolStripMenuItem.Click
        edi_invoicing.Show()
    End Sub
    Private Sub AcknowledgeToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AcknowledgeToolStripMenuItem.Click
        ackcodes.Show()
    End Sub
    Public Function WPOT_AddSO(ByVal SalesOrder As String, ByVal Releasenum As String, ByVal row As Integer) As Boolean
        Dim success As Boolean = False
        Dim QDB_Con As DB2Connection = New DB2Connection("Database=********;User ID=********;Password=********;Server=********.********.com:50000;Persist Security Info=True")
        Dim mySelectText As String = "SELECT * FROM QUANTUS.EMPLOYEE"
        Dim QDB_DA As DB2DataAdapter = New DB2DataAdapter(mySelectText, QDB_Con)
        Dim QDB_DS As DataSet = New DataSet()
        Dim SelectShippedBLText As String
        Dim dr As DataRow
        Dim total_tax As Double = 0
        WPOT_DA.SelectCommand.CommandText = "SELECT * FROM WPOT WHERE ORDERNUM = '" + SalesOrder.ToUpper + " " + Releasenum + "'"
        If WPOT_DA.Fill(WPOT_DS, "WPOT") > 0 Then
            If MsgBox("Sales Order has already been added to ******** PO Tracking table." + _
                      vbCrLf + "Labels will have to be reprinted." + _
                      vbCrLf + vbCrLf + "Delete old Sales Order from table and add the new one?", _
                      MsgBoxStyle.YesNo, "******** PO Tracking") = MsgBoxResult.Yes Then
                For Each tmpdr2 As DataRow In WPOT_DS.Tables("WPOT").Rows
                    tmpdr2.Delete()
                Next
                WPOT_DA.Update(WPOT_DS, "WPOT")
                WPOT_DS.Tables("WPOT").Clear()
                WPOT_DS.AcceptChanges()
            Else
                WPOT_DS.Tables("WPOT").Clear()
                WPOT_DS.AcceptChanges()
                Return False
            End If
        End If
        SelectShippedBLText = "SELECT RTRIM(QUANTUS.SALES_ORDER.CUSTPONUM) AS PONUM, RTRIM(QUANTUS.CUSTOMER.NAME1) AS NAME, " + _
            "LTRIM(QUANTUS.SALES_ORDER.ORDERNUM) AS ORDERNUM, LTRIM(QUANTUS.SALES_ORDER.RELEASE) AS RELEASE, " + _
            "QUANTUS.SALES_ORDER.DTREQUESTED, QUANTUS.SALES_ORDER.DTPROMISED, " + _
            "RTRIM(QUANTUS.CUSTOMER.MAILADDRESS1) AS ADDRESS1, RTRIM(QUANTUS.CUSTOMER.MAILADDRESS2) AS ADDRESS2, RTRIM(QUANTUS.CUSTOMER.MAILCITY) AS CITY, " + _
            "RTRIM(QUANTUS.CUSTOMER.MAILPROV) AS STATE, RTRIM(QUANTUS.CUSTOMER.MAILCOUNTRY) AS COUNTRY, RTRIM(QUANTUS.CUSTOMER.MAILPOSTAL) AS ZIP, " + _
            "RTRIM(QUANTUS.SALES_ORDER.CARRIER) AS CARRIER, " + _
            "LTRIM(QUANTUS.ORDER_LINE.ALTITEMNUM) AS ALTITEMNUM, LTRIM(QUANTUS.ORDER_LINE.ITEMNUM) AS ITEMNUM, REPLACE(RTRIM(QUANTUS.ORDER_LINE.ALTITEMDESC), ',', ' ') AS ALTITEMDESC, " + _
            "QUANTUS.ORDER_LINE.ORDERQTY AS QTY, QUANTUS.ORDER_LINE.SHIPQTY, QUANTUS.ORDER_LINE.LINESEQNO AS LINE_NUM, RTRIM(QUANTUS.SALES_ORDER.MISCINFO) AS PO_TYPE " + _
            "FROM QUANTUS.SALES_ORDER, QUANTUS.ORDER_LINE, QUANTUS.CUSTOMER " + _
            "WHERE QUANTUS.SALES_ORDER.BRANCH = QUANTUS.ORDER_LINE.BRANCH " + _
            "AND QUANTUS.SALES_ORDER.ORDERNUM = QUANTUS.ORDER_LINE.ORDERNUM " + _
            "AND QUANTUS.SALES_ORDER.RELEASE = QUANTUS.ORDER_LINE.RELEASE " + _
            "AND QUANTUS.SALES_ORDER.SHIPBRANCH = QUANTUS.CUSTOMER.BRANCH " + _
            "AND QUANTUS.SALES_ORDER.SHIPACCT = QUANTUS.CUSTOMER.CUSTOMERACCT " + _
            "AND QUANTUS.ORDER_LINE.SOURCE = 'S' " + _
            "AND LTRIM(QUANTUS.ORDER_LINE.BRANCH) = '02' " + _
            "AND LTRIM(QUANTUS.CUSTOMER.BILLINGACCT) = '********' " + _
            "AND QUANTUS.ORDER_LINE.OPPARENTUNIQUE = 0 " + _
            "AND LTRIM(QUANTUS.SALES_ORDER.ORDERNUM) = '" + SalesOrder.ToUpper + "' " + _
            "AND LTRIM(QUANTUS.SALES_ORDER.RELEASE) = '" + Releasenum + "' " + _
            "ORDER BY QUANTUS.ORDER_LINE.LINESEQNO ASC"
        QDB_DA.SelectCommand.CommandText = SelectShippedBLText
        If QDB_DA.Fill(QDB_DS, "OrderLines") > 0 Then
            If PO_DataGridView.Rows(row).Cells("PONUM").Value.ToString.TrimStart("0") = QDB_DS.Tables("OrderLines").Rows(0).Item("PONUM").ToString Then
                If PO_DataGridView.Rows(row).Cells("POTYPE").Value.ToString = QDB_DS.Tables("OrderLines").Rows(0).Item("PO_TYPE").ToString Then
                    For Each tmpdr As DataRow In QDB_DS.Tables("OrderLines").Rows
                        dr = WPOT_DS.Tables("WPOT").NewRow
                        dr.Item("PONUM") = tmpdr.Item("PONUM").ToString
                        dr.Item("STORENUM") = tmpdr.Item("NAME").ToString.Substring(tmpdr.Item("NAME").ToString.Length - 4, 4).TrimStart("0", "#")
                        dr.Item("ORDERNUM") = tmpdr.Item("ORDERNUM").ToString + " " + tmpdr.Item("RELEASE").ToString
                        dr.Item("DTREQUESTED") = tmpdr.Item("DTREQUESTED").ToString
                        If tmpdr.Item("DTPROMISED").ToString = "1/1/9999 12:00:00 AM" Then
                            MsgBox("Date Promised can't be blank.", MsgBoxStyle.Critical, "******** PO Tracking")
                            success = False
                            WPOT_DS.Tables("WPOT").Clear()
                            WPOT_DS.AcceptChanges()
                            Return success
                        End If
                        dr.Item("DTPROMISED") = tmpdr.Item("DTPROMISED").ToString
                        dr.Item("SHIP_TO_CODE") = WPOT_Acks.ComboBox1.Text
                        dr.Item("ADDRESS1") = tmpdr.Item("ADDRESS1").ToString
                        dr.Item("ADDRESS2") = tmpdr.Item("ADDRESS2").ToString
                        dr.Item("CITY") = tmpdr.Item("CITY").ToString
                        dr.Item("STATE") = tmpdr.Item("STATE").ToString
                        If String.IsNullOrEmpty(tmpdr.Item("ALTITEMNUM").ToString) Then
                            MsgBox("Customer Item Number can't be blank.", MsgBoxStyle.Critical, "******** PO Tracking")
                            success = False
                            WPOT_DS.Tables("WPOT").Clear()
                            WPOT_DS.AcceptChanges()
                            Return success
                        End If
                        dr.Item("ALTITEMNUM") = tmpdr.Item("ALTITEMNUM").ToString
                        dr.Item("ITEMNUM") = tmpdr.Item("ITEMNUM").ToString
                        dr.Item("ALTITEMDESC") = tmpdr.Item("ALTITEMDESC").ToString
                        dr.Item("QTY") = tmpdr.Item("QTY").ToString
                        dr.Item("SHIPQTY") = "0"
                        dr.Item("LINE_NUM") = tmpdr.Item("LINE_NUM").ToString
                        dr.Item("STATUS") = 0
                        dr.Item("SENT") = 0
                        dr.Item("LABELS") = 0
                        dr.Item("COMPANY") = tmpdr.Item("NAME").ToString
                        dr.Item("COUNTRY") = tmpdr.Item("COUNTRY").ToString
                        dr.Item("ZIP") = tmpdr.Item("ZIP").ToString
                        WPOT_DS.Tables("WPOT").Rows.Add(dr)
                    Next
                    WPOT_DA.Update(WPOT_DS, "WPOT")
                    WPOT_DS.Tables("WPOT").Clear()
                    WPOT_DS.AcceptChanges()
                    success = True
                Else
                    MsgBox("Sales Order PO Type does not match PO", MsgBoxStyle.Critical, "******** PO Tracking")
                End If
            Else
                MsgBox("Sales Order is for a different Purchase Order", MsgBoxStyle.Critical, "******** PO Tracking")
            End If
        Else
            MsgBox("Can't find Sales Order", MsgBoxStyle.Critical, "******** PO Tracking")
        End If
        Return success
    End Function
    Public Function CheckPOTracked(ByVal PO_Type As String) As Boolean
        Dim tracked As Boolean = False
        If PO_Type = "ZGL0" Or _
                    PO_Type = "ZUS1" Or _
                    PO_Type = "ZUS2" Or _
                    PO_Type = "ZUS4" Or _
                    PO_Type = "ZUS5" Or _
                    PO_Type = "ZUS6" Or _
                    PO_Type = "ZUS8" Then
            tracked = True
        End If
        Return tracked
    End Function
    Private Sub WPOT_DataGridView_UserDeletedRow(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowEventArgs) Handles WPOT_DataGridView.UserDeletedRow
        WPOTTableAdapter.Update(WediDataSet7)
    End Sub
    Private Sub AboutToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AboutToolStripMenuItem.Click
        AboutBox1.ShowDialog()
    End Sub
End Class
Public Class Line_items_SDQ
    Public partnum As String
    Public part_sdq As List(Of SDQ) = New List(Of SDQ)
End Class
Public Class SDQ
    Public storenum As String
    Public quantity As String
End Class


Copyright © 2013 ProTechs-Online.com; All rights reserved.