Visual Studio module code example for EDI AS2 communication.
Imports System.Data.SqlClientImports Rebex.Net
Imports Rebex.Security.Certificates
Module Module1
Public myLog As New EventLog()
Public WPOT_CON As SqlConnection = New SqlConnection("Data Source=*******; Initial Catalog=*******; User Id=*******; Password=*******")
Public WPOT_DA As SqlDataAdapter = New SqlDataAdapter("SELECT TOP 1 * FROM WPOT", WPOT_CON)
Public WPOT_DS As DataSet = New DataSet
Sub Main()
WPOTEventlog()
CreateUploadFile()
End Sub
Sub WPOTEventlog()
If Not EventLog.SourceExists("WPOT") Then
EventLog.CreateEventSource("WPOT", "******* Tracking")
Console.WriteLine("CreatingEventSource")
End If
' Create an EventLog instance and assign its source.
myLog.Source = "WPOT"
' Write an informational entry to the event log.
'myLog.WriteEntry("******* Tracking Eventlog setup.")
End Sub
Sub CreateUploadFile()
Dim BatchID As Long
Dim FilePath As String = "Upload\*******_"
Dim FileName As String
Dim str As String = "Supplier Acct Number,******* PO Number,******* Store Number,Supplier Sales/Work Order Number,Estimated Ship Date,Actual Ship Date,Estimated Date of Arrival,Ship To Name,Ship to Street Address,Ship to City,Ship to State,Carrier SCAC (Standard Carrier Alpha Code) Number,Pro Number or Load Number,Bill of Lading Number,Pallet ID Number,Container Type,Container Number,Quantity in Container,******* Item Part Number,Supplier Item Part Number,Supplier Item Part Number Description,******* Component Part Number,Supplier Component Part Number,Supplier Component Description,Supplier Quantity Ordered,Supplier Quantity Shipped,Status,Ship Condition,Part Type" + vbCrLf
Dim tmpdate As Date
Try
setupWpot()
WPOT_DA.SelectCommand.CommandText = "SELECT * FROM WPOT WHERE SENT = 0"
BatchID = My.Computer.Registry.GetValue("HKEY_LOCAL_MACHINE\SOFTWARE\*******", "Batch ID", Nothing)
BatchID = BatchID + 1
If WPOT_DA.Fill(WPOT_DS, "WPOT") > 0 Then
For Each dr As DataRow In WPOT_DS.Tables("WPOT").Rows
If Not dr.Item("SHIP_TO_CODE").ToString = "ON" Then
str = str + "*******,"
str = str + dr.Item("PONUM").ToString.Replace(",", " ") + ","
str = str + dr.Item("STORENUM").ToString.Replace(",", " ") + ","
str = str + dr.Item("ORDERNUM").ToString + ","
tmpdate = dr.Item("DTREQUESTED")
str = str + tmpdate.ToString("MM/dd/yyyy") + ","
If IsDBNull(dr.Item("DTSHIPPED")) Then
str = str + ","
Else
tmpdate = dr.Item("DTSHIPPED")
str = str + tmpdate.ToString("MM/dd/yyyy") + ","
End If
tmpdate = dr.Item("DTPROMISED")
str = str + tmpdate.ToString("MM/dd/yyyy") + ","
str = str + dr.Item("SHIP_TO_CODE").ToString + ","
str = str + dr.Item("ADDRESS1").ToString.Replace(",", " ") + " " + dr.Item("ADDRESS2").ToString.Replace(",", " ") + ","
str = str + dr.Item("CITY").ToString.Replace(",", " ") + ","
str = str + dr.Item("STATE").ToString.Replace(",", " ") + ","
If IsDBNull(dr.Item("CARRIER")) Then
str = str + ","
Else
str = str + dr.Item("CARRIER").ToString.Replace(",", " ") + ","
End If
If IsDBNull(dr.Item("PRONUM")) Then
str = str + ","
Else
str = str + dr.Item("PRONUM").ToString.Replace(",", " ") + ","
End If
If IsDBNull(dr.Item("WAYBILLNUM")) Then
str = str + ","
Else
str = str + dr.Item("WAYBILLNUM").ToString.Replace(",", " ") + ","
End If
If IsDBNull(dr.Item("PALLETID")) Or dr.Item("SHIPQTY") = 0 Then
str = str + ","
Else
str = str + dr.Item("PALLETID").ToString + ","
End If
'Container Type
str = str + ","
'Container Number
str = str + ","
'Qty in Container
If IsDBNull(dr.Item("PALLETID")) Or dr.Item("SHIPQTY") = 0 Then
str = str + ","
Else
str = str + dr.Item("SHIPQTY").ToString + ","
End If
str = str + dr.Item("ALTITEMNUM").ToString.Replace(",", " ") + ","
If dr.Item("ITEMNUM").ToString.Contains("_CMP") Then
str = str + dr.Item("ITEMNUM").ToString.Substring(0, dr.Item("ITEMNUM").ToString.IndexOf("_CMP")) + ","
Else
str = str + dr.Item("ITEMNUM").ToString + ","
End If
str = str + dr.Item("ALTITEMDESC").ToString.Replace(vbCrLf, " ") + ","
'******* Component Number
str = str + ","
'Supplier Component Number
If dr.Item("ITEMNUM").ToString.Contains("_CMP") Then
str = str + dr.Item("ITEMNUM").ToString + ","
Else
str = str + ","
End If
'Supplier Component Description
If dr.Item("ITEMNUM").ToString.Contains("_CMP") Then
str = str + dr.Item("ALTITEMDESC").ToString.Replace(vbCrLf, " ") + ","
Else
str = str + ","
End If
str = str + dr.Item("QTY").ToString + ","
str = str + dr.Item("SHIPQTY").ToString + ","
If dr.Item("STATUS") Then
str = str + "C,"
Else
str = str + "A,"
End If
'Ship Condition
If IsDBNull(dr.Item("ETA")) Then
str = str + ","
Else
str = str + dr.Item("ETA").ToString + ","
End If
'Part Type
If dr.Item("ITEMNUM").ToString.Contains("_CMP") Then
str = str + "C" + vbCrLf
Else
str = str + "I" + vbCrLf
End If
dr.Item("DATE_SENT") = DateTime.Now
dr.Item("BATCHID") = BatchID.ToString
End If
dr.Item("SENT") = 1
Next
FileName = FilePath + BatchID.ToString("D6") + ".csv"
System.IO.File.WriteAllText(FileName, str)
My.Computer.Registry.SetValue("HKEY_LOCAL_MACHINE\SOFTWARE\*******", "Batch ID", BatchID)
If FTPUploadFile(FileName) Then
WPOT_DA.Update(WPOT_DS, "WPOT")
End If
End If
Catch ex As Exception
myLog.WriteEntry("CreateUploadFile failed!" + vbCrLf + ex.ToString, System.Diagnostics.EventLogEntryType.Error)
End Try
End Sub
Function FTPUploadFile(ByVal fileString As String) As Boolean
Dim FileName As String
Dim FTPServer As String = "*******.*******.com"
Dim ftp As New Ftp()
Dim success As Boolean = False
Dim startpos As Integer
Dim endpos As Integer
Try
startpos = fileString.LastIndexOf("\") + 1
endpos = fileString.Length - startpos
FileName = fileString.Substring(startpos, endpos)
' create Ftp object, connect and log in
ftp.Connect(FTPServer, 990, Nothing, FtpSecurity.Implicit)
ftp.Login("*******.*******.com|*******", "*******")
' set transfer type to binary
ftp.TransferType = FtpTransferType.Ascii
' download file and display number of bytes transferred
Dim bytes As Long = ftp.PutFile(fileString, FileName)
myLog.WriteEntry("Transfered " + bytes.ToString + " bytes.")
' disconnect
ftp.Disconnect()
success = True
Catch ex As Exception
myLog.WriteEntry("FTPUploadFile failed!" + vbCrLf + ex.ToString, System.Diagnostics.EventLogEntryType.Error)
End Try
Return success
End Function
Public Sub setupWpot()
Dim DAUpdateCmd As SqlCommand
Try
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, LINE_NUM = @LINE_NUM, PALLETID = @PALLETID, STATUS = @STATUS, SENT = @SENT, LABELS = @LABELS, BATCHID = @BATCHID, COMPANY = @COMPANY, COUNTRY = @COUNTRY, ZIP = @ZIP 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, 32, "COMPANY")
DAUpdateCmd.Parameters.Add("@COUNTRY", SqlDbType.VarChar, 4, "COUNTRY")
DAUpdateCmd.Parameters.Add("@ZIP", SqlDbType.VarChar, 12, "ZIP")
WPOT_DA.UpdateCommand = DAUpdateCmd
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
End Module