Updating a text field in SQL Server

[threepins]

Hi,

I have a website using a SQL Server database to store all of it's data.
Retrieving data and writing basic data back is fine, however, when i go
to update one table that has a text field in it the update fails if
the amount of data being passed to the text field is too large.

Is there a way around this or a particular update i should be using?

Any information would be greatly appreciated.

Cheers
Bj

[Vinod Kumar]

Is the text data beyong 2GB? How large is the update?

If that being the case, this is a limitation you need to work with.

--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

<th*******@gmail.com> wrote in message
news:11**********************@c13g2000cwb.googlegr oups.com...

Hi,

I have a website using a SQL Server database to store all of it's data.
Retrieving data and writing basic data back is fine, however, when i go
to update one table that has a text field in it the update fails if
the amount of data being passed to the text field is too large.

Is there a way around this or a particular update i should be using?

Any information would be greatly appreciated.

Cheers
Bj

[threepins]

Hi Vinod,

The amount of data being passed to the update is on average about 2 or
3 paragraphs worth of data (in the one i was trying to insert there was
about 1000+ characters including whitespace).

Am i using the wrong driver or provider in my connection statement?
(connection string is: oConn.ConnectionString =
"Provider=SQLOLEDB.1;User ID=sa;PWD=password;Initial
Catalog=databasename;Data Source=machinename")

Cheers for any help you can give me further

Bj

[Gregory Dean]

On 2/14/05 6:34 AM, in article
11**********************@c13g2000cwb.googlegroups. com, "th*******@gmail.com"
<th*******@gmail.com> wrote:

Hi Vinod,

The amount of data being passed to the update is on average about 2 or
3 paragraphs worth of data (in the one i was trying to insert there was
about 1000+ characters including whitespace).

Am i using the wrong driver or provider in my connection statement?
(connection string is: oConn.ConnectionString =
"Provider=SQLOLEDB.1;User ID=sa;PWD=password;Initial
Catalog=databasename;Data Source=machinename")

Cheers for any help you can give me further

Bj

Typically, you add a javascript to your web page form field that limits the
number of characters entered to match the size of your field in the table.
It would help if you posted the error message. I would guess that it is a
warning that the data will be truncated.

-Greg

[threepins]

Hi Gregory,

I don't want to truncate it, i would like the whole data to be passed
through and inserted into the field. The field is of type TEXT (which
stores up to 2gig right?) so it should hold the amount of text i'm
wanting to insert. I think it's just a limitation of ADO and passing
that much data as a string possibly....

The error message is:

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
The identifier that starts with 'Long list of text that i'm trying to
insert' is too long. Maximum length is 128.

Cheers
Bj

[Erland Sommarskog]

(th*******@gmail.com) writes:

I have a website using a SQL Server database to store all of it's data.
Retrieving data and writing basic data back is fine, however, when i go
to update one table that has a text field in it the update fails if
the amount of data being passed to the text field is too large.

Is there a way around this or a particular update i should be using?

Any information would be greatly appreciated.

Is that text as in the datatype text, or just a varchar column?

In any case, you should specify "fails". That is, do you get an error
message or what? (And please post any error messages.)
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

(th*******@gmail.com) writes:

I don't want to truncate it, i would like the whole data to be passed
through and inserted into the field. The field is of type TEXT (which
stores up to 2gig right?) so it should hold the amount of text i'm
wanting to insert. I think it's just a limitation of ADO and passing
that much data as a string possibly....

The error message is:

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
The identifier that starts with 'Long list of text that i'm trying to
insert' is too long. Maximum length is 128.

This is because you are delimiting a string literal with double
quotes. SQL Server uses single quotes for string delimiters. Double
quotes are for delimiting identifiers with funny characters in the
them.

And by the way, you should be passing you strings as parameters to
a command. If you send the SQL string, you will need to handle
embedded quotes.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[threepins]

Hi Erland,

<quote>
And by the way, you should be passing you strings as parameters to
a command
</quote>

How do i do this? Can you provide an example i can build on?

Cheers
Bj

[Gregory Dean]

On 2/14/05 7:31 PM, in article
11**********************@o13g2000cwo.googlegroups. com, "th*******@gmail.com"
<th*******@gmail.com> wrote:

Hi Erland,

<quote>
And by the way, you should be passing you strings as parameters to
a command
</quote>

How do i do this? Can you provide an example i can build on?

Cheers
Bj

Here is a link to a page with several examples of entering large amounts of
data from a web form into a SQL database. One of the examples shows the
command method.

http://authors.aspalliance.com/steve...les/sprocs.asp

-Greg