Click here to Skip to main content
11,921,889 members (58,734 online)
Rate this:
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 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 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 568
1 Dave Kreskowiak 285
2 Sergey Alexandrovich Kryukov 173
3 Richard MacCutchan 155
4 ppolymorphe 135
0 OriginalGriff 7,036
1 KrunalRohit 4,530
2 Sergey Alexandrovich Kryukov 3,286
3 George Jonsson 2,840
4 Suvendu Shekhar Giri 2,216

Advertise | Privacy | Mobile
Web03 | 2.8.151120.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