473,836 Members | 1,510 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Inserting / Updating LongText Field using WriteText?

Hi, I'm trying to store large strings to a database, so am using the
text field type (LongText). I have used this before when storing the
html of a webpage, and was able to store more than 255 characters by
using just a normal update sql statement. Now I'm trying to store the
body of research papers, and must be doing something different, as I
can only store 255 characters.

Can someone explain why SQL Server doesn't like what I am doing -
should I be using the WriteText / UpdateText function? If so, please
explain by example how I would do that, and why doing that works.

Thanks so much,
Iain
Jul 20 '05 #1
5 5296
Iain Porter (st***@intraspi n.com) writes:
Hi, I'm trying to store large strings to a database, so am using the
text field type (LongText). I have used this before when storing the
html of a webpage, and was able to store more than 255 characters by
using just a normal update sql statement. Now I'm trying to store the
body of research papers, and must be doing something different, as I
can only store 255 characters.

Can someone explain why SQL Server doesn't like what I am doing -
should I be using the WriteText / UpdateText function? If so, please
explain by example how I would do that, and why doing that works.


You need to tell us of what you are doing and how you are diagnosing
that you only store 255 characters. Did you check datalength() for
the column?

A common trap is that the default output in Query Analyzer is 255 chars;
this is an option in QA that you can change under Tools.

If this does not help, you need to supply more information. Code snippets,
and table defintions are welcome.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2
Apologies for the lack of detail. The problem is apparent because
when selecting the longtext feild, using READTEXT as below, the text
data cuts off after 255. The problem could be here, getting data out -
I'm not convinced I have the code right, I just think it's in the
getting data in.

Thanks very much for your help, let me know if you need different
info,
Iain

Code samples:

To get data out from the longtext column(content) by id:
javascript:
try {
conn.Open(strCo nn);
cmd.ActiveConne ction = conn;
cmd.CommandText = "SelectContent" ;
cmd.CommandType = adCmdStoredProc ;
cmd.Parameters. Append(cmd.Crea teParameter("@i d", adInteger,
adParamInput, 4, id));
contentRS = cmd.Execute();

var content = "";
while (!contentRS.EOF ) {
content += contentRS("cont ent");
contentRS.MoveN ext;
}
}
....
return content;

SQL (SelectContent) :
SET NOCOUNT ON

DECLARE
@txtptrval VARBINARY(16),
@startOffset INT,
@readLength INT,
@datalength INT

SELECT @txtptrval = TEXTPTR(content ) FROM papers WHERE id=@id
SELECT @startOffset = 0
SELECT @readLength = 255
SELECT @datalength = DATALENGTH(cont ent) FROM papers WHERE id=@id

-- If last chunk, reduce buffer size to the nChars remaining
IF ((@startOffset + @readLength) > @datalength) (
SELECT @readLength = @datalength - @startOffset
)

WHILE (@startOffset < @datalength)
BEGIN
READTEXT papers.content @txtptrval @startOffset @readLength
SELECT @startOffset = @startOffset + @readLength

-- Last chunk, reduce buffer size to the get the last nChars
remaining
IF (@startOffset + @readLength) > @datalength
SELECT @readLength = @datalength - @startOffset
END
SET NOCOUNT OFF
END
---------------------------------------------------------------------

To get data in:

CREATE proc InsertPaper
@authors nvarchar(20),
@title nvarchar(255),
@pubyear int,
@journal nvarchar(255),
@issue int,
@pages nvarchar (13),
@paperabstract text,
@content text,
@id int OUTPUT

as

insert into Papers(authors, title, pubyear, journal, issue, pages,
abstract, content)
values (@authors, @title, @pubyear, @journal, @issue, @pages,
@paperabstract, @content)

select @id = @@identity
GO
--------------------------------------------------------
Jul 20 '05 #3
Iain Porter (st***@intraspi n.com) writes:
Apologies for the lack of detail. The problem is apparent because
when selecting the longtext feild, using READTEXT as below, the text
data cuts off after 255. The problem could be here, getting data out -
I'm not convinced I have the code right, I just think it's in the
getting data in.

Thanks very much for your help, let me know if you need different
info,
When I run a modified version of you script, I seem to get all the data in
the text column, sliced in pieces of 255 chars at a time. So the SQL seems
to be OK.

The problem is like to be in the Javascript code:
while (!contentRS.EOF ) {
content += contentRS("cont ent");
contentRS.MoveN ext;
}


You are assuming that you have one result set with all the slices of
the text column. But you have one result set for each slice, instead
of MoveNext, you should have:

contentsRS = contentsRS.Next Recordset

and the stop condition should be on whether contentsRS is a valid
object or not.

Disclaimer: I have no experience or knowledge of Javascript programming.
I am assuming that you are using ADO, because it looks like ADO.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #4
Erland, I'm sorry I've taken so long to come back to you - I didn't
get an email through alerting me to your post, not sure why - thank
you so much for responding again.

I've updated my code as you suggested, but unfortunately get the same
result. My change is listed below:

var content = "";
// while (!contentRS.EOF ) {
// content += contentRS("cont ent");
// contentRS.MoveN ext;
// }

while (contentRS.Stat e != 0) { // 0 means adStateClosed
content += contentRS("cont ent");
contentRS.NextR ecordset;
}

I'm at a loss as to why it's not working - any further thoughts? Is
there a way to count the number of RecordSets returned and reference
each by number or something? I can't see anything like that in a ADO
RecordSet Object Reference.

Thanks very much, I'll check back sooner this time.
Iain
Jul 20 '05 #5
Hi!! I figured it out - thanks so much for your help. The solution,
for anyone searching, is that you have to set the recordset to the
recordset's NextRecordSet:

contentRS = contentRS.NextR ecordSet

Thanks again Erland,
Iain
Jul 20 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
1467
by: AP | last post by:
hi, Table trp contains a field called infotekst (type=LONGTEXT). The php script does show the content of this field infotekst properly while the amount of characters is less the 255 (?). But if it has more than 255 characters than it shows nothing!! What can be wrong in this script or while working with LONGTEXT fields? br,
16
22521
by: Michael Walton | last post by:
I am trying to write some code that inserts or updates a text field in my SQL Server 2000 database. The SQL statement is created using a submit from a form, and the text is coming from a <textarea>, and therefore being placed into the SQL statement via a Request("field"). However, due to limitations in SQL Server 2000 and text fields, I can not use a simple Update or Insert command with text over 8000 bytes. Does anybody have any...
2
912
by: Adrian Majchrzak | last post by:
Hi I have a little problem with my database and php. In one of my tables i've creted field of type Text. When i try to put some larger than 255 characters to this field from php, that doesnt work. Script dont want to put text to my table, strange. I try to chande type of this field to MediumText and LongText and result is the same. This script send only max 255 characters by field Textarea. Is this a problem whith my database or php...
1
5968
by: Srinadh | last post by:
Hi all, We have files with about 20 to 30 fields per row. We are trying to update such files with about 60 rows as contiguous data in a CLOB field. It passes through. But when we try updating files with about 60 to 200 rows, we get the
1
1372
by: Yama | last post by:
Hello, Can someone tell me how to insert text into a table containing a TEXT field? How to update? I would like to create a stored procedure that take a text parameter and inserts it into a new column in my table. Then do the same stored procedure but this time to update the TEXT field. HELP!
7
8980
by: diffuser78 | last post by:
I recently tried a hand at wxGlade and was happy to see it designs a GUI for you in minutes. I am a newbie Python coder. I am not completely aware of GUI programming. I can easily make menubars etc but I am not too sure sure how to get more windows poppping to get more information. I mean when you click something, a new windows would open and ask for input like that. Since I am using wxGlade and wxPython....can you suggest me a quick...
0
1866
by: Melanie | last post by:
Hi - my problem is as follows: I'm writing an ASP.Net 2.0 app with C#. From the app, I call a stored procedure that updates some data in the app's database (SQL Server 2000). This stored procedure then declares and assigns a value to a variable of type nvarchar(4000) and calls a 2nd stored procedure, passing it, among other things, the newly created variable. The 2nd stored procedure performs some updates to a different SQL Server 2000...
4
2964
by: =?iso-8859-1?B?aWFuYXLp?= | last post by:
hey all, I'm trying to get real time updates of batch file output. Here is my batch file: @echo off echo 1 @ping 127.0.0.1 -n 2 -w 1500 nul echo 2 @ping 127.0.0.1 -n 2 -w 1500 nul echo 3
2
2975
by: John Kotuby | last post by:
Hi all, Maybe this belongs in the Full Text group but I am writing an ASP.NET application with a SQL Server 2005 backend, so I am posing the question here. I have been using fulltext search on a text field for a while because originally the field was only being updated nightly and I could repopulate/rebuild the index nightly. Now I will be allowing users to Update the field in real-time. I am using a simple Update SQL statement to...
0
9827
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9677
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10862
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10607
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9389
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
6986
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5662
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5838
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4468
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.