Click here to Skip to main content
12,395,871 members (57,418 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:

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
DEB4u605

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 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
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160721.1 | Last Updated 20 Sep 2011
Copyright © CodeProject, 1999-2016
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