VB.NET Scripting for Calculated Field
We use a calculated field to pull back values from a SQL view using a combination of job number, position ID, pay type ID, and temp ID from the source file. We have run into the issue where the temp ID contains an apostrophe as a result of the temp’s name (O’Neal), resulting in the calculated field returning no values. I believe the apostrophe in the temp ID is causing the statement to truncate the combination of job number, position ID, pay type ID and temp ID after the letter O, so it doesn’t find a match when verifying against the SQL view. The combination in the SQL view and from the source file are the same, so I can only think that the apostrophe in the TEMPID is causing issues with the syntax of the select statement, shown below.
Is there any way to adjust the syntax to allow for temp ID’s that include an apostrophe? We are not VB.NET pros, so any assistance would be greatly appreciated.
Sample data from a source file:
JOBNUMBR POSITIONID PAYTYPID TEMPID
JAX_EMR_000000 SUPPORT 110-10 O’NE9999
Select statement from our calculated field in SmartConnect:
Dim sqlQuery As String =“select temp_rate from csi_temp_rate_client_rate where jobnumbr ='” & _JOBNUMBR & “‘ and POSITION_ID ='” & _POSITIONID & “‘ and PAYTYPID ='” & _PAYTYPID & “‘ and TEMPID ='” & _TEMPID & “‘ “
To see the underlying issue, you’d need to run a SQL trace to see the statement generated and then the SQL error. The problem isn’t going to be that it just can’t find the value due to being truncated – the issue is that the single quote causes bad sql and so the statement doesn’t run at all in SQL.
You’d get an “unenclosed single quote near O” I believe.
The solution is to replace any single quotes in a string with two single quotes – “escaping” the value is how it is referred to in SQL & .NET.
Fortunately the code you write in SC is pure VB.NET so any example you find on the internet are valid. The only thing that is interesting about scripting in the replacement of the field values above – but if you think of the field names like _TEMPID as just any normal hard coded string in your code then you’ll be fine.
So if we take the field in question that you have as
….and PAYTYPID ='” & _PAYTYPID & “‘ and TEMPID ='” & _TEMPID & “‘ “
we need to fix the _TEMPID, so we use the Replace() method that all strings have.
So we change it to:
….and PAYTYPID ='” & _PAYTYPID & “‘ and TEMPID ='” & _TEMPID.Replace(“‘”,”””) & “‘ “
so that is horrible to read with the double and single quotes, so saying it across
single quote (replace what? a single quote)
single quote (and replace with two single quotes)
I tried using the syntax exactly as you listed it in your response, and it is throwing an error when I try to validate it. I checked to make sure every character and space matched. It says Error in calculation column: Identifier expected.
‘sql Query’ is not declared. It may be inaccessible due to its protection level.
sqlQuery is your variable, not mine. I don’t even reference it. And actually I didn’t reproduce your entire query string – I just gave the relevant pieces to fix.
you’d want to re-type the entire sqlQuery string again using what you had at the beginning and then adding what I had on the end.
If you would like to submit an answer or comment, please sign in to the eOne portal.