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
– Steve, Commented Jan 13, 2018 at 20:02
I did same thing but still end with same error com.Parameters.Add("@idP", SqlDbType.NVarChar,8000).Value=proNames[i]; — Ali
– Ali, Commented Jan 13, 2018 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
– Steve, Commented Jan 13, 2018 at 20:12
Please read minimal reproducible example 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
– Alexei Levenkov, Commented Jan 13, 2018 at 20:21

Ehsan Ullah Nazir · Accepted Answer · 2018-01-13 20:29:26Z

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, 2018 at 20:29

answered Jan 13, 2018 at 20:05

Ehsan Ullah Nazir

1,9371 gold badge15 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Ehsan Ullah Nazir Over a year ago

` proNames[i]` has value ?

Ehsan Ullah Nazir Over a year ago

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

Ali Over a year ago

it gives empty result

Ehsan Ullah Nazir Over a year ago

Not sure what are you missing now , please look at excepted answer at stackoverflow.com/questions/18316767/…

Ali Over a year ago

that accepted answer is worked with integer i want to do with string ......

|

Justinas Marozas · Accepted Answer · 2018-01-13 20:12:22Z

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, 2018 at 20:12

Justinas Marozas

2,7321 gold badge21 silver badges40 bronze badges

2 Comments

Justinas Marozas Over a year ago

What about SqlDbType.NVarChar?

Justinas Marozas Over a year ago

Is it still saying parameter not supplied?

Collectives™ on Stack Overflow

sql parameterized query in C# with string

2 Answers 2

7 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related