Dynamic SQL select query is not working

[khalidinio]

We are using SharePoint 2013 and Nintex forms and workflow 2013. I have a list with a single text column "veroorzakerSoort". This single text column "veroorzakerSoort" will be filled by a Nintex form. After the form is completed there is a nintex workflow started automatically.

In the workflow I try to do a SQL select query and add as parameter the value of SharePoint column "veroorzakerSoort". I dont get an error, but the result is an empty string.

I think I have this issue because of the trema in the value of the SharePoint column "veroorzakerSoort". In my example the value is "Cliënt" or "Familie van een cliënt".

When I test the query with a hardcoded parameter it works great also with the trema.

Who can help me to troubleshoot this?

[emha]

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

[khalidinio]

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.

[emha]

still typo with missing apostrophe?

[khalidinio]

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?

[emha]

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.

[khalidinio]

@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?

[emha]

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.

[khalidinio]

@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.

[emha]

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.