Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jan 2005
    Posts
    165

    Unanswered: Append string to Text field....

    I am trying to append some text to a full text field in SQL Server 2000. I tried this; but, it didn't work:

    Code:
    UPDATE DefendantEventPros SET EventComment=EventComment + ' This event was completed on "& Date() &".' WHERE EventNumber="& eventnumber &"
    I get an error saying:

    [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid operator for data type. Operator equals add, type equals text.

    How can I append some text to the end of a field? Thanks for any help!

  2. #2
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    UPDATE DefendatEventPros
    SET EventComment= (SELECT EventComment + ' This event was completed ON "' + GETDATE() + '".' WHERE EventNumber=' + @eventnumber + ')

    ?
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  3. #3
    Join Date
    Jan 2005
    Posts
    165
    Thanks; but, i get the same error as my first one.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    UPDATE DefendantEventPros
    SET    EventComment = EventComment +
             ' This event was completed on ' + Convert(varchar(20), GetDate()) + '.'
    WHERE  EventNumber = 1
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2005
    Posts
    165
    I think including the date has caused some confusion. I was including the ASP date function to insert the date. I'll leave that out for now.

    Here is my new code:

    Code:
    UPDATE DefendantEventPros SET EventComment=EventComment + ' This event has been completed.' WHERE EventNumber=1234
    The EventComment field is a Text(16) field.
    Last edited by bla4free; 12-10-07 at 17:07.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what is a TEXT(2) field?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2005
    Posts
    165
    Quote Originally Posted by r937
    what is a TEXT(2) field?
    I'm sorry. I meant Text(16)

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    TEXT(16)????

    would you mind scripting out your table into a CREATE TABLE statement?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jan 2005
    Posts
    165
    I was referring to its length. I guess i wrote it wrong.

    Code:
    [EventComment] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, try this --

    UPDATE DefendantEventPros
    SET EventComment = cast(EventComment as varchar(7000)) + ' This event was completed '
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Quote Originally Posted by r937
    okay, try this --

    UPDATE DefendantEventPros
    SET EventComment = cast(EventComment as varchar(7000)) + ' This event was completed '
    just don't run it 7000 times.

  12. #12
    Join Date
    Jan 2005
    Posts
    165
    I ran a len query on my eventcomment field and i had several that were over 8000 characters. isn't varchar's limit 8000? i guess i'll just tell my code to ignore anything over that long to keep things simple. thanks for all your help!

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    in sql server 2000, the limit on varchar is 8000

    but you said EventComment was TEXT

    and you're planning to arbitrarily ignore the parts that are too long???
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Jan 2005
    Posts
    165
    I guess I'll just ignore records with 8000+ characters and put some limits in our application. Maybe we can modify our process of how event comments are managed. Thanks again for your help!

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Now there's a solution!
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •