returning text data from SQL Server to ASP.NET page

[Rod Snyder]

I'm trying to set up an asp.net (vb.net) that will allow a user to
insert/update a bio/profile. I wanted to create a SQL Server 2000 table that
includes their contact info and bio info. However, I'm not getting SQL
Server to accept more than about two paragraphs of info. I've tried the
datatypes of ntext and nvar char to no success. I'm presuming most people
will be cutting and pasting from a Word document also.

Does anyone have any suggestions re: the datatype issue and also the
formating of the text when it comes out of SQL Server and into the ASP.NET
page. I would imagine this is a fairly common thing to do, but I haven't
been able to find anything in the documentation about it.

Also, I up to other ways also. Like is there a way to have them upload a
text file and pull the info out of the text file and somehow present it on
the ASP.NET page?

Any help or direction would be greatly appreciated.

Rod

 

[Chip]

You can use a varchar(8000) which is considerably more than 2 paragraphs.
You might check how you're passing the parameter to your stored procedure.
If you don't specify how many characters the variable is, it defaults to
255.

parmListName.Direction = ParameterDirection.Output
parmTitle = cmd.Parameters.Add("@title", SqlDbType.VarChar)
parmTitle.Size = 100

Uploading a file is also pretty easy in .NET. Using the IDE, it's an HTML
control: "File Field".
Once it's uploaded to a temp directory, you can read the file in and
manipulate it and display it.

Chip

 

[Hermit Dave]

ntext is the most optimised way to store large amout of textual information.
if you are using ntext then you wont be able to use stored procs output
parameter to return the value.
instead you will have to return a SELECT Query which can be read using a
DataReader or a DataSet.

plus max length for a data row in a particular table is limited to 8080. so
unless all other columns have total capacity of 80 bytes it would chop of
the rest.

 

[rod snyder]

Chip:
Do you have any more detail on the "reading the file in". What should
I look for in the documentation to be able to read it in (into what) and
how to present it on an asp.net page.
Rod

 

[Steven Cheng[MSFT]]

Hi Rod,


Thanks for posting in the community! My name is Steven, and I'll be
assisting you on this issue.
From your description, you want to let user input large paragraphs of text
and then stored into SQLServer
DataBase. Also, you'd like to retrieve the data out later and display in a
certain ASP.NET web page, yes?
If there is anything I misunderstood, please feel free to let me know.

As for this problem, here is my suggestions on it:
First , about the data type use to store the file content. I think the
text/ntext data type is the proper one since text/ntext type are particular
designed for store large amount of text information. It is highly optimised
dependent on the actual data's size.
If you use char/varchar, you need to definitly specify the size which'll
cause the waste of the space.
As for the proper way to store the text content from web page into DataBase
and also retrieve it out and display, I think you may use the DataReader
Component in ADO.NET as Hermit suggested. The DataReader can help execute a
certain insert or query statement. Then you just pass the text content as
the input parameter of the insert statement and when retrieving it out, you
just query the certain text/ntext type column and output it to a certain
label on the ASP.NET web page. Do you think so?

In addtion, instead of using text type, you could also first convert the
text content into byte arrays and then store it as binary large object
(BLOB) data to Microsoft SQL Server. Just like store other binary datas
such as images or rich documents...
Here is a KB article on: #HOW TO: Read and Write BLOB Data by Using ADO.NET
Through ASP.NET
http://support.microsoft.com/?id=326502
You may have a view to see whether it helps you.

Further more, I 've also searched some kb articles which have discussed on
some know issues or limitations on using Text/ntext type as input or output
param of store procedure with ADO.NET, you may also have a check to see
whether they'll help you:

#PRB: Stored Procedure with Text (BLOB) Input Returns Null Output
http://support.microsoft.com/?id=178445

#PRB: Text Output Parameter Empty with Unicode Build
http://support.microsoft.com/?id=216196

Please consider the above suggestions. If you have any questions, please
feel free to post here.


Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[Steven Cheng[MSFT]]

Hi Rod,


Have you had a chance to tried out the suggestions in my last reply or
have you got any progress on this problem?
If you need any further help, please feel free to post here.


Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[Steven Cheng[MSFT]]

Hi Rod,


In the former reply, I said that "The DataReader can help execute a certain
insert or query statement.", I'm sorry that I've made a mistake, it should
be "The SqlCommand or OleCommand object can help execute a certain sql
statement". And as for the "how to read in a file from ASP.NET web page"
you mentioned, do you mean how to upload a file to serverside via a ASP.NET
web page? If so, here is a kb article focus on how to upload a file to
serverside in ASP.NET web application:

#HOW TO: Upload a File to a Web Server in ASP.NET by Using Visual Basic .NET
http://support.microsoft.com/?id=323245

#HOW TO: Upload a File to a Web Server in ASP.NET by Using Visual C# .NET
http://support.microsoft.com/?id=323246

Please check out the above items. In the mean time, if you have any
questions or got any progress on this issue, please feel free to post here.



Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[pmd_areef]

Hi Steve,

I have a similar problem to the one discussed below, which we suspect to be a limitation from ADO.Net Parameter object.

1. My requirement is to store large data in one data column in SQL Server 2005. As the data could be large, I have picked up NText column.
2. Now, I am trying to insert data into this column from ADO.Net using the following code snippet.

SqlParameter prmSD_Info = new SqlParameter();
prmSD_Info.SourceColumn = "AA_Info";
prmSD_Info.ParameterName = "@AA_Info";
prmSD_Info.SqlDbType = SqlDbType.NText;
prmSD_Info.Size = 1073741823;

3. If we observe in here, the datalength of Parameter object is actually int. So, I get an exception at the last line as it is larger than 65K + data length.

Do you have any idea if this is a limitation in ADO.Net that we cannot insert more than 65K+ data into NText column using ADO.Net? If this is the case, then do you have any idea what will be the other alternatives for me on this issue?

Thanks in Advance,
Areef