COMMUNITY FORUM

Solved this myself.    ENTERING RETURNS IS NOT WORKING IN THIS POSTING, SO THIS MAY NOT APPEAR CORRECTLY.                                                             Restriction on Create Transaction Node – Cambridge is the batch ID; you could also specify a source field.                                                                                         
GlobalMessage="CAMBRIDGE"
 return true                                                                                                          SCRIPT:                        '—————————————————————————————
' Script Name: UpdatePostingDate
' Created On: 01/18/2009
' Author: Chris Hanson
' Purpose: This script updates the batch posting date in SQL Server.
' History:
 '—————————————————————————————
'—————————————————————————————
 ' Requirements: This script should be located in the Tasks -> Map -> After -> Success
 ' section of the SmartConnect map setup. In the case of this template,
' the GlobalMessage global variable is being set to the batch number
' in the restriction when the map runs, then this script uses it to
 ' update the posting date in the SY00500.
'—————————————————————————————
 '—————————————————————————————
 ' Setup: There are five variables to assign in the configuration section. The
 ' first four determine which values are used in the connection string.
 ' Those four variables – the server (data source), database (initial
' catalog), user, and password should be changed to the needed values.
' The last variable, sqlQuery, contains the query message that is going
' to be executed. This needs to be changed to reference an actual table
' and its corresponding columns before the script will run.
 '—————————————————————————————
 '———————————– CONFIGURATION ————————————-
Dim server As String = "SQLINSTANCE"
Dim database As String = "DBNAME"
Dim user As String = "USERID"
Dim password As String = "PASSWORD"
Dim sqlQuery As String = "UPDATE SY00500 SET GLPOSTDT = '" & DateTime.Now.ToString("yyyy-MM-dd 00:00:00.000") & "' where BCHSOURC = 'Sales Entry' and BACHNUMB = '" & GlobalMessage & "'"
 '—————————————————————————————
Try 'Declare the connection string based on the configuration variables
Dim conString As String = "Data Source=" & server & ";Initial Catalog=" & database & ";User=" & user & ";Password=" & password & ";"
'Define the SQL connection and open it
Dim myConn As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(conString)
myConn.Open()
'Declare the SQL command as the query string and execute it
Dim command As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(sqlQuery, myConn)
command.ExecuteNonQuery()
'Close the connection
myConn.Close()
Catch ex As Exception
Messagebox.Show(ex.Message, "UpdatePostingDate Script Error")
Return False
End Try
Return True