Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# Sqlite
Hi There,
 
I'm quite new to prog and facing a problem in "insert" using SQLITE. I have 2 tables like this:
 
table1:
1: reg_no (auto generated)(primary key)
2:name
 
table2:
1:reg_no (primary key)(foreign key from table1)
2:course_name
 
What I need is, whenever a new insert happens into "table1" an entry to "table2" also.
How can I know what is that auto-generated field in "table1"?
I can do this by using "select max()".. but it's increasing the steps..
 
Thanx
 
Debasish Sahu
Posted 20-Sep-11 10:07am
DEB4u589

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

You have a number of problems here: first and foremost that SQLite does not support the best way to handle this: use a stored procedure.
 
The other is that auto generated indexes are the work of Stan (he is Satan's limp-wristed brother) and I avoid using them for just this reason - you don't know what the index is when you insert a record. With SQLITE it's not such a problem - you could use MAX to retrieve it, but it's a pain, and not a good practice as it does not scale well if you want to move to a "real" database such as SQL Server - in a multiuser environment using MAX to retrieve your ID is positively dangerous as it could retrieve someone else's ID instead, intermittently.
 
Instead, I would suggest using a Unique Identifier field instead of an autoincremented integer: You generate the Guid ID in your code, and insert the record into the table, probably within a transaction block. Since you control the ID, there can never be a mistake. Surprisingly, there is little or no performance overhead, and if you start to use replication later it can save database space as a GUID is needed for that anyway, and would be created by the DB system when you insert the row.
  Permalink  
Comments
DEB4u at 20-Sep-11 15:34pm
   
Thank you,I got you, but that's ok for some code no for a name but I'd like to ask, if the column is just a serial number which need to be incremented , in that case what'd I do?
Philippe Mori at 20-Sep-11 18:36pm
   
But when using SQL Server with generated code (like data set and probably LINQ-to-SQL and other), IDs are automatically handled when inserting data if auto-increment and relationships were properly defined.
 
I don't know for SQLite...

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 400
1 Jochen Arndt 190
2 Richard MacCutchan 135
3 DamithSL 95
4 Garth J Lancaster 90
0 OriginalGriff 6,045
1 DamithSL 4,601
2 Maciej Los 4,032
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,220


Advertise | Privacy | Mobile
Web02 | 2.8.141220.1 | Last Updated 20 Sep 2011
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100