PLEASE HELP!!!

Question

PLEASE HELP!!!

Dinesh Ranasinghe

Valued Member

Points: 70
More actions
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

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

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

Antares686 SSC Guru Points: 125444 More actions · Answer 1

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.

philcart SSC-Forever Points: 47796 More actions · Answer 2

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

Dinesh Ranasinghe Valued Member Points: 70 More actions · Answer 3

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

philcart SSC-Forever Points: 47796 More actions · Answer 4

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

Dinesh Ranasinghe Valued Member Points: 70 More actions · Answer 5

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

philcart SSC-Forever Points: 47796 More actions · Answer 6

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

Dinesh Ranasinghe Valued Member Points: 70 More actions · Answer 7

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

Antares686 SSC Guru Points: 125444 More actions · Answer 8

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.

Dinesh Ranasinghe Valued Member Points: 70 More actions · Answer 9

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()