Solved

Help with update query

Posted on 2007-03-23
5
158 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 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
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
Comment Utility
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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now