Solved

UPDATE table SET col1 = col2 not working

Posted on 2008-06-18
15
2,812 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
  • 2
  • +3
15 Comments
 
LVL 65

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 65

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 37

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 350 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Outlook VBA question - How do I mark a message as read 2 60
Access Form - Button Format Issue 13 50
Access Query function 4 52
2 Global Vars, 1 List Box 4 34
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

759 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