Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

UPDATE table SET col1 = col2 not working

Posted on 2008-06-18
15
Medium Priority
?
2,826 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 66

Expert Comment

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

Author Comment

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

Expert Comment

by:nexusnation
ID: 21816235
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 66

Expert Comment

by:Jim Horn
ID: 21816263
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
ID: 21816327
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
ID: 21816828
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
ID: 21816887
Delphi expert needed. Anyone??
0
 
LVL 6

Expert Comment

by:FactorB
ID: 21819529
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
ID: 21819560
I meant "piece of code" :)
0
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 21820253
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
ID: 21822008
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
ID: 21828094
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
ID: 21828100
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 1050 total points
ID: 21828176
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
ID: 31468541
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

963 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