Why SQL Server stored procedure doesn't recognize text from Visual Studio?

Question

Ok. Will try to explain with images... This is my SQL Server and my query:

enter image description here

As you see I getting the result. But then I start my app in VS2013, put break point when I want to call my stored procedure and copy text from VS:

enter image description here

And paste Name in Qhuery:

enter image description here

But I didn't get the result! The names ABSOLUTELY THE SAME!

This Query doesnt't work:

SELECT TOP 1 [Employee].[EmployeeID]
        FROM [Employee]
        WHERE [Employee].[FullName] = 'Brad Oelmann'

Are you expecting the `SqlCommand` to return results? In your c# code you don't read the result set - you execute as a non-query statement. What are you trying to do, because if you are looking for a result set, that's not the way to do it. You need a `SqlDataReader` or `SqlDataAdapter` to read row data. — Charleh, Dec 19 '13 at 17:39
Ok. I Open new query and paste this select. When I copy text from visual studio and paste instead of @FullName it doesn't work, when I'm typing the same text it works. — Bryuk, Dec 19 '13 at 17:41
it's only piece of stored procedure, but this query doesn't work — Bryuk, Dec 19 '13 at 17:42
`I Open new query and paste this select. When I copy text from visual studio and paste instead of @FullName it doesn't work`, it's still ***really unclear*** what does and doesn't work, and what you did when those things do and don't work. — Mike Perrenoud, Dec 19 '13 at 17:48
I had this same thing happen when I copied a SELECT statement out of a Lync conversation and tried it. It just didn't work. I am assuming that there is a special character in the text you are copying. It is weird though. — SteveB, Dec 19 '13 at 18:11
I am assuming that the query isn't returning the right data to your app. Have you tried editing the data in your wariable watch window (delete the name and retype it)? See if the application then gets the right data. If so, you know that the data isn't EXACTLY the same although it looks like it is. — SteveB, Dec 19 '13 at 18:14
How to remove "a special character" from string before passing it to the stored procedure? — Bryuk, Dec 19 '13 at 18:17
I don't know. Did you run the test to see if you get back the correct data? — SteveB, Dec 19 '13 at 18:24

score 1 · Accepted Answer · edited May 23 '17 at 11:56

I agree the initial suspect is a "special character" that shows up as whitespace pasting in SSMS. It has happened to me filtering client data with t-sql.

To replace special characters, there is a good starting point here: .NET replace non-printable ASCII with string representation of hex code

In that case, they're looking for "control characters" in particular and doing a fancy replacement, but the idea of finding the special characters RegEx is the same.

You can look at all kinds of special sets of characters here: http://msdn.microsoft.com/en-us/library/20bw873z(v=vs.110).aspx

But it might be easier to define what you do want if you are doing something specific like a name. For example, you can replace anything that isn't an English letter (for one example) with a space:

    str = System.Text.RegularExpressions.Regex.Replace( _
            str, _
            "[^a-zA-Z]", _
            " ")

Thanks for checking out and using my answer, @Bryuk. I see and appreciate your "suggested edit". I think I'm too new to accept it myself. I hope it gets picked up soon. — Mike M, Dec 20 '13 at 16:26

score 0 · Answer 2 · answered Dec 19 '13 at 18:59

0

It's really stupid, but I got simple solution. Since my DB Table contains only ~50 records, I retyped all names and now it works. So the problem was not in VS but in SQL Server side.

If somebady will have similar problem, first of all try to update data in your table somehow. You can try to select all data, copy-paste in in notepad and put it back in SQL Server.

answered Dec 19 '13 at 18:59

Bryuk

3,295
10
45
74

Maybe the problem was the collation or a "invisible" special character. If you retyped all names how do you populated that table first time? – jean Dec 19 '13 at 19:20
And rememeber you can't retype a table with a million records. – HLGEM Dec 19 '13 at 19:33

Why SQL Server stored procedure doesn't recognize text from Visual Studio?

2 Answers2