Source language: Translate to:

Sql insert autoinc

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

Sql insert autoinc

Postby BRobinsonS » Tue Jan 07, 2014 11:33 am

Started with (but said fields didn't match)
dbpCreateTable "DogDB" "DispositionA" "id AutoInc PrimaryKey;DescriptionA String(20)"
dbpOpenTable "DogDb" "DispositionA" ""
dbpExecSQL "DogDb" "INSERT INTO DispositionA VALUES('Research - SCC');|INSERT INTO DispositionA VALUES('WECHS');|INSERT INTO DispositionA VALUES('Redeemed');|INSERT INTO DispositionA VALUES('Adoption');" ""

Tried this
dbpExecSQL "DogDb" "INSERT INTO DispositionA VALUES(AutoInc,'Research - SCC');|INSERT INTO DispositionA VALUES(AutoInc,'WECHS');|INSERT INTO DispositionA VALUES(AutoInc,'Redeemed');|INSERT INTO DispositionA VALUES(AutoInc,'Adoption');" ""
Tried Null first in place of AutoInc. Even tried 'auto'.

Don't know if I need AutoInc field. The table is just used to pick values for main table.
I read a lot about Access tables should have an autoinc field

So what is the correct way to INSERT where an autoinc field is involved?
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 340
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada

Re: Sql insert autoinc

Postby Neosoft Support » Tue Jan 07, 2014 11:41 am

You don't necessarily need an AutoInc field, you just need a PrimaryKey field that is guaranteed to be unique. We often use an AutoInc field for this purpose.

Unfortunately, the database engine will not allow you to import data into an AutoInc field. You can still use an AutoInc field as your PrimaryKey, just don't try to import anything into it. Instead create another plain Integer field for your imported data and let the database generate the AutoInc data automatically.
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5602
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Re: Sql insert autoinc

Postby BRobinsonS » Tue Jan 07, 2014 1:41 pm

** Edited **
I tried to just import the text field only and it reported that fields didn't match or similar message.
Actual message
Number of query values and destination fields are not the same

So I thought it was because I was leaving out the autoinc field. Seems to be looking for two fields i.e. id (autoinc) and DescriptionA (string 20).

Perhaps it was a typo I didn't see.
Does the Sql defintion and the table field size need to match?

I will revisit the sql coding.
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 340
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada

Re: Sql insert autoinc

Postby Neosoft Support » Wed Jan 08, 2014 12:11 pm

I think you need to include the table name and field name in your SQL statement. For example:

INSERT INTO TableName (FieldName) VALUES ('Research - SCC');
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5602
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Re: Sql insert autoinc

Postby BRobinsonS » Wed Jan 08, 2014 5:32 pm

That makes sense I'll have to try that.
In the meantime, I deleted the id autoinc field, but maybe I should add it back in to try this.
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 340
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada

Re: Sql insert autoinc

Postby dpayer » Thu Jan 09, 2014 5:42 pm

You typically don't need to insert or change the value into the auto increment column. When you add a new record to the database, the auto increment field automatically puts in the next number in the sequence. If you delete records, it does not fill in those numbers as the purpose of this field is to keep the original order of the entry of the records.

What is your goal in working with the autoinc field?

David P.
David Payer
Des Moines, Iowa
USA
User avatar
dpayer
 
Posts: 1382
Joined: Mon Apr 11, 2005 5:55 am
Location: Iowa - USA

Re: Sql insert autoinc

Postby BRobinsonS » Thu Jan 09, 2014 9:20 pm

I generally understand how an autoinc field works.

Just used it as Primary Index field because Access seems to work best that way according to what I have read here.
In this case, I use the tables for lookup (dbpPopupValueList) value for Main Table.

Perhaps you can shed some light on just when Tables (Access) need a primary index and when they don't.
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 340
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada

Re: Sql insert autoinc

Postby dpayer » Fri Jan 10, 2014 7:42 am

BRobinsonS wrote:I generally understand how an autoinc field works.

Just used it as Primary Index field because Access seems to work best that way according to what I have read here.
In this case, I use the tables for lookup (dbpPopupValueList) value for Main Table.

Perhaps you can shed some light on just when Tables (Access) need a primary index and when they don't.



There are times when the data itself will/must have a unique record for each piece of data. For example, in an inventory system, you would have a unique product ID number that serves as the index. You would not want to have two different products with the same ID. In membership systems where you have multiple people with the same last name you need another way to identify the record uniquely. That is the function of an autoincrement field. There should never be two records with the same autoinc number. Small tables that contain simple lists that will be used to identify options would typically not need an index. Tables with hundreds of entries with duplicate data in some fields would.

$.02
(not sure if US cents are worth the same as Canadian ones though :)
David Payer
Des Moines, Iowa
USA
User avatar
dpayer
 
Posts: 1382
Joined: Mon Apr 11, 2005 5:55 am
Location: Iowa - USA

Re: Sql insert autoinc

Postby BRobinsonS » Fri Jan 10, 2014 8:35 am

I guess, in short, if I have a field/record where I want to guarantee a unique value I should use autoinc field.

regarding $.02 USD = $0.018 CDN about now. :(
will hurt a little bit for our trip to Florida in Feb. :)
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 340
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada


Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 2 guests