Solved

Help with update query

Posted on 2007-03-23
5
162 Views
Last Modified: 2010-03-20
Hello,

I have a particular field in a sql database that is text and I went to use it to log information.  So what I am asking is what is the syntax for adding lines to existing data in a field?  How would I write the update query to add text the the data in the existing field so it can appear as a log of events.  Thanks your help.
0
Comment
Question by:2326ac
  • 3
  • 2
5 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 18778580
what database?

most of the times:
UPDATE yourtable
  SET yourfield = yourfield & "new data"

the & could be + or ||  ... depends on the database type you are using.

note however that when really want to make a kind of "log", you should make each log entry 1 row in a log table.
0
 

Author Comment

by:2326ac
ID: 18779057
Thank you I have tried your suggestion - see below:  

I get an error at cmd,executeNonQuery

Invalid Opertator for data type.  Operator equals add, type equals text.

Dim cmd As New SqlCommand("Update tblReqMain set Status = 'Approved', LManComments = '" + StrCom + "', ProbCat = '" + strProbCat + "', AuthName = '" + LbUsername.Text + "', History = History + '*** Authorised by line manager' Where ReqID = " & StrID, New SqlConnection(strconn))

All fields are either Text or VarChar
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18779115
if the field is TEXT (so you are using SQL Server), that is the problem, as you CANNOT update TEXT/NTEXT fields like that. you have to use UPDATETEXT command to update that field (the other fields can be updated normally!)

as noted above, using this as "log" is a conceptual and practical BAD idea, you should not do it that way.

if you have SQL Server 2005, you could use a workaround by using VARCHAR(MAX) instead of TEXT, then the update will work that way. (using + instead of & )
0
 

Author Comment

by:2326ac
ID: 18779922
There will only be a max three actions in this log of about a few lines each.  Thank you all for your advice.  I have tried the suggestion of updatetext (see below) but I get a syntax error near the value of strcom

Dim cmd2 As New SqlCommand("UPDATETEXT tblReqMain.history '" + StrCom + "' WHERE ReqID = " & StrID, New SqlConnection(strconn))
        cmd2.Connection.Open()
        cmd2.ExecuteNonQuery()
        cmd2.Connection.Close()

Any advice would be great
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18780404
you did not read the sample in the documentation, did you?
http://msdn2.microsoft.com/en-us/library/aa260658(SQL.80).aspx
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question