Getting only first character in Output parameter in C#

Question

I have defined output parameter as shown below:

C#:

scom.Parameters.Add("@User_ID",SqlDbType.VarChar,8).Direction = ParameterDirection.Output ;

Sql:

@User_ID varchar(8) output

I am getting complete string when executing procedure in Sql Server, but getting only first character in C#. I searched a lot and ensured that size is defined in both C# and Sql. Even I tried with fixed length character (Char(8)), but still getting only first character in C#. Please let me know what is the issue.

C# Code:

public bool CheckPhone(string phoneNumber)
{
    SqlConnection myconn=new SqlConnection(connectionString);
    try
    {
        myconn.Open();
        SqlCommand scom = new SqlCommand("AuthenticatePhone", myconn);
        scom.CommandType = CommandType.StoredProcedure;
        scom.Parameters.Add("@phoneNumber", SqlDbType.BigInt).Value = Convert.ToInt64(phoneNumber);
        scom.Parameters.Add("@User_ID", SqlDbType.Char, 8).Direction = ParameterDirection.Output;

        scom.Parameters.Add("@User_Name", SqlDbType.Char, 120).Direction = ParameterDirection.Output;

        scom.ExecuteNonQuery();
        if (scom.Parameters["@User_Name"] == null)
        {
            return false;
        }
        else
        {
            UserID = (string)scom.Parameters["@User_ID"].Value;//.ToString();
            UserName = (string)scom.Parameters["@User_Name"].Value;//.ToString();
            myconn.Close();
            return true;
        }
    }
    catch (Exception e)
    { 
        string error = e.InnerException + e.Message; 
    }
    finally 
    { 
        myconn.Close();
    }

    return false;
}

Sql:

Create procedure dbo.AuthenticatePhone

  @phoneNumber numeric(11,0)        ,
  @User_ID     varchar(8)    output ,
  @User_Name   varchar(120)  output
as
begin

  Select @User_ID   = convert(varchar(8),[User_ID]) ,
         @User_Name = [User_Name]
  from dbo.NRE_Users
  where PhoneNumber = @phoneNumber
  ;

  print @User_ID
  print @User_Name

end

Have you tried to use SqlDbType.VarChar ? Char doesn't match your procedure definition. — Luke Marlin
– Luke Marlin, Commented Mar 12, 2014 at 16:24
No it didn't return. I am not sure what could be the issue. Now I am not using stored procedure. I am executing sql query through sql adapter. — user3411591
– user3411591, Commented Mar 15, 2014 at 11:13

Andrew Li · Accepted Answer · 2018-09-17 12:54:08Z

2

Try to use the below code:

Parameter[param number].Size = 200;

edited Sep 17, 2018 at 12:54

Andrew Li

1,05512 silver badges29 bronze badges

answered Sep 17, 2018 at 11:34

Hisham Ogbah

213 bronze badges

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

Comments

Nicholas Carey · Accepted Answer · 2014-03-12 19:52:19Z

Doesn't repro for me. Given this stored procedure:

create procedure dbo.AuthenticatePhone

  @phoneNumber numeric(11,0)        ,
  @User_ID     varchar(8)    output ,
  @User_Name   varchar(120)  output

as

  set @User_ID   = '1234567890' -- should be truncated to '12345678'
  set @User_Name = 'The quick brown fox jumped over the lazy dog.'

  return 0
go

Running this SQL in SSMS:

 declare @userId   varchar(1000) = 'xxx'
 declare @userName varchar(1000) = 'yyy'
 exec AuthenticatePhone 1 , @User_ID = @userId out , @User_Name = @userName out
 select @userId,@userName

results in the expected results:

@userId contains the expected 12345678
@userName contains the expected The quick brown fox jumped over the lazy dog.

Executing it via C#:

using ( SqlConnection connection = new SqlConnection( "Server=localhost;Database=sandbox;Trusted_Connection=True;" ) )
using ( SqlCommand    command    = connection.CreateCommand() )
{

  command.CommandType = CommandType.StoredProcedure;
  command.CommandText = "dbo.AuthenticatePhone" ;

  SqlParameter phoneNumber = new SqlParameter {
    ParameterName = "@phoneNumber"           ,
    IsNullable    = true                     ,
    Direction     = ParameterDirection.Input ,
    Value         = 2125551212L              ,
    } ;
  command.Parameters.Add( phoneNumber ) ;

  SqlParameter userId = new SqlParameter {
    ParameterName = "@User_ID"                ,
    IsNullable    = true                      ,
    Direction     = ParameterDirection.Output ,
    DbType        = DbType.String             ,
    Size          = 1000                      ,
    Value         = DBNull.Value              ,
    } ;
  command.Parameters.Add( userId ) ;

  SqlParameter userName = new SqlParameter {
    ParameterName = "@User_Name" ,
    IsNullable    = true ,
    Direction     = ParameterDirection.Output ,
    DbType        = DbType.String ,
    Size          = 1000 ,
    Value         = DBNull.Value ,
    } ;
  command.Parameters.Add( userName ) ;

  connection.Open() ;
  int rowsAffected = command.ExecuteNonQuery() ;
  connection.Close() ;

  Console.WriteLine( "Rows Affected: {0}"     , rowsAffected             ) ;
  Console.WriteLine( "User ID:       {{{0}}}" , userId.Value   as string ) ;
  Console.WriteLine( "User Name:     {{{0}}}" , userName.Value as string ) ;

}

likewise results in the expected

Rows Affected: -1
User ID:       {12345678}
User Name:     {The quick brown fox jumped over the lazy dog.}

Even substituting your parameter definitions:

command.Parameters.Add("@phoneNumber", SqlDbType.BigInt).Value = 2125551212L ;
command.Parameters.Add("@User_ID", SqlDbType.Char, 8).Direction = ParameterDirection.Output;
command.Parameters.Add("@User_Name", SqlDbType.Char, 120).Direction = ParameterDirection.Output;

You get the expected results.

Though you might note that SqlDbType.Char is a SQL char(X). It's equivalent of `convert(char(120),'John Doe') in T-SQL. The .Net string will wind up padded with spaces to the specified length.

You might consider changing the type specifier to SqlDbType.VarChar: it will match the parameter declaration in the stored procedure and you won't find yourself needing to trim trailing whitespace from the string to make it useful.

Hi Nicholas, I will give it a try again. Sorry I posted code with Char (actually I was testing out with various options). Earlier it was VerChar and it was not working. I will try it again now and let you know.

Milen · Accepted Answer · 2014-03-12 16:24:50Z

0

change SqlDbType.Char to SqlDbType.VarChar

here:

scom.Parameters.Add("@User_ID", SqlDbType.Char, 8).Direction = ParameterDirection.Output;

scom.Parameters.Add("@User_Name", SqlDbType.Char, 120).Direction = ParameterDirection.Output;

answered Mar 12, 2014 at 16:24

Milen

8,8977 gold badges47 silver badges59 bronze badges

Comments

coolblue2000 · Accepted Answer · 2014-03-12 16:23:25Z

-1

As far as I know a char in .net is only one character long. So you will need to change the parameter type to varchar

scom.Parameters.Add("@User_ID", SqlDbType.Varchar, 8).Direction = ParameterDirect

answered Mar 12, 2014 at 16:23

coolblue2000

5,11614 gold badges46 silver badges78 bronze badges

3 Comments

Luke Marlin Over a year ago

Little typo : SqlDbType.VarChar and not SqlDbType.Varchar

Nicholas Carey Over a year ago

SqlDbType is the SQL data type involved. Not the CLR data type.

user3411591 Over a year ago

Hi, Sorry it was actually VarChar (I wrongly wrote it as Char), but it was not working.

Collectives™ on Stack Overflow

Getting only first character in Output parameter in C#

4 Answers 4

Comments

2 Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related