--- title: "Global Script VB.NET Function Examples" slug: "vb-net-global-script-function-examples" updated: 2024-03-28T17:12:20Z published: 2024-03-28T17:12:20Z canonical: "skthelp.syniti.com/vb-net-global-script-function-examples" --- > ## Documentation Index > Fetch the complete documentation index at: https://skthelp.syniti.com/llms.txt > Use this file to discover all available pages before exploring further. # VB.NET Global Script Function Examples The following VB.NET example functions can be defined in a global script, then used in replication scripts and expressions to modify target field values during replication. For detailed steps on how to define a Global Script function, check [Writing a Function to Use in Scripts and Expressions](/v1/docs/writing-a-function-to-use-in-scripts-and-expressions). ## Accessing Values Using a Different Database Connection If your replication needs record values which are accessible only by opening a separate database connection and running a query there, you can write the following script in the Global Script Editor, then call the script as indicated below. The GetQueryValue function retrieves the first returned value from the passed in connection and query. If the parameters are illegal, the query returns an error or the query does not return a value, the function returns System.DBNull.Value. ```plaintext Public Shared Function GetQueryValue(objConnection as Object, strQuery as String) as Object     'If a parameter is null, return a null value  If objConnection Is Nothing OR strQuery="" Then Return System.DBNull.Value  'Declare variables that might be used   Dim cmd As IDbCommand = Nothing    Dim reader As IDataReader = Nothing   Dim objValue as Object = System.DBNull.Value   'Use the Try block to make sure we capture any exceptions that might be generated    Try        'Use a SyncLock to make sure this function is the only user of the connection since the       'source or target connection might be in use. SyncLock(objConnection)  'Create and execute the select to get the specified value  cmd = objConnection.CreateCommand  cmd.CommandText = strQuery               reader = cmd.ExecuteReader                'If we can read from the reader, the record is already there. Get info.  If reader.Read Then objValue = reader.GetValue(0) End SyncLock 'If there are no exceptions so far, return the value reader.Close reader = Nothing cmd.Dispose         Return objValue 'Output exceptions to the log Catch ex As Exception   AddLog("Exception in GetQueryValue: " & ex.ToString,0) Finally   if (not reader is Nothing) Then reader.Close   End If    if (not cmd is Nothing) Then cmd.Dispose   End If       End Try 'Return System.DBNull.Value because an exception has been generated if this point is reached.       Return System.DBNull.Value End Function 'GetQueryValue ``` ## Calling the Script from a Mapping Expression 1. If you are using the function in an existing replication, either stop the Replication Agent or disable the replication. 2. For an existing replication, right-click on the replication and choose **Replication Properties**, then click **Mapping** to display the Mapping Editor. For new replications, perform the following steps when you get to the mapping portion of replication creation. 3. Right-click on the target field that will be using the generated value and choose **Map to Expression**. 4. In the Expression Generator window, expand the User Functions folder and click on (all) to see if your function is listed. If the function is not displayed, check to make sure that you have used the proper Public Shared qualifier for the function. See [Writing a Function to Use in Scripts and Expressions](/v1/docs/writing-a-function-to-use-in-scripts-and-expressions) for more information. 5. Type an expression similar to the following in the Expression Editor, modifying the SELECT statement to retrieve the information you need. ```plaintext  GetQueryValue(DBRS.SourceConnection, "SELECT Capital FROM Capitals WHERE abbrv='" & [ABBRV] & "'") ``` 1. Click **OK** to close the Expression Generator window. 2. Click **OK** to close the Mapping window. 3. Click **OK** to close the Replication Properties window. 4. Either start the Replication Agent or enable the replication to test that your function is working. ## Date Conversion Define this function in the Global Script Editor, then call it by mapping the source field to an expression on the target field. The expression would be DateConvert([DT]), where DT is the name of the source field. The DT field should have the **Use Unmapped** option checked. ```plaintext  Public Shared Function DateConvert(intDate)     Dim cyy     Dim yyyy     Dim mm     Dim dd     cyy = CInt(intDate/10000)     mm = CInt((intDate - cyy * 10000) / 100)     dd = intDate Mod 100     if (cyy < 100)       yyyy = 1900 + cyy     else       yyyy = 2000 + (cyy Mod 100)     End if     DateConvert = DateSerial(yyyy, mm, dd)   End Function ``` ## Julian Date Conversion Define this function in the Global Script Editor, then call it by mapping the source field to an expression on the target field. The expression would be JulianToDate(obj), where obj is the name of the source field. The source field should have the **Use Unmapped** option checked. This function takes a numeric Julian date in the format YYYDDD where YYY is the number of years since 1900 and DDD is the day of the year and returns a VB.Net date value or Null if the original value was null. ```plaintext Public Shared Function JulianToDate(obj as Object) as Object     If obj Is Nothing Then Return Nothing     If Not IsNumeric(obj) Then Return Nothing      Dim intYears as Integer     intYears = Int(obj/1000)            Dim intDays as Integer     intDays = (obj - intYears * 1000) - 1         Dim datDate as Date     datDate = CDate("1/1/" & Str(1900 + intYears)).AddDays(intDays)    Return datDate    End Function ``` ## Combine Date and Time Fields Define this function in the Global Script Editor, then call it by mapping the source field to an expression on the target field. The expression would be CombineDateTime(objDate, objTime), where objDate is the name of the date source field and objTime is the name of the time source field. The source fields should have the **Use Unmapped** option checked. ```plaintext  Public Shared Function CombineDateTime(objDate as Object, objTime as Object) as Object     'If the date object is nothing return a null value    If objDate is Nothing Then Return System.DBNull.Value        'If you want to return a time of midnight for null times, remove this line    If objTime is Nothing Then Return System.DBNull.Value         'If we don't have a legal date return a null value    If Not IsDate(objDate) Then Return System.DBNull.Value     Dim intHours as Integer     intHours = Hour(objTime)     Dim intMinutes as Integer     intMinutes = Minute(objTime)     Dim intSeconds as Integer     intSeconds = Second(objTime)     Dim datOutput as Date     datOutput = DateAdd("h", intHours, objDate)     datOutput = DateAdd("n", intMinutes, datOutput)     datOutput = DateAdd("s", intSeconds, datOutput)     Return datOutput   End Function ``` ## Time Function that Handles Null Values Define this function in the Global Script Editor, then call it by mapping the source field to an expression on the target field. The expression would be SafeTimeValue(objTime), where objTime is the name of the time source field. The source field should have the **Use Unmapped** option checked. This function can be used to replace the built in TimeValue function because it checks for null values. When a record is deleted on SQL Server, only the primary key is replicated so when an attempt is made to modify the time value, the TimeValue routine fails because it has been passed a null value. ```plaintext Public Shared Function SafeTimeValue(objTime as Object) as Object  Dim intHours as Integer  Dim intMinutes as Integer  Dim intSeconds as Integer  'Return a time of midnight for null or illegal times  intHours = 0  intMinutes = 0  intSeconds = 0  If Not objTime is Nothing Then        If IsDate(objTime) Then         intHours = Hour(objTime)         intMinutes = Minute(objTime)         intSeconds = Second(objTime)      End If  End If  Dim datOutput as Date  datOutput = DateAdd("h", intHours, datOutput)  datOutput = DateAdd("n", intMinutes, datOutput)  datOutput = DateAdd("s", intSeconds, datOutput)  Return datOutput End Function ```