nvarchar converted to string - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

nvarchar converted to string

Author

Topic

daliah
Starting Member

3 Posts

Posted - 2011-04-20 : 12:26:07

hi,

i created a stored procedure that returns me an nvarchar(50) output. but when i use this stored procedure in visual studio 2010, i get only the first character of this output. i made sure that i casted it to string.
any ideas??
thank you

dalia

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-20 : 12:49:36

In your stored procedure, make sure that the parameter is declared as NVARCHAR(50) and not just NVARCHAR. Also, make sure that it is declared as OUTPUT. So it should be something like this:


@paramName NVARCHAR(50) OUTPUT

Then, in Visual Studio, you also need to specify that the parameter is NVARCHAR, length 50, and OUTPUT (or INPUTOUTPUT). In C# for example:


SqlParameter param = new SqlParameter("@paramName", System.Data.SqlDbType.NVarChar, 50);
param.Direction = System.Data.ParameterDirection.Output; // Or InputOutput

daliah
Starting Member

3 Posts

Posted - 2011-04-20 : 16:37:31

thank you, but this didn't work.
this is the stored procedure:

create procedure usp_get_password
@id int,
@password nvarchar(50) output

as
begin

select @password = ua_password
from dbo.user_account
where ugi_ID = @id

end

and this is how i use it in visual studio

public string M_get_password(int id)
{
string connStr = ConfigurationManager.ConnectionStrings["MyDbConn"].ToString();
SqlConnection connection = new SqlConnection(connStr);
SqlCommand command = new SqlCommand();
command.Connection = connection;
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandText = "usp_get_password";
command.Parameters.AddWithValue("@id", id);
command.Parameters.AddWithValue("@password", "");
command.Parameters["@password"].Direction = System.Data.ParameterDirection.Output;
connection.Open();
command.ExecuteNonQuery();
string password = (string)command.Parameters["@password"].Value;
connection.Close();
return password;

}

for example if the password was "daliah", the visual studio code returns only "d"

dalia

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-20 : 17:36:22

In your C# code, when you use AddWithValue, the string you are adding is an empty string, so it gets a default size of 1. Change it as shown in red below, or use the code I had posted earlier where you define the parameter and then add it to the parameters collection.


public string M_get_password(int id)
{
string connStr = ConfigurationManager.ConnectionStrings["MyDbConn"].ToString();
SqlConnection connection = new SqlConnection(connStr);
SqlCommand command = new SqlCommand();
command.Connection = connection;
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandText = "usp_get_password";
command.Parameters.AddWithValue("@id", id);
command.Parameters.AddWithValue("@password", "");
command.Parameters["@password"].Size = 50;
command.Parameters["@password"].Direction = System.Data.ParameterDirection.Output;
connection.Open();
command.ExecuteNonQuery();
string password = (string)command.Parameters["@password"].Value;
connection.Close();
return password;

}

daliah
Starting Member

3 Posts

Posted - 2011-04-22 : 05:46:13

thank you, this finally worked :)

dalia

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-22 : 08:08:48

Glad it did, and you are quite welcome :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources