How to pass Unicode value as parameter for select command

Question

Here is my code but it returns -1 value. The Name field contains a unicode value.

  using (SqlConnection conn = new SqlConnection(connStr))
  {      
      string str = string.Format("select SrNo from Jat where Name=@Name");
      SqlCommand dCmd = new SqlCommand(str, conn);
      try
      {               
          dCmd.Parameters.AddWithValue("@Name","N" +Name);
          int i= dCmd.ExecuteNonQuery();
          return i;
      }
      catch
      { 
           throw; 
      }
   }

See this : **http://stackoverflow.com/questions/7548371/unicode-sql-query-w-parameter-instead-n-prefix** — huMpty duMpty, Sep 20 '12 at 09:01

Pranay Rana · Accepted Answer · 2012-09-20T09:01:58.430

2

there is no need to write down

dCmd.Parameters.AddWithValue("@Name","N" +Name);

remove "N" from this but it doesnt create nvarchar parameter

dCmd.Parameters.AddWithValue("@Name", Name);

To create nvarchar type parameter than you can do this

dCmd.Parameters.Add(new SqlParameter("@Name", SqlDbType.NVarChar)).Value = Name;

this will create nvarchar parameter than you can add this parameter as nvarchar parameter

one more change in code is make use of ExecuteScalar

int i= Convert.ToInt32( dCmd.ExecuteScalar());

make use of ExecuteScalar method rather than ExecuteNonQuey ....becuase non Sclar method return value of you SrNo and NonQuery return you row affected when using update and insert query

edited Sep 20 '12 at 09:01

answered Sep 20 '12 at 08:56

Pranay Rana

175,020
35
237
263

@Somnath - Check your database against the passed in name. The query requires it to be _identical_. – Oded Sep 20 '12 at 09:01
@Somnath - make use of ExecuteScalar method rather than ExecuteNonQuey ....becuase non Sclar method return value of you SrNo and NonQuery return you row affected when using update and insert query – Pranay Rana Sep 20 '12 at 09:03
thanks @PranayRana it works by using int i= Convert.ToInt32( dCmd.ExecuteScalar()); – Somnath Sep 20 '12 at 09:05

score 1 · Answer 2 · answered Sep 20 '12 at 09:00

1

First, the N'' is only required in TSQL for Unicode string literals.

Second, if you want to return the SELECTed SrNr, use ExecuteScalar()

answered Sep 20 '12 at 09:00

devio

36,858
7
80
143

How to pass Unicode value as parameter for select command

2 Answers2