Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > Please HELP! - Error while passing input parameters to sp

Reply
Thread Tools

Please HELP! - Error while passing input parameters to sp

 
 
=?Utf-8?B?SmltbXk=?=
Guest
Posts: n/a
 
      09-09-2005
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>


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Methods for passing parameters while moving in various Java ServerPages (JSP) Weasley Java 2 12-05-2009 03:18 PM
Passing parameters to an executable vs. passing them to a server Ramon F Herrera C++ 8 09-13-2009 02:48 AM
Error while passing input parameters to stored procedure using DAA =?Utf-8?B?TWlrZQ==?= ASP .Net 0 06-22-2005 07:44 PM
Help while error "Error while trying to run project:" David ASP .Net 1 07-19-2004 08:41 PM
Got error msg while Debugging : Error while trying to run project: ... ^CrazyCoder^ ASP .Net 3 09-15-2003 09:40 AM



Advertisments