Please HELP! - Error while passing input parameters to sp

[Guest]

I have a web page that displays data from a sql server db. I'm using a stored
procedure to return data from a field of type text as an OUTPUT parameter.

How can I use an OUTPUT parameter of datatype text being returned from a
stored procedure? I need to use it's value to display comments text on a
profile page. The comments will be more than 8000 characters so using varchar
is not an option.

Maybe I'm on the wrong track...if so, then the goal is to display text data
larger than 8000 characters on a web page alongwith various other data by
using stored procedure parameters.

<SQL sp>
CREATE PROCEDURE dbo.getNote (
....other params here
@nteComments text = null OUTPUT,
@NoteId int)
AS
select @nteComments = nteComments -- this is a text column
From Notes
where NoteId = @NoteId
return 1
</SQL sp>

<VB.Net code 1>
strCon = ConfigurationSettings.AppSettings("constring")
con = New SqlClient.SqlConnection(strCon)
cmd = New SqlClient.SqlCommand("getNote", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@nteComments", SqlDbType.Text).Direction =
ParameterDirection.Output
.... other params here

con.Open()
cmd.ExecuteNonQuery() 'CRASH! ERROR above is returned
con.Close()
</VB.Net code 1>


<error 1>
Parameter 4: '@nteComments' of type: String, the property Size has an
invalid size: 0
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information about
the error and where it originated in the code.
Exception Details: System.InvalidOperationException: Parameter 4:
'@nteComments' of type: String, the property Size has an invalid size: 0
</error 1>


Then I modify the code (after reading various articles on google) to have
the size specified. doign that results in code and error sets 2...below...

<VB.Net code 2>
strCon = ConfigurationSettings.AppSettings("constring")
con = New SqlClient.SqlConnection(strCon)
cmd = New SqlClient.SqlCommand("getNote", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@nteComments", SqlDbType.Text, 10000).Direction =
ParameterDirection.Output
.... other params here

con.Open()
cmd.ExecuteNonQuery() 'CRASH! ERROR above is returned
con.Close()
</VB.Net code 2>

<error 2>
A severe error occurred on the current command. The results, if any, should
be discarded.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information about
the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: A severe error
occurred on the current command. The results, if any, should be discarded."
</error 2>