VB.NET Scripting for Calculated Field
K Zahn asked 7 years ago
Hello,
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 & “‘ “
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 & “‘ “
Answers
Patrick Roth Staff answered 7 years ago
K Zahn,
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.
https://msdn.microsoft.com/en-us/library/czx8s9ts(v=vs.110).aspx
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
.Replace
left paren
double quote
single quote (replace what? a single quote)
double quote
comma
double quote
single quote
single quote (and replace with two single quotes)
double quote
right paren
patrick
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.
https://msdn.microsoft.com/en-us/library/czx8s9ts(v=vs.110).aspx
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
.Replace
left paren
double quote
single quote (replace what? a single quote)
double quote
comma
double quote
single quote
single quote (and replace with two single quotes)
double quote
right paren
patrick
K Zahn replied 7 years ago Hi Patrick,
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.
Any ideas?
Patrick Roth Staff replied 7 years ago 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.