Dynamic SQL select query is not working

first of all, your SQL query is not syntactically correct - you miss there (likely) equal sign in where clause.

hi, that was a typo because I try a lot of fixes. I changed the picture like how it is and returning an empty string.

still typo with missing apostrophe?

I'm so sorry. I fixed the typo. Still have the issue.

UPDATE
When the value of column "VeroorzakerSoort" = "Ander persoon" it works. So it looks like the special character letter "ë" is maybe the problem. Is this a know problem with some fix?

Is it maybe something with utf8 encoding?

I do not understand but I believe the checkbox right bellow the query is 'Escape reference values for use in strings'.

could you try to tick it off?

are you able to capture at SQL server side in what form the query comes in?

Can you reproduce the problem with 'Run now'? if so, you could capture what's being sent to the SQL server.

@emha  I check which query is comming in sql by SQL Profiler. This is the query comming in:

select [ID] from dbo.PersonType where [Name] = 'Familie van een cliënt'

It looks great, but when I copy it and try to execute it in SQL Server Management Studio it returns no results. I removed the spaces in 'Familie van een cliënt' and entered the spaces by my own and now it is working. So this is the problem! Do you know how I can fix this? Are there differant spaces? Maybe some unicode problem?

these seems likely to be non-breakable spaces (ASCII 160) instead of regular ones (ASCII 32)

since you take over that string directly from list field it looks it's already stored that way there, I'm not sure whether by purpose or accidentaly....

how do you populate that value to the list field?

what's datattype of the list field? is it multiline text rich text?

you may 'clean' the value before you supply it to SQL statement either with replace() function or with regular expression action.

@emha the SharePoint column is of type "Single line of text". The value is set by Nintex Form by the choice of a collection radio buttons.

so you may try to check that choice configuration first whether its options already do not contain inaproproprite spaces.

but to be on save side, I'd implement cleaning in workflow in any case. so that if someone changes/extends choice control in future it doesn't break the functionality again.

How do I need to replace a "non-breaking space" to a normal space? Something like this?

OLD sql query:

Select ID
From dbo.PersonType
Where Name = '{ItemProperty:VeroorzakerSoort}'

New sql query:

Select ID
From dbo.PersonType
Where Name = 'fn-Replace({ItemProperty:VeroorzakerSoort}, , )'

I'd suggest to do it in a separate action (set variable, build string...), store result to a variable and then reference variable in the SQL.

I guess, the way, you depicted it, nintex will misundersatnd meaning of apostrophes and take fn-replace() literally, not as a function.

the other advantage is that you can then write variable into a log to check what exactly is supplied to the SQL.

I created a new workflow variable. I created a new action to set the value of this variable. In this action I do:

this is logging to the history:

The middle space is a normal space. The first and thirs space is the non-break space. How can I replace the non-break space?

try to insert non breakable space character into formula as ALT+0160