INSERTING TEXT

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!
YamaInsert is simple.
INSERT table_name(int_column, text_column) VALUES(1, ' ... really long text
... ')
Update is a little trickier. In the apps I write, I pull out the whole
value, let the user edit it as a whole, and write the whole value back. As
you might know, you can't append to a TEXT column (so you can't do UPDATE
table_name SET text_column += @.varcharParam), and you can't even manipulate
it locally because you can't have a local variable of type TEXT.
If you are adding things to the end of a text column, you might consider
having a separate related table with comments. That way you can track them
separately, and you don't have to worry about concatenation.
The only time I would ever update is if I have to a batch search and replace
on all rows, e.g. see http://www.aspfaq.com/2445
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Yama" <ykamyar@.grandpacificresorts.com> wrote in message
news:#4zAO0w2DHA.1532@.TK2MSFTNGP10.phx.gbl...
> 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!
> Yama
>|||Hi Aaron,
Are you sure the INSERT will work even with more than 8000 characters?
I thought you had to use the WRITETEXT command?
Yama
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:%23MPf2fy2DHA.2680@.tk2msftngp13.phx.gbl...
> Insert is simple.
> INSERT table_name(int_column, text_column) VALUES(1, ' ... really long
text
> ... ')
> Update is a little trickier. In the apps I write, I pull out the whole
> value, let the user edit it as a whole, and write the whole value back.
As
> you might know, you can't append to a TEXT column (so you can't do UPDATE
> table_name SET text_column += @.varcharParam), and you can't even
manipulate
> it locally because you can't have a local variable of type TEXT.
> If you are adding things to the end of a text column, you might consider
> having a separate related table with comments. That way you can track
them
> separately, and you don't have to worry about concatenation.
> The only time I would ever update is if I have to a batch search and
replace
> on all rows, e.g. see http://www.aspfaq.com/2445
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "Yama" <ykamyar@.grandpacificresorts.com> wrote in message
> news:#4zAO0w2DHA.1532@.TK2MSFTNGP10.phx.gbl...
> > 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!
> >
> > Yama
> >
> >
>|||> Are you sure the INSERT will work even with more than 8000 characters?
Yes, though it depends from where it came. Some providers / clients will
truncate because they don't know how to deal with >255 or >8000.
> I thought you had to use the WRITETEXT command?
I've never used the WRITETEXT command in production systems. www.aspfaq.com
stores articles in a TEXT column and I have no problems using INSERT /
UPDATE through a web interface. All I can suggest is that you keep your
drivers up to date (e.g. MDAC 2.8) and test your environment before taking
my word for it.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||My Dear Friend,
Here is how I settled doing it.
In my ASPX web page I have four fields. A dropdown with a list of all the
letters, a checkbox for activating a letter or setting it to innactive, a
text box for the description of a letter, and another text box for the
letter with multiple line enabled. Hope you like this... :-)
--The table:
CREATE TABLE [dbo].[tblLetter] (
[LetterID] [numeric](9, 0) IDENTITY (1, 1) NOT NULL ,
[Letter] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Description] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Active] [bit] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
--The stored procedure:
CREATE PROCEDURE SaveLetter
@.Description VARCHAR(100) = '',
@.Active BIT = 1,
@.BlobLetter TEXT = '',
@.blnInsert BIT = 0,
@.LetterNumber INT = NULL,
@.blnDelete BIT = 0
AS
DECLARE @.s BINARY(16)
IF @.blnInsert = 1 AND @.blnDelete = 0
BEGIN
BEGIN TRAN
DECLARE @.ID INT
INSERT INTO tblLetter
(Description, Active, Letter) VALUES (@.Description, @.Active, @.BlobLetter)
SET @.ID = @.@.IDENTITY
SELECT @.s = TEXTPTR( Letter )
FROM tblLetter
WHERE LetterID = @.@.IDENTITY
WRITETEXT tblLetter.Letter @.s @.BlobLetter
COMMIT TRAN
END
IF @.blnInsert = 0 AND @.blnDelete = 0
BEGIN
BEGIN TRAN
UPDATE tblLetter
SET Description = @.Description , Active = @.Active
WHERE LetterID = @.LetterNumber
SELECT @.s = TEXTPTR(Letter)
FROM tblLetter
WHERE LetterID = @.LetterNumber
WRITETEXT tblLetter.Letter @.s @.BlobLetter
COMMIT TRAN
END
IF @.blnDelete = 1
BEGIN
DELETE FROM tblLetter
WHERE LetterID = @.LetterNumber
END
GO
Yama Kamyar
Senior Microsoft .NET Consultant
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:%23SAJJZ62DHA.1740@.TK2MSFTNGP09.phx.gbl...
> > Are you sure the INSERT will work even with more than 8000 characters?
> Yes, though it depends from where it came. Some providers / clients will
> truncate because they don't know how to deal with >255 or >8000.
> > I thought you had to use the WRITETEXT command?
> I've never used the WRITETEXT command in production systems.
www.aspfaq.com
> stores articles in a TEXT column and I have no problems using INSERT /
> UPDATE through a web interface. All I can suggest is that you keep your
> drivers up to date (e.g. MDAC 2.8) and test your environment before taking
> my word for it.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>

INSERTING TEXT

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!
YamaInsert is simple.
INSERT table_name(int_column, text_column) VALUES(1, ' ... really long text
... ')
Update is a little trickier. In the apps I write, I pull out the whole
value, let the user edit it as a whole, and write the whole value back. As
you might know, you can't append to a TEXT column (so you can't do UPDATE
table_name SET text_column += @.varcharParam), and you can't even manipulate
it locally because you can't have a local variable of type TEXT.
If you are adding things to the end of a text column, you might consider
having a separate related table with comments. That way you can track them
separately, and you don't have to worry about concatenation.
The only time I would ever update is if I have to a batch search and replace
on all rows, e.g. see http://www.aspfaq.com/2445
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Yama" <ykamyar@.grandpacificresorts.com> wrote in message
news:#4zAO0w2DHA.1532@.TK2MSFTNGP10.phx.gbl...

quote:

---

> Hello,
> Can someone tell me how to insert text into a table containing a TEXT

---

field?

quote:

---

> 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

quote:

---

> but this time to update the TEXT field.
> HELP!
> Yama
>

---

|||Hi Aaron,
Are you sure the INSERT will work even with more than 8000 characters?
I thought you had to use the WRITETEXT command?
Yama
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:%23MPf2fy2DHA.2680@.tk2msftngp13.phx.gbl...

quote:

---

> Insert is simple.
> INSERT table_name(int_column, text_column) VALUES(1, ' ... really long

---

text

quote:

---

> ... ')
> Update is a little trickier. In the apps I write, I pull out the whole
> value, let the user edit it as a whole, and write the whole value back.

---

As

quote:

---

> you might know, you can't append to a TEXT column (so you can't do UPDATE
> table_name SET text_column += @.varcharParam), and you can't even

---

manipulate

quote:

---

> it locally because you can't have a local variable of type TEXT.
> If you are adding things to the end of a text column, you might consider
> having a separate related table with comments. That way you can track

---

them

quote:

---

> separately, and you don't have to worry about concatenation.
> The only time I would ever update is if I have to a batch search and

---

replace

quote:

---

> on all rows, e.g. see http://www.aspfaq.com/2445
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "Yama" <ykamyar@.grandpacificresorts.com> wrote in message
> news:#4zAO0w2DHA.1532@.TK2MSFTNGP10.phx.gbl...
> field?
> procedure
>

---

|||> Are you sure the INSERT will work even with more than 8000 characters?
Yes, though it depends from where it came. Some providers / clients will
truncate because they don't know how to deal with >255 or >8000.

quote:

---

> I thought you had to use the WRITETEXT command?

---

I've never used the WRITETEXT command in production systems. www.aspfaq.com
stores articles in a TEXT column and I have no problems using INSERT /
UPDATE through a web interface. All I can suggest is that you keep your
drivers up to date (e.g. MDAC 2.8) and test your environment before taking
my word for it.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||My Dear Friend,
Here is how I settled doing it.
In my ASPX web page I have four fields. A dropdown with a list of all the
letters, a checkbox for activating a letter or setting it to innactive, a
text box for the description of a letter, and another text box for the
letter with multiple line enabled. Hope you like this... :-)
--The table:
CREATE TABLE [dbo].[tblLetter] (
[LetterID] [numeric](9, 0) IDENTITY (1, 1) NOT NULL ,
[Letter] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Description] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Active] [bit] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
--The stored procedure:
CREATE PROCEDURE SaveLetter
@.Description VARCHAR(100) = '',
@.Active BIT = 1,
@.BlobLetter TEXT = '',
@.blnInsert BIT = 0,
@.LetterNumber INT = NULL,
@.blnDelete BIT = 0
AS
DECLARE @.s BINARY(16)
IF @.blnInsert = 1 AND @.blnDelete = 0
BEGIN
BEGIN TRAN
DECLARE @.ID INT
INSERT INTO tblLetter
(Description, Active, Letter) VALUES (@.Description, @.Active, @.BlobLetter)
SET @.ID = @.@.IDENTITY
SELECT @.s = TEXTPTR( Letter )
FROM tblLetter
WHERE LetterID = @.@.IDENTITY
WRITETEXT tblLetter.Letter @.s @.BlobLetter
COMMIT TRAN
END
IF @.blnInsert = 0 AND @.blnDelete = 0
BEGIN
BEGIN TRAN
UPDATE tblLetter
SET Description = @.Description , Active = @.Active
WHERE LetterID = @.LetterNumber
SELECT @.s = TEXTPTR(Letter)
FROM tblLetter
WHERE LetterID = @.LetterNumber
WRITETEXT tblLetter.Letter @.s @.BlobLetter
COMMIT TRAN
END
IF @.blnDelete = 1
BEGIN
DELETE FROM tblLetter
WHERE LetterID = @.LetterNumber
END
GO
Yama Kamyar
Senior Microsoft .NET Consultant
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:%23SAJJZ62DHA.1740@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Yes, though it depends from where it came. Some providers / clients will
> truncate because they don't know how to deal with >255 or >8000.
>
> I've never used the WRITETEXT command in production systems.

---

www.aspfaq.com

quote:

---

> stores articles in a TEXT column and I have no problems using INSERT /
> UPDATE through a web interface. All I can suggest is that you keep your
> drivers up to date (e.g. MDAC 2.8) and test your environment before taking
> my word for it.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>

---