Click here to Skip to main content
Rate this: bad
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:
1: reg_no (auto generated)(primary key)
1:reg_no (primary key)(foreign key from table1)
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..
Debasish Sahu
Posted 20-Sep-11 10:07am

1 solution

Rate this: bad
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.
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 288
1 ProgramFOX 205
2 Peter Leow 140
3 Sergey Alexandrovich Kryukov 133
4 Dave Kreskowiak 96
0 OriginalGriff 288
1 ProgramFOX 165
2 Peter Leow 130
3 Sergey Alexandrovich Kryukov 95
4 Fredrik Bornander 85

Advertise | Privacy | Mobile
Web04 | 2.8.150301.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