Post reply

PLEASE HELP!!!

  • January 18, 2005 at 7:10 pm

    #87443

    Hi

    I am not sure if this is the right place to post this question.  But, I haven't had much luck on other threads.  I have a aspx page that has a textarea (multiline textbox control) which inserts to a sql server database using a stored proc.  The database type is Text and the insert part is working fine from what I could tell.  However, I am having trouble retrieving the text from the database.  The code I have to retrieve is below:

    ALTER PROCEDURE

    dbo.spGetText

    (

    @AppID

    uniqueidentifier,

    @gettext

    Text OUTPUT

    )

    AS

    SET  @gettext = (SELECT ResText FROM Files

    WHERE

    AppID = @AppID)

    I sure I can't assign value to the text output variable.  But, is there a work around or another way to get the same result.

    Also, can anyone tell me what is the equivalent datatype to use on the vb.net side to store the text parameter.  Any help or suggestions would be greatly appreciated.  Thanks in advance.

    Dinesh

     

  • January 18, 2005 at 7:27 pm

    #537959

    Unfortunately even thou Text is a usable variable type for a SP and it can be an OUTPUT variable you cannot do assignment beyond the variable itself, so what goes in is all that will come out.

    If you are after ResText just return it as a record set is your only option and using the code on your page read from the recordset object you created.

  • January 18, 2005 at 8:37 pm

    #537962

    I'd also be questioning the use of the text field in the table. Surely the person viewing the aspx page isn't going to type 2k worth of text into a textbox ??

    If the text field was varchar, or nvarchar, moving data in and out of the table would be so much simpler.

    --------------------
    Colt 45 - the original point and click interface

  • January 18, 2005 at 8:42 pm

    #537963

    Thanks for the reply Phill.  Actually the application I am working on is set up to insert a block of text like a resume which could exceed 2k in the multiline textbox (<textarea&gt.  Yes I agree setting up the field as varchar or nvarchar is definitely much easier but it wouldn't help in this situation.  Any other suggestions you might have would be greatly appreciated.  Thanks.

    Dinesh

  • January 18, 2005 at 9:57 pm

    #537973

    Yuck ... I hate storing files in the database. Much rather store them in the file system and store a reference to them in the database.

    I you'll need to do as Antares has suggested and return a recordset. I'm not sure of the .Net equivalent, but in VB6 you'd use GetChunk. Maybe this article will help??

    http://support.microsoft.com/kb/317034/EN-US/

    --------------------
    Colt 45 - the original point and click interface

  • January 18, 2005 at 10:20 pm

    #537976

    I think you misunderstood me.  It not a file I am trying to store in the database.  It a block of text.  For example, the user would cut and paste the txt into the textarea and I am saving the input into the db in a text datatype.  I want to allow about 10000 to 15000 characters.  That's why I choose text datatype.  And   I do have a file storing functionality which works fine.  Anyway, thanks for your help.  If you think of anything else please let me know. 

    Dinesh

  • January 18, 2005 at 10:47 pm

    #537980

    Ah yes, I did misunderstand you. I saw the word resume and presumed you were storing resumes in the database.

    So have you been find a resolution to your problem?

    --------------------
    Colt 45 - the original point and click interface

  • January 19, 2005 at 9:19 am

    #538116

    No not yet, I'll let you know and post it when I find the solution.  Although I don't understand why sql server won't let you assign text datatype as a output parameter.  Is there a specific reason for this that I am just not seeing?

    Dinesh

  • January 19, 2005 at 9:47 am

    #538126

    It just isn't allowed, not sure if it has something to do with the pointer mechanism or something to do with memory management? When your data returns via a recordset you should have to do nothing more than read the returned data out of the field of the recordset object. I have done this without any issues in the past but I also have only barely overran past the 8k varchar limit on many of the text columns I have needed.

  • January 19, 2005 at 10:41 am

    #538140

    Ok I found the solution.  I can't credit for it I found it on another thread.  I just modified the code to fit my application.  Here's the vb.net code.  Didn't have to change anything on the sqlserver side.  Trick was to use the ExecuteScalar() method to execute the stored proc.  Thanks for everybody's help.

    Dinesh

    Code Below:

           Dim objCon As SqlConnection

            Dim objCmd As SqlCommand

            Dim strConn As String

            Dim sqlParam As SqlParameter

            Dim appid As String = Session("appid")

                  

               

                objCon = New SqlConnection

                objCon = mobjmyAppCon.conApp()

               

                objCmd = New SqlCommand

                objCmd.Connection = objCon

                objCmd.CommandText = "spGetText"

                objCmd.CommandType = CommandType.StoredProcedure

                sqlParam = cmm.CreateParameter()

                sqlParam.ParameterName = "@appid"

                sqlParam.Direction = ParameterDirection.Input

                sqlParam.SqlDbType = SqlDbType.UniqueIdentifier

                sqlParam.Size = 16

                sqlParam.Value = New System.Guid(appid)

                'add parameters to the command object

                objCmd.Parameters.Add(sqlParam)

               

                'open connection to db

                objCon.Open()

                txtResText.Text = CStr(objCmd.ExecuteScalar())

    objCon.Close()

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply