Inputoutput Params

  • Thread starter Thread starter Micky Doyle
  • Start date Start date
M

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
 
Hi Micky,

Isn't possible that you are putting too large text into @sessionid within
sp?
Can you show us sp body?
 
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)
 
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.

bruce barker said:
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 said:
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
Click to expand...
Click to expand...
 
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
 
Back
Top