468,140 Members | 1,465 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,140 developers. It's quick & easy.

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!

Yama
Nov 18 '05 #1
1 1198
My Dear Newsgroup,

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

"Yama" <yk*****@grandpacificresorts.com> wrote in message
news:uz**************@TK2MSFTNGP09.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

Nov 18 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Pushpendra Vats | last post: by
3 posts views Thread by rcoco | last post: by
27 posts views Thread by didacticone | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.