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

auto value problem

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
thedrone
General User
General User


Joined: 09 May 2008
Posts: 6

PostPosted: Fri May 09, 2008 9:37 am    Post subject: auto value problem Reply with quote

Hi, I'm quite new to using oo base, but have managed, not without some cursing, to make a relational database with queries forms etc. that is just about ok. However, I have completely failed to get the primary key (id number) to auto increment. I'm at the tearing my hair out stage. I've read quite a number of posts on the subject, but not really found an answer.
My version of oo is 2.4. I've made umpteen tables from scratch, even one that had nothing *but* a primary key, set INTEGER, auto value 'YES' and absolutely no data. It seems to save ok, then if I add any other fields and look at it again it has mysteriously changed to auto value 'NO'. Ok, I can add a number myself each time I enter a new record, but this seems such a fundamental thing to expect a database to do that I just don't understand what's going on. Sorry about the rant.
Back to top
View user's profile Send private message
r4zoli
Super User
Super User


Joined: 17 May 2005
Posts: 570
Location: Budapest, Hungary

PostPosted: Fri May 09, 2008 10:23 am    Post subject: Reply with quote

You connect to MySQL or similar databases, or use built in HSQLDB database?
If external it happens with my files, too. It must be set in MySQL.
In HSQLDB normally works for me.
Back to top
View user's profile Send private message
thedrone
General User
General User


Joined: 09 May 2008
Posts: 6

PostPosted: Fri May 09, 2008 11:27 am    Post subject: Reply with quote

Thanks for the reply. As a comparative newbie, this is something I've not really got a grasp on. I've been using Lotus Approach which pretty much does everything I want, but I want to get used to using an alternative in case Approach becomes completely obsolete. I already use oo for most other things. I started off by linking to existing databases, but am currently doing completely new databases with oo. When I looked at the connections, the current driver is set to com.sun.star.comp.sdbc.ODBCDriver. Does this mean I should probably change it?
Back to top
View user's profile Send private message
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Tue May 13, 2008 4:44 am    Post subject: Reply with quote

Hello

About the auto value problem.
there are two solution
one it is a solution for one table. It is describe by Kabing : http://www.oooforum.org/forum/viewtopic.phtml?t=69632&highlight=identity

The other one is for all tables in the database.
Open the database. not the table
goto Edit -->Database -->Advanced click
there is opening a dialog with two tabs goto tab "Generated Values"
mark the check box
write "IDENTITY" in the first line "Instruction automatic increment"
click OK

For new tables it will work for old table you must look : http://www.oooforum.org/forum/viewtopic.phtml?t=69759&highlight=identity

It is working for the HSQLDB for other databases i don't know.
I don't know the exact terms in English it is not my language

Romke
Back to top
View user's profile Send private message
thedrone
General User
General User


Joined: 09 May 2008
Posts: 6

PostPosted: Tue May 13, 2008 8:53 am    Post subject: Reply with quote

Thank you for the suggestions but I'm still having problems. I'm even wondering if I should try and completely uninstall oo and do a fresh install (XP). It doesn't help that I'm completely out of my depth with SQL.
When I looked at edit-database-advanced I got two choices, "end text lines with CTRL+LF" and "form data input checks for required fields" and that is all
Also, as soon as I save a table in design view, I can't edit the field properties at all, only add new fields. This means that if I choose to save a table and have the primary key added automatically, I can't go back and alter the properties.
I tried using the table wizard as a test and even though I selected auto value YES on that, it was all greyed out and set to NO in the design view after saving.
With all the time I'm spending on it, I may as well just add the numbers myself, but I'm stubborn and want to know why it does this!
Back to top
View user's profile Send private message
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Tue May 13, 2008 12:44 pm    Post subject: Reply with quote

Hello thedrone

It looks that you have not activated JAVA.
Look at this link for activating JAVA
http://www.oooforum.org/forum/viewtopic.phtml?t=71036&highlight=instal+java
Be sure you have activated java

Quote:
When I looked at edit-database-advanced I got two choices, "end text lines with CTRL+LF" and "form data input
checks for required fields" and that is all

This is the good place. Don't forget to write the word IDENTITY in the field Instruction automatic increment

Romke
Back to top
View user's profile Send private message
thedrone
General User
General User


Joined: 09 May 2008
Posts: 6

PostPosted: Tue May 13, 2008 1:40 pm    Post subject: Reply with quote

many thanks for your time - I really hoped this would be the answer when I found I had Java installed but it was not set to the latest version. Unfortunately, even after a complete restart to make sure oo wasn't hanging on to any old settings, I still have the same problem. The table wizard works fine right up until saving then the auto value is suddenly switched from yes to no.
Back to top
View user's profile Send private message
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Tue May 13, 2008 2:09 pm    Post subject: Reply with quote

thedrone

Sorry I can not help you but give not up. I have try to installate a linux on a 200 mhz pentium with 68 MByte. Time over time the CD would not contimue with booting . The solotion was to write the word addswap correct with double D and not one

Romke
Back to top
View user's profile Send private message
thedrone
General User
General User


Joined: 09 May 2008
Posts: 6

PostPosted: Tue May 13, 2008 2:36 pm    Post subject: Reply with quote

yes, it usually turns out to be something simple - I don't know if that makes you feel better or worse..
Back to top
View user's profile Send private message
thedrone
General User
General User


Joined: 09 May 2008
Posts: 6

PostPosted: Wed May 14, 2008 10:29 am    Post subject: Reply with quote

Suddenly it works! Thanks Romke for pointing out that I need put IDENTITY in as the auto increment statement as this seems to have cured the problem - I would never have guessed that this was necessary. I did try your suggestion straight away but it made no difference and is only working after the computer was shut down overnight. Coincidence?
To sum up. I made a simple test table using the wizard, adding only the fields AccountID and Description. The AccountID field (Integer) was set to auto value, with the word IDENTITY in the box underneath. Next, I chose to set the existing field AccountID as the primary key, and noticed that the auto value check box was selected automatically. When I finished and opened the table in data input view, <auto-field> was in the AccountID column and this changed to incremental numbers as I added records. Thanks again!
Back to top
View user's profile Send private message
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Thu May 15, 2008 12:37 am    Post subject: Reply with quote

Hello thedrone

I'm glad you have succeed. I remember me I have look for month how to do it. I have read the manual from HSQLDB you can find here
http://hsqldb.org/web/hsqlDocsFrame.html

I would make it complete for other readers.
You have to repeat this for every other database if you want have auto increment.
Identity is only for HSQLDB for other systems I don't know. Look in the documents.for a statement

Romke
Back to top
View user's profile Send private message
keithclark1966
OOo Enthusiast
OOo Enthusiast


Joined: 18 Jan 2007
Posts: 189

PostPosted: Tue Jun 03, 2008 4:01 pm    Post subject: Reply with quote

RPG wrote:
Hello

The other one is for all tables in the database.
Open the database. not the table
goto Edit -->Database -->Advanced click
there is opening a dialog with two tabs goto tab "Generated Values"
mark the check box
write "IDENTITY" in the first line "Instruction automatic increment"
click OK

Romke


This is not an option in my version of OOo Base! No such dialog tab comes up.

Keith
_________________
Open Office 3.0 under Ubuntu 9.04, XP and Vista
Back to top
View user's profile Send private message
Tamblyne
General User
General User


Joined: 07 May 2006
Posts: 26

PostPosted: Sat Jul 05, 2008 10:34 am    Post subject: Reply with quote

keithclark1966 wrote:

This is not an option in my version of OOo Base! No such dialog tab comes up.


I don't have this either (2.4.0 for Windows). Edit > Database > Advanced Settings (the rest of the options are greyed out) yields a dialog box with only a "Special Settings" tab and two check boxes: (1) End text lines with CR+LF and (2) Form data input checks for required fields. There are no other tabs here.

I'm having a lot of problems -- so now I'm wondering if there's something wrong with my installation, which I just upgaded today.
Back to top
View user's profile Send private message Send e-mail
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Sat Jul 05, 2008 2:39 pm    Post subject: Reply with quote

Hello

What kind of DB engine you are using? The instruction are special for HSQLDB.
If you are using an other db engine, it is maybe possible that there is no autoincrement.
Check also if java is installed on your machine and I think you have the good tab (page)

Romke
Back to top
View user's profile Send private message
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Tue Jul 08, 2008 5:44 pm    Post subject: Reply with quote

autovalue problem
howto make hsqldb autovalue

I did discover to day this possibilitie for making autovalue in this way.

It looks me only possible with the HSQLDB engine

This can be used special if you don't get the tabpage.

1 Edit the table where you want have the aututo value
2 edit the field where you have auto value
3 choose for the fieldtype BIGINT
4 choose for the autovalue Yes

If you use a wizard you must also choose BIGINT in the autovalue field

I hope it helps
Romke
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