Solved

UPDATE table SET col1 = col2 not working

Posted on 2008-06-18
15
2,799 Views
Last Modified: 2013-11-23
Hello software engineers,

I'm trying to copy data from one column to another in an Access table using SQL from Delphi and I'm not having any luck.
The following worked in Access:  UPDATE PowerUnits SET [TEMP] = Name;

Tried this and got no error, but no data were copied to the TEMP column:
    with MyQuery do
    begin
      Close;
      SQL.Clear;
      SQL.Add('UPDATE PowerUnits SET [TEMP] = Name');
      ExecSQL;
    end;

Tried this and the column was still not copied:
    DataMod.CommandQuery.CommandText := 'UPDATE PowerUnits SET [TEMP] = Name';
    DataMod.CommandQuery.Execute;

I also tried both of the blocks of code above with no brackets around the TEMP column name. BTW, I know that the TEMP column exists, and the only difference between the two columns is that TEMP has Unicode Compression set to YES and Name does not. They are both CHAR(25).

Help, please. I've been floundering for hours! :)
0
Comment
Question by:reforest
  • 6
  • 2
  • 2
  • +3
15 Comments
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
>DataMod.CommandQuery.CommandText := 'UPDATE PowerUnits SET [TEMP] = Name';
Define 'name'.
0
 

Author Comment

by:reforest
Comment Utility
Name is a CHAR(25) column that already exists in the PowerUnits table and contains data.
0
 
LVL 12

Expert Comment

by:nexusnation
Comment Utility
Name should have brackets too, assuming you're referring to another column in PowerUnits.  The issue is that Name is a reserved keyword in many languages, including (I believe) SQL.
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
nexus is correct, Name is likely a reserved word in every database engine, and therefore (1) you'd need to surround it with square brackets [ ], or better yet (2) Rename (no pun intended) the column something else.
0
 

Author Comment

by:reforest
Comment Utility
Tried the following, and there are still no data copied to my TEMP column:

    DataMod.CommandQuery.CommandText := 'UPDATE PowerUnits SET [TEMP] = [Name]';
    DataMod.CommandQuery.Execute;
0
 
LVL 12

Expert Comment

by:nexusnation
Comment Utility
Your SQL is correct, and Unicode Compression shouldn't play a factor.  I would think, therefore, that your problem is in your code.  Unfortunately, I don't know Delphi.
0
 

Author Comment

by:reforest
Comment Utility
Delphi expert needed. Anyone??
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 6

Expert Comment

by:FactorB
Comment Utility
Few things:
1. Open access file and try to run UPDATE [PowerUnits] SET [PowerUnits].[TEMP] = [PowerUnits].[Name]
2. Check if your Query component (MyQuery) is connected properly and is not in read-only state
3. Is there any peace of code before the one that you gave causing exceptions, consider the option that executable doesn't even reach the Query execution, if there is any code it is recommended to post it here
4. Afterwards it should work:
      MyQuery.Close;
      MyQuery.SQL.Clear;
      MyQuery.SQL.Add('UPDATE [PowerUnits] SET [PowerUnits].[TEMP] = [PowerUnits].[Name]');
      MyQuery.ExecSQL;

Regards B.
0
 
LVL 6

Expert Comment

by:FactorB
Comment Utility
I meant "piece of code" :)
0
 
LVL 36

Expert Comment

by:Geert Gruwez
Comment Utility
Occasionally i come accross a similar problem
Using double quotes solved my problem

UPDATE PowerUnits SET "TEMP" = "Name"
0
 
LVL 13

Expert Comment

by:rfwoolf
Comment Utility
Okay I know delphi rather well, and basically whatever SQL will work in Access will work the same if executed from within Delphi depending on your actual database engine.
In any case you need to eliminate the possibilty of using reserve words - so use quotes as someone mentioned above or refer to your fieldnames as descendants of a table as in FactorB's example:
  MyQuery.SQL.Add('UPDATE [PowerUnits] SET [PowerUnits].[TEMP] = [PowerUnits].[Name]');
or try
MyQuery.SQL.Add('UPDATE PowerUnits SET PowerUnits."TEMP" = PowerUnits."Name")

As for NAME being a reserve word - that would be rather sad because I've used it in about 10 tables - but yes you nonetheless need to eliminate the possibility
0
 

Author Comment

by:reforest
Comment Utility
It's still not working, even with all the great suggestions. I just noticed something that is probably important. When I try to run an UPDATE query within my Access database, the security settings prevent me from running it. When I tested the UPDATE statement in Access and finally got it to work I forgot that I had to change the security settings before I did it. Is this the real problem? If so, how can I change those settings from Delphi? By the way, I can run all kinds of other queries in Delphi code without this problem (ALTER TABLE, ALTER COLUMN, CREATE TABLE, ADD, and SELECT INTO).
0
 

Author Comment

by:reforest
Comment Utility
Increased the points, since I'd really like to figure this out soon...thanks in advance for the help.
0
 
LVL 13

Accepted Solution

by:
rfwoolf earned 350 total points
Comment Utility
The security settings?
What version of access are you using? Vista? (just a joke)
That's strange. But in any case, surely you would get an error in delphi if there was a security problem?
First try and execute the SQL as a query in Access as you have done. When it works, take the exact SQL and try it in Delphi. If you get an error message, report the message. If you get no error message and it works, then try dynamically building the SQL query.
0
 

Author Closing Comment

by:reforest
Comment Utility
I did try to copy the SQL that worked into my Delphi code, but I tried it again, and it worked. The thing I did differently (occurred to me while I was laying in bed last night) was I commented out all the code after it and ran the program without setting a breakpoint in debug mode. Before, I was opening the database to check it just after the EXECSQL because I didn't want to Delete the Name column if the data weren't copied. This is the code that worked:
    with BlankQuery do
    begin
      Close;
      SQL.Clear;
      SQL.Add('UPDATE PowerUnits SET TEMP = Name');
      ExecSQL;
    end;
{code to delete name column and make other db manipulations}

I guess that was it. Thanks for the help everyone.
reforest
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 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

12 Experts available now in Live!

Get 1:1 Help Now