locked
Unnamed parameter issue RRS feed

  • Question

  • Hi All,

    We have developed data provider for our database.

    I have strange issue

    string

    job = "TestþJob"; //to see þ character in visual studio IDE press(alt +231)

    command.CommandText =

    "insert into JOBS (JOB_CODE,JOB_TITLE ) Values (?,?)";

     

    IDataParameter pJobCode = command.CreateParameter(); IDataParameter pJobText = command.CreateParameter();

     

    pJobCode.Value = jobCode;

    command.Parameters.Add(pJobCode);

     

    pJobText.Value = job ;

    command.Parameters.Add(pJobText);

    command.ExecuteNonQuery();

    When I insert this value using unmaned parameter It store it as "Test  Job" in database. when I insert it by executin below query

    insert1 = "INSERT INTO JOBS (JOB_CODE, JOB_TITLE) VALUES('" +jobCode+ "','" + job + "')";

    It executes fine and store it as "
    TestþJob" database .

    My question is why unnamed parameters store it as "Test  Job" instead of storing it as TestþJob

    Please help me

    Thanks,
    Rahul

    string

     

    • Moved by KJian_ Monday, March 8, 2010 10:06 AM (From:.NET Framework inside SQL Server)
    Wednesday, March 3, 2010 12:36 PM

Answers

  • You need to pass Command Pameter as nvarchar type as you are passing a unicode character

    The following works on my machine and it can insert  þin the database as   TestþJob

    OleDbConnection objConn = new OleDbConnection("Provider=SQLNCLI10;Server=localhost;Database=tempdb;Trusted_Connection=yes;");
    OleDbCommand objCommand = new OleDbCommand();
    
    
    string Job = "TestτJob";
    
    objCommand.Connection = objConn;
    objCommand.CommandType = CommandType.Text;
    objCommand.CommandText = "insert into JOBS (JOB_CODE,JOB_TITLE ) Values (?,?)";
    
    
    OleDbParameter pJobCode = new OleDbParameter("@JobCode", SqlDbType.NVarChar);
    pJobCode.Value = "JobCode";
    pJobCode.Size = 50;
    
    objCommand.Parameters.Add(pJobCode);
    
    
    OleDbParameter pJobText = new OleDbParameter("@JobText", SqlDbType.NVarChar);
    pJobText.Value = Job;
    pJobText.Size = 50;
    
    
    objCommand.Parameters.Add(pJobText);
    
    objConn.Open();
    int i = objCommand.ExecuteNonQuery();
    objConn.Close();
    

     "?" is called unnamed paramters for OLEDB  MSDN documnets it very well here
    http://msdn.microsoft.com/en-us/library/bbw6zyha.aspx

    Tuesday, March 9, 2010 3:55 PM

All replies

  • Hi,

    Could you please elaborate a bit?  Is it SQL Server or other databases you are using?


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, March 8, 2010 10:09 AM
  • Can you post the entire code using "Insert Code Block"   looks like formatting is messed up. Post the entire code.

    Monday, March 8, 2010 3:49 PM
  • string job = "TestþJob"; //to see þ character in visual studio IDE press(alt +231)
    command.CommandText = "insert into JOBS (JOB_CODE,JOB_TITLE ) Values (?,?)"; 
    IDataParameter pJobCode = command.CreateParameter(); IDataParameter pJobText = command.CreateParameter(); 
     pJobCode.Value = jobCode;
    command.Parameters.Add(pJobCode);
    
     pJobText.Value = job ;
    command.Parameters.Add(pJobText);
    command.ExecuteNonQuery();
    //When I insert this value using unmaned parameter It store it as "Test  Job" in database. when I insert it by executin below query
    insert1 = "INSERT INTO JOBS (JOB_CODE, JOB_TITLE) VALUES('" +jobCode+ "','" + job + "')";

    It executes fine and store it as "TestþJob" database .

    My question is why unnamed parameters store it as "Test  Job" instead of storing it as TestþJob

    Please help me
    Tuesday, March 9, 2010 1:59 PM
  • You need to pass Command Pameter as nvarchar type as you are passing a unicode character

    The following works on my machine and it can insert  þin the database as   TestþJob

    OleDbConnection objConn = new OleDbConnection("Provider=SQLNCLI10;Server=localhost;Database=tempdb;Trusted_Connection=yes;");
    OleDbCommand objCommand = new OleDbCommand();
    
    
    string Job = "TestτJob";
    
    objCommand.Connection = objConn;
    objCommand.CommandType = CommandType.Text;
    objCommand.CommandText = "insert into JOBS (JOB_CODE,JOB_TITLE ) Values (?,?)";
    
    
    OleDbParameter pJobCode = new OleDbParameter("@JobCode", SqlDbType.NVarChar);
    pJobCode.Value = "JobCode";
    pJobCode.Size = 50;
    
    objCommand.Parameters.Add(pJobCode);
    
    
    OleDbParameter pJobText = new OleDbParameter("@JobText", SqlDbType.NVarChar);
    pJobText.Value = Job;
    pJobText.Size = 50;
    
    
    objCommand.Parameters.Add(pJobText);
    
    objConn.Open();
    int i = objCommand.ExecuteNonQuery();
    objConn.Close();
    

     "?" is called unnamed paramters for OLEDB  MSDN documnets it very well here
    http://msdn.microsoft.com/en-us/library/bbw6zyha.aspx

    Tuesday, March 9, 2010 3:55 PM
  • Hi

    Thanks for your reply.

    But, I am not able to inser the character we get when we press (alt+230). I am able to insert the character when press alt+231 or alt+322 etc.

    why I am not able to insert only this character(ie alt+230) how special  this character is?


    Regards,
    rahul
    Monday, March 15, 2010 10:00 AM
  • In your **first** post you said alt+231 does not insert, now its alt + 230?

    ADO.net and SQL Server is impartial to unicode characters  as long as you use correct datatypes and pass them as unicode data.

    Monday, March 15, 2010 1:45 PM
  • Hi

    I am sorry.

    I am not able to insert the cahracter (alt+231). but, I am able to insert alt+230,alt+232 etc.

    How this (alt+231) character is different from other characters (i.e. alt+230,alt+232).

    Please help me.



    Regards,
    Prash
    Wednesday, March 17, 2010 5:04 AM
  • Code I posted inserts τ (alt + 231) Character.
    Wednesday, March 17, 2010 2:56 PM
  • Hi

     Thanks for your reply...

    As you said in your previous post i.e.

    You need to pass Command Pameter as nvarchar type as you are passing a unicode character"

     now my question is

    why we need to pass it as Nvarchar?

    I got following info nvarchar exists so that you can store unicode data (characters that require 2 bytes to be represented). Each character of an nvarchar column requires 2 bytes of storage whereas a varchar column requires 1 byte per character.

    How many bytes the a icelandic character takes?

    Thanks,
    Rahul
    Thursday, March 18, 2010 4:52 AM