By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,694 Members | 1,846 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,694 IT Pros & Developers. It's quick & easy.

Please HELP! - Error while passing input parameters to sp

P: n/a
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>
Nov 19 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.