Sql Output Parameters

Question

0.00/5 (No votes)

See more:

Hi,
i have the following problem. I want to access some SQL output parameter
via my c# code.

The procedure looks like:

SQL

CREATE PROCEDURE sp_DoSome

@VALUE int output,@OTHER nvarchar(MAX) output

AS
BEGIN
	
SELECT @VALUE =  -198;
SELECT @OTHER = 'Works';

END
GO

And the code to access the values:

C#

 using (SqlCommand com = new SqlCommand 
{ CommandType = CommandType.StoredProcedure, 
  CommandText = "sp_AskForUpdate", 
  Connection = _connection })
                 
    {
      com.Parameters.AddWithValue("@VALUE", SqlDbType.Int);
      com.Parameters.AddWithValue("@OTHER", SqlDbType.NVarChar);
      com.Parameters["@VALUE"].Direction = ParameterDirection.Output;
      com.Parameters["@OTHER"].Direction = ParameterDirection.Output;
      _connection.Open();
      com.ExecuteNonQuery();
      temp = com.Parameters["@VALUE"].Value.ToString() + com.Parameters["@OTHER"].Value;
      _connection.Close();
                }

....goes on

Doing this just with int's works fine. But in all other cases generates an exception
like this (nvarchar):

C#

04/11/2013 11:10:00 Error converting data type nvarchar(max) to int.Void OnError(System.Data.SqlClient.SqlException, Boolean, System.Action`1[System.Action])System.Data.SqlClient.SqlException (0x80131904): Error converting data type nvarchar(max) to int.
   bei System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   bei System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   bei System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   bei System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   bei System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite)
   bei System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
   bei System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
   bei System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   bei JuloGroupService.Service1.AskForUpdate(Int32 companyId, Int32 miaId, Boolean& free) in C:\Users\schafmann\Desktop\visual studio 2010\Projects\JuloGroupService\JuloGroupService\Service1.svc.cs:Zeile 331.
ClientConnectionId:d955c0e9-079a-452c-8538-005e91776d3a

I dont understand why this generates a converting error???

Thanks for any help
Andree

Posted 10-Apr-13 23:14pm

Schafmann

Updated 11-Apr-13 0:58am

v2

Add a Solution

Comments

CHill60 11-Apr-13 5:39am

How is temp declared?

Schafmann 11-Apr-13 5:43am

temp is declared as string

CHill60 11-Apr-13 5:58am

You could try adding .ToString() to the + com.Parameters["@OTHER"].Value; bit. Doesn't help to explain why it's happening I know! (But it might make the problem "go away")

Schafmann 11-Apr-13 6:20am

thanks, but this aslo dont work. I have changed the type for the @Other
Parameter to "SqlDbType.Int" and it works. But any other type wont work (the
same error message like above)

tumbledDown2earth 11-Apr-13 6:09am

You should consider the return values from the sp. They should match

Schafmann 11-Apr-13 6:33am

The value type in the stored procedure is declared: @OTHER nvarchar(MAX) output;
In my c# code its: com.Parameters.AddWithValue("@OTHER", SqlDbType.NVarChar);

Isnt this right to match the value?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Schafmann · Accepted Answer · 2013-04-11T01:20:00

For now i have solved the Problem in this way:

C#

 using (SqlCommand com = new SqlCommand 
{ CommandType = CommandType.StoredProcedure, 
  CommandText = "sp_AskForUpdate", 
  Connection = _connection })
                 
    {
      com.Parameters.AddWithValue("@VALUE", SqlDbType.Int);
      com.Parameters.AddWithValue("@OTHER", SqlDbType.NVarChar);
      com.Parameters["@VALUE"].Direction = ParameterDirection.Output;
      com.Parameters["@OTHER"].Direction = ParameterDirection.Output;

      //Adding this to my code has solved the problem
      com.Parameters["@OTHER"].Size = 255;
      com.Parameters["@OTHER"].Value = " ";

      _connection.Open();
      com.ExecuteNonQuery();
      temp = com.Parameters["@VALUE"].Value.ToString() + com.Parameters["@OTHER"].Value;
      _connection.Close();
                }