Inputoutput Params

[Micky Doyle]

I guess this is bad practice but i have an SQL 2000 SP that uses a
single varchar(100) parameter for input and output. I would like to
use an SQLcommand object to call it but this doesn't seem to work,
here's my code:

SqlParameter ParamSessionID = new SqlParameter("@SessionID",
SqlDbType.VarChar, 100);
ParamSessionID.Direction = ParameterDirection.InputOutput;
Comm1.Parameters.Add(ParamSessionID);
Comm1.Parameters["@SessionID"].Value =
m_currentPage.Session.SessionID;
Comm1.CommandType = CommandType.StoredProcedure;
Conn.Open();
Comm1.ExecuteScalar();

The error i get is 'System.Data.SqlClient.SqlException: String or
binary data would be truncated.'

Using the debugger i can see the value of the parameteris as a 25
charected string.
SQL profiler shows the following command being issued:

declare @P1 varchar(100)
set @P1=NULL
exec sp_CevasSessionCreate @SessionID = @P1 output
select @P1

This seems to suggest that SQLClient doesn't implement InputOutput as
expected, i could get the data by setting the commandtype to text and
using ExecuteScalar but it would be nice to do this via the command
object.
Any ideas?

Micky

 

[Miha Markic]

Hi Micky,

Isn't possible that you are putting too large text into @sessionid within
sp?
Can you show us sp body?

 

[bruce barker]

ExecuteScaler expects a 1 row resultset, if your stored proc does not return
one, then you are confusing .net

if your proc only returns parameters not any rows, use:

ExecuteNonQuery

-- bruce (sqlwork.com)

 

[Micky Doyle]

Thanks guys, but what I am really asking about is using the same
parameter to pass data in both ways. This doesn't seem to work. The
error message is just a side effect of this.

Micky.

ExecuteScaler expects a 1 row resultset, if your stored proc does not return
one, then you are confusing .net

if your proc only returns parameters not any rows, use:

ExecuteNonQuery

-- bruce (sqlwork.com)

I guess this is bad practice but i have an SQL 2000 SP that uses a
single varchar(100) parameter for input and output. I would like to
use an SQLcommand object to call it but this doesn't seem to work,
here's my code:

SqlParameter ParamSessionID = new SqlParameter("@SessionID",
SqlDbType.VarChar, 100);
ParamSessionID.Direction = ParameterDirection.InputOutput;
Comm1.Parameters.Add(ParamSessionID);
Comm1.Parameters["@SessionID"].Value =
m_currentPage.Session.SessionID;
Comm1.CommandType = CommandType.StoredProcedure;
Conn.Open();
Comm1.ExecuteScalar();

The error i get is 'System.Data.SqlClient.SqlException: String or
binary data would be truncated.'

Using the debugger i can see the value of the parameteris as a 25
charected string.
SQL profiler shows the following command being issued:

declare @P1 varchar(100)
set @P1=NULL
exec sp_CevasSessionCreate @SessionID = @P1 output
select @P1

This seems to suggest that SQLClient doesn't implement InputOutput as
expected, i could get the data by setting the commandtype to text and
using ExecuteScalar but it would be nice to do this via the command
object.
Any ideas?

Micky

 

[Azhrarn]

Heya,
don t know if this is the case you you, but my c#program was spitting
out the same problem because I was trying to write a string to a db
field which was smaller than the string I was trying to write.
Solution:
increase the field size.
Worked for me
Bye
Azhrarn