Click here to Skip to main content
11,706,979 members (53,048 online)
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 9:07am
DEB4u594

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 Sergey Alexandrovich Kryukov 140
1 OriginalGriff 125
2 chainerlt 110
3 ppolymorphe 99
4 Mika Wendelius 70
0 OriginalGriff 9,178
1 Sergey Alexandrovich Kryukov 8,487
2 CPallini 5,189
3 Maciej Los 4,766
4 Mika Wendelius 3,696


Advertise | Privacy | Mobile
Web04 | 2.8.150819.1 | Last Updated 20 Sep 2011
Copyright © CodeProject, 1999-2015
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