INserting Text

  • Thread starter Thread starter Yama
  • Start date Start date
Y

Yama

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
 
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
 
Back
Top