ADO.net SqlParameter using a + sign between words

Question

I am using ADO.net and using SqlParameter passing into a stored procedure a group of words that sometimes may have a + between then. Up until the plus sign the search is working fine but once I add it I am getting no results. Do I need to change anything in the c# code I am run the sp fine with the plus sign.

c# code

SqlParameter[] parms = new SqlParameter[] {
    new SqlParameter("@Title", SqlDbType.VarChar)
};
parms[0].Value =Title;

SQL SP

create PROCEDURE [usp_Select_Title] -
    -- Add the parameters for the stored procedure here  
@Title nvarchar(512) = null




WHERE (dcc.Title like '%'+ @Title +'%')

Is that stored procedure doing anything with that @TItle that might trip? — Hans Keﬆing, Jul 20 '23 at 13:44
Its just doing a like I can run in from management studio and it runs fine — Jefferson, Jul 20 '23 at 13:46
Are trying to pass in _multiple_ titles in one parameter, or you are trying to search for a title with a plus sign in it? — D Stanley, Jul 20 '23 at 14:14
trying to search for one title with a plus sign &title=Red + Blue — Jefferson, Jul 20 '23 at 14:15

Albert Einstein · Accepted Answer · 2023-07-20T14:34:47.557

2

As per the discussion in comment, the value was passed to via query string in URL. In this the case the + sign has a semantic meaning in the query string which is used to represent a space.

So, the solution for this is to encode the + sign with %2B in URL and decode back to + in C#.

References:

edited Jul 20 '23 at 14:34

answered Jul 20 '23 at 13:58

Albert Einstein

7,472
8
36
71

I did try that but I am still not getting the data being returned – Jefferson Jul 20 '23 at 14:00
can it be related to the SQL WHERE Clause – Jefferson Jul 20 '23 at 14:03
Can you put debugger and check what value is going to SP. – Albert Einstein Jul 20 '23 at 14:06
in sql profiler its removing the + sign – Jefferson Jul 20 '23 at 14:11
So, you need to replace your + sign with unicode from ado.net and replace back in sp – Albert Einstein Jul 20 '23 at 14:12
sorry so it looks like postman is removing the plus – Jefferson Jul 20 '23 at 14:13
@Jefferson can you create executable demo in https://dotnetfiddle.net/ and http://sqlfiddle.com/ – Albert Einstein Jul 20 '23 at 14:16
@Jefferson are you passing your parameter from URL? – Albert Einstein Jul 20 '23 at 14:17
I'm not 100% it's the same, but anytime I've used `SqlParamter` I've used three parameters - Parameter Name, SqlDbType and Size: `"@Title", SqlDbType.NVarChar, -1` (-1 for max). Could that be your issue? – Patrick Hurst Jul 20 '23 at 14:18
Yes I am passing the parameter from a URL in postman. – Jefferson Jul 20 '23 at 14:21
2

@Jefferson I think that is your issue. https://stackoverflow.com/questions/30262783/allow-plus-sign-in-url https://stackoverflow.com/questions/6855624/plus-sign-in-query-string – Albert Einstein Jul 20 '23 at 14:23
yes that did it thanks – Jefferson Jul 20 '23 at 14:24

ADO.net SqlParameter using a + sign between words

1 Answers1