Inputoutput Params

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
Wed Jan 07 09:28:12 CST 2004

Hi Micky,

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

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

"Micky Doyle" <mickyddoyle@yahoo.com> wrote in message
news:d3c9042b.0401070705.37a976f6@posting.google.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

bruce
Wed Jan 07 13:16:29 CST 2004

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" <mickyddoyle@yahoo.com> wrote in message
news:d3c9042b.0401070705.37a976f6@posting.google.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

mickyddoyle
Thu Jan 08 08:15:22 CST 2004

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" <nospam_brubar@safeco.com> wrote in message news:<et#cCLV1DHA.1336@TK2MSFTNGP12.phx.gbl>...
> 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" <mickyddoyle@yahoo.com> wrote in message
> news:d3c9042b.0401070705.37a976f6@posting.google.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
Thu Jan 22 04:31:04 CST 2004

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



----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---