OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

[solved]How to Insert a new field in design view?

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
hwtan
OOo Advocate
OOo Advocate


Joined: 24 Aug 2004
Posts: 320
Location: Singapore

PostPosted: Sun Mar 30, 2008 11:57 pm    Post subject: [solved]How to Insert a new field in design view? Reply with quote

I would like to insert a new field in a saved table. However, when I edit a table in the design view, it is not possible to insert a new field in between two existing field? Is there any way to achieve that?

Last edited by hwtan on Mon Mar 31, 2008 9:37 pm; edited 1 time in total
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Mon Mar 31, 2008 7:56 pm    Post subject: Reply with quote

hwtan:

You asked:
hwtan wrote:
I would like to insert a new field in a saved table. However, when I edit a table in the design view, it is not possible to insert a new field in between two existing field? Is there any way to achieve that?

Yes, it is possible to insert a new field (column) in between ( before ) an exsiting field (column ).

But, since it is a database . . . the ORDER of the fields ( columns ) should make NO difference.

However, if you are insistent on knowing . . . I will assume . . . you are using the Embedded OpenOffice database . . . HSQL . . . and . . . you can confirm this by looking at the status bar ( bottom ) of your opened database.

According to HSQL documentation at: http://www.hsqldb.org/doc/guide/ch09.html#alter_table-section

Alter Table: http://www.hsqldb.org/doc/guide/ch09.html#alter_table-section wrote:

ALTER TABLE

ALTER TABLE <tablename> ADD [COLUMN] <columnname> Datatype
[(columnSize[,precision])] [{DEFAULT <defaultValue> |
GENERATED BY DEFAULT AS IDENTITY (START WITH <n>[, INCREMENT BY <m>])}] |
[[NOT] NULL] [IDENTITY] [PRIMARY KEY]
[BEFORE <existingcolumn>];

Adds the column to the end of the column list. The optional BEFORE <existingcolumn> can be used to specify the name of an existing column so that the new column is inserted in a position just before the <existingcolumn>.

So, for example . . . let us assume . . . I have a Table with a name of My_Table and it contains a column ( field ) by the name of Country and you want to ADD a column ( field ) of City as VARCHAR(40) BEFORE Country . . . in this case:
  1. Open your database file
  2. From the Menu: Tools -> SQL...
  3. Under Command to execute . . . enter the following SQL:
    Code:
    ALTER TABLE "My_Table" ADD COLUMN "City" VARCHAR(40) BEFORE "Country";

  4. Press Execute button
  5. Command successfully executed. should be displayed.
  6. Press Close button
  7. From the Menu: View -> Refresh Tables

Of course, change:
  • "My_Table"
  • "Country"
  • "City"
to the names in your database.

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your first post title (edit button) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
hwtan
OOo Advocate
OOo Advocate


Joined: 24 Aug 2004
Posts: 320
Location: Singapore

PostPosted: Mon Mar 31, 2008 9:37 pm    Post subject: Reply with quote

Thank you sliderrule. Although it is not as friendly as I would expect, it is a solution any way. Thanks once again.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group