Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5846
  • Last Modified:

ADD FIELD SET A DEFAULT VALUE SQL

Hi Geniuses, Gurus, Experts, et al.,
I want to make sure I understand. There was a question answered before about setting default values when creating a table. It was closed and the expert said there was a M$ bug. Are you saying there is no way to create a column that contains a default value when programming in Delphi and working with an Access 2000 database? I tried the following and it didn't work:

with MyQuery do
begin
    Close;
    SQL.Clear;
    SQL.Add('ALTER TABLE ' + TableName + ' ');
    SQL.Add('ADD COLUMN Deleted CHAR(1) DEFAULT ''N''');
    ExecSQL;
  end;

Help please.
0
reforest
Asked:
reforest
1 Solution
 
JohnjcesCommented:
One problem as I see it is you should be using an ADOCommand. Works with Access fine.

Here is an example...

 ADOCommand1.CommandText := 'Alter Table CDLib ADD CDKey Text(50)';
 ADOCommand1.Execute;
 ADOCommand1.CommandText := 'Alter Table CDLib ADD Comments Memo';
 ADOCommand1.Execute;

You can then add your default text as you did above.

I also would use quoted string. I feel it makes quoted strings in strings easier and more fool proff.:

SQL.Add('ADD COLUMN Deleted CHAR(1) DEFAULT ' + QuotedStr('N'));

Hope this helped.

John
0
 
rfwoolfCommented:
"I tried it and it didn't work"
-please provide details as to in what way it did not work, such as any error messages or the results.

Just a minor note about the field "Deleted" - I don't think it's a reserve word but just in case it is you may want to put it in quotes or show it as a descendant of a table
Example
SELECT MyTable.Deleted  FROM MyTable
0
 
bokistCommented:
Hi,

Try this way :
SQL.Add('ADD COLUMN Deleted CHAR(1) DEFAULT ''N'' with values');
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
reforestAuthor Commented:
The following code created the column, but did not put any values into rows that already contained data, and the last row (when viewed in Access) had #Error in the Deleted column:

DataMod.CommandQuery.CommandText := 'ALTER TABLE Chemicals ADD COLUMN Deleted CHAR(1) DEFAULT ' + QuotedStr('N');
DataMod.CommandQuery.Execute;

The following code caused an error that said 'Syntax error in ALTER TABLE statement':
with MyQuery do
  begin
    Close;
    SQL.Clear;
    SQL.Add('ALTER TABLE Chemicals ADD COLUMN Deleted CHAR(1) DEFAULT ''N'' with values');
    ExecSQL;
  end

So, still nothing that adds 'N' to my new column. Thanks for the help, but more ideas needed.
0
 
nico5038Commented:
I'm afraid that the CREATE TABLE statement of the Jet-engine (the access database engine) doesn't support default values.
When you're able to open the Access Tables collection from Delphi with code, then a default value can be added. I used VBA code in the past to manipulate table field properties, but that's "within" Access and not in the database engine....

Nic;o)
0
 
reforestAuthor Commented:
Thanks for the help. It always stinks when the answer is, 'You can't do it the easy way'. Oh well. I've figured out a workaround now.
0
 
nico5038Commented:
Glad I could help, but now I'm curious how your work around is implemented....
I guess you use an UPDATE query or add the Default value to the form field before the INSERT.

Nic;o)
0
 
reforestAuthor Commented:
Actually, it's not anything interesting. I found a way to do what I wanted to do without entering a default value at all. I'm such a beginner that I was having trouble trying to select rows where MyTextField = ''. I thought if I added a default, then I could SELECT * from MyTable WHERE MyTextFIeld = 'Default'. I now know that I need to SELECT * from MyTable WHERE MyTextFIeld Is Null.

BTW, I still have no idea how to rename columns or copy data from one column to another within the same table...want me to submit a new question?
0
 
nico5038Commented:
No need for a new question, as I'm not "in for the points" :-)

To copy data from column to column use:

        UPDATE tblA SET fieldA = FieldB

Without a WHERE clause all rows will be effected. (Always try first on a backup copy of the table when manipulating data in tables!)

Renaming is done with the "AS" predicate like:

        SELECT fieldA AS Column1 from tblA

This will give one column named "Column1" in the output of the query.

Success with your application !

Nic;o)
0
 
reforestAuthor Commented:
Thanks for the help, but I'm still having trouble.

Before you sent this reply I opened a question titled 'UPDATE table SET col1 = col2 not working', because I found something on the internet about copying columns in SQL. The question is still open, though, because it's not working in Delphi. It works if I open my Access database and run the UPDATE query, but not when I try to do it in code from Delphi.

I have successfully run other types of queries (SELECT INTO, ALTER TABLE, ALTER COLUMN, etc.), so I know how it works most of the time. It's just not working for my UPDATE query.

Do you program in Delphi?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now