Please HELP! - Error while passing input parameters to sp

Discussion in 'ASP .Net' started by =?Utf-8?B?SmltbXk=?=, Sep 9, 2005.

  1. 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>
    =?Utf-8?B?SmltbXk=?=, Sep 9, 2005
    #1
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. David
    Replies:
    1
    Views:
    489
    David
    Jul 19, 2004
  2. =?Utf-8?B?TWlrZQ==?=
    Replies:
    0
    Views:
    751
    =?Utf-8?B?TWlrZQ==?=
    Jun 22, 2005
  3. KK
    Replies:
    2
    Views:
    550
    Big Brian
    Oct 14, 2003
  4. Weasley
    Replies:
    2
    Views:
    761
    ankit agarwal
    Dec 5, 2009
  5. Eric Johnston
    Replies:
    5
    Views:
    97
    Grant Wagner
    Jul 22, 2005
Loading...

Share This Page