Inside an SQL query to my external database I have the following line in the WHERE clause.
AND LOWER({Customers}.[Name]) LIKE @Input
The input is formatted "'%input%'" so it will show up in the query as '%input%'.
Now sometimes ill put parts of words like 'who' and 'fish' and whatever and it will get plenty of results. However, if I put the letter t or blank or some random letters, instead of returning no rows (or in some cases many rows) it returns that the database returned the following error in the advanced query "Input string was not in a correct format. "
When I run the same query on my database I don't get that error at all. I have no idea what the problem is.
Ok, It was a developer issue. There were a few fields in one of the joined tables with some bad data and when it was returning any of those rows, when it would go to input that bad data into the result it was saying the input was not properly formatted. It was the result input not the input to the query that was called input. Wow, Sorry about that all! Thanks for the help.
Hi David,
is there a space when you insert one character like '% t %' ?
With the space character the statement will be like '% %'
> this will select zero or more than one characters - a blank space - zero or more than one char...
If possible show the @input contents
Hope this helps you
Regards
I have some logic to clear out extra spaces and I've been testing it in the SQL widgit and I'm still having this issue.
Hi David, I think for the value you passed in for param @Input, no need to be formatted as "'%input%'", please try with only "%" + input + "%".
Yes, I am passing it in as "'%" + input + "%'".
Can you test to hardcode % % and not passing them on the input.
something like:
AND LOWER({Customers}.[Name]) LIKE "%" + @Input + "%"
Have you checked the generated SQL? It should give you a hint of what is wrong in those cases.
I'm not seeing any issues with the generated SQL. It's so strange. It keeps sometimes saying the input format is incorrect. But other times is returns the expected results. It just depends on what the input text is. Depending on the letter I use. I don't have any issues with any letter combinations on my database itself. If I just type in 15 random letters it just returns no rows (which it should). But some random letters will return not correct format. When I type in a single letter it also give me not correct format even though on my database it just returns a LOT of results. I look out the return limit incase that was the problem somehow and it changed nothing.
that is very, very strange. Never heard anything like that.
For more than 20 years using DB's never heard something like this.
Could you show us when there is an error please? It sounds very weird.
Regards & thanks
I'm running the query as a test on the sql widget (I do get the same results when I test it in the app). I've been testing random strings into the test input of the widgit. When I enter "'%you%'" I get a few results, "'%yoz%'" I get no results, and "'%wee%'" I get the format error message. On the database itself I get the same results for the first and no results for the last two, which is what I would expect. If I had an issue with my query, or if I'm imporperly entering it into my db gui than I don't think I would get the correct results and when I'm using the same format that I should get a format issue. I'm at a loss.