sql parameterized query in C# with string

Question

com = new SqlCommand("SELECT pro_name from products where pro_name= @idP", con);
                SqlParameter param = new SqlParameter
                {
                    ParameterName = "@idP",
                    Value = proNames[i]
                };
                com.Parameters.Add(param);

it works fine with integer type but for string its not working it give error

System.Data.SqlClient.SqlException: 'The parameterized query '(@idP nvarchar(4000))SELECT pro_name from products where pro_nam' expects the parameter '@idP', which was not supplied.'

Add also the setting for the SqlDbType in the constructor of the parameter with value equal to SqlDbType.NVarChar — Steve, Jan 13 '18 at 20:02
I did same thing but still end with same error com.Parameters.Add("@idP", SqlDbType.NVarChar,8000).Value=proNames[i]; — Ali, Jan 13 '18 at 20:09
Well, you are definitively adding that parameter, but if you get that error message then we should look at the remainder of your code when you execute that command to be certain that you haven't skipped something — Steve, Jan 13 '18 at 20:12
Please read [MCVE] guidance and make sure all necessary code and data present in the sample. I.e. in this case `proNames[i]` must be replaced with actual value (possibly simplified/sanitized) and actual call to DB shown (as frequently such problems come from constructing one query but using some other one to make actual call) — Alexei Levenkov, Jan 13 '18 at 20:21

Ehsan Ullah Nazir · Answer 1 · 2018-01-13T20:29:26.243

2

Try with passing proper DBType. In your case , this will be NVarChar

Make sure proNames[i] has value , Not Null or EmptyString

SqlParameter param = new SqlParameter()
{
    ParameterName = "@idP",
    SqlDbType = SqlDbType.NVarChar, // your DBType
    Value = proNames[i]
};

You can also try adding parameter as below:

command.Parameters.AddWithValue("@idP", proNames[i]);

edited Jan 13 '18 at 20:29

answered Jan 13 '18 at 20:05

Ehsan Ullah Nazir

1,827
1
14
20

` proNames[i]` has value ? – Ehsan Ullah Nazir Jan 13 '18 at 20:15
worked with `command.Parameters.AddWithValue("@idP", "proNames[i]");` ? – Ehsan Ullah Nazir Jan 13 '18 at 20:26
it gives empty result – Ali Jan 13 '18 at 20:29
Not sure what are you missing now , please look at excepted answer at https://stackoverflow.com/questions/18316767/c-sharp-execute-sqlcommand-with-parameters-in-using-code-block – Ehsan Ullah Nazir Jan 13 '18 at 20:35
that accepted answer is worked with integer i want to do with string ...... – Ali Jan 13 '18 at 20:37
did you tried executing SP directly on SQL server ? – Ehsan Ullah Nazir Jan 13 '18 at 21:12
same error on SQL as well ? as you have in application ? – Ehsan Ullah Nazir Jan 14 '18 at 14:27

score 1 · Answer 2 · answered Jan 13 '18 at 20:12

1

You should set SqlDbType and Size if it's VAR* type.

 SqlParameter param = new SqlParameter()
 {
     ParameterName = "@idP",
     SqlDbType = SqlDbType.VarChar,
     Size = 4000,
     Value = proNames[i]
 };

By the way, if you'll want to pass NULL - you'll have to use DbNull.Value instead of just normal C# NULL.

answered Jan 13 '18 at 20:12

Justinas Marozas

2,482
1
17
37

What about `SqlDbType.NVarChar`? – Justinas Marozas Jan 13 '18 at 21:01
Is it still saying parameter not supplied? – Justinas Marozas Jan 13 '18 at 21:07

sql parameterized query in C# with string

2 Answers2

Linked