Results 1 to 27 of 27

Thread: Retrieve the autoID # when adding a new row

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Nov 2022
    Location
    Manitoba, Canada
    Posts
    142

    Retrieve the autoID # when adding a new row

    It has been brought to my attention that in order to prevent concurrency errors when editing a newly saved record in the database, that I should retrieve the autoID number of that new record before attempting to save any changes to it. This is how I have come to understand the situation anyway. Please correct me if I am wrong!

    Why I need to be concerned with this was explained to me in these words: "When you add a new record and then Save the change you'll notice the "ID" field is still empty. If you were to Edit the newly added record after it's already been saved , when you try to save the new change you'll get an error, a concurrency error. That's because the ID is missing."

    In any event, a piece of code was offered to me in an effort to alleviate this concern. I am having some trouble with this code and I will share my rendition of that code here before I go on:

    Code:
    Private adGemstones As New SQLiteDataAdapter(Adapter1, dbCon)
    
        Private Sub adGemstones_RowUpdated(sender As Object, e As RowUpdatedEventArgs) Handles adGemstones.RowUpdated
    
            'We are only interested in new records.
            If e.StatementType = StatementType.Insert Then
                'Get the last ID auto-generated by the database.
                Using command As New SQLiteCommand("SELECT Last_Insert_RowId()", dbCon)
                    'Update the ID of the local row.
                    e.Row("ID") = CInt(command.ExecuteScalar())
                    Me.lblID.Text = e.Row("ID").ToString
                End Using
            End If
        End Sub
    Honestly, I don't really understand what is going on here and hope to get some clarification. I'm not even truly sure that I understand why I need this but instead of private messaging the kind chap who suggested I look this code over, I thought I would post in the forum so as to maybe help others (while searching for help myself).

    Regarding this code, it is my belief that when the data adapter's (adGemstones) RowUpdated event activates, this code then initiates a search in the datatable for the last inserted row ID. Again, I'm not really clear on why this is important but it has been indicated to me that it is. Furthermore, I also have it written so as to display that new found ID in a label afterwards but that's not relevant I don't think.

    When I attempt to run the code above I get an error (Handles clause requires a WithEvents variable defined in the containing type or one of its base types). I don't know what this errors is trying to tell me except that it expects to see a WithEvents variable. It should also be noted that in my code window, the first line of the code seems to have an error. Visual Studio is putting the dreaded red, squiggly line under the dataadapter (shown in bold/underline below):

    Code:
    Private Sub adGemstones_RowUpdated(sender As Object, e As RowUpdatedEventArgs) Handles adGemstones.RowUpdated
    I will start off by asking, why is my data adapter being called out as a code error in the first line of code posted above?
    Last edited by The_Hobbyist; Jan 23rd, 2023 at 06:46 PM.

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    109,288

    Re: Retrieve the autoID # when adding a new row

    So, when you opened a search engine and typed in the words "vb.net withevents", what did you not understand about the information you found? None of us know stuff to begin with but you should know how to find stuff. If you're asking a question here without having read the documentation or doing a web search first then you haven't really tried. ALWAYS do what you can for yourself first. It's for your own sake, as you'll end up saving yourself time and learning more in the process. This is the first match I got when I did that web search. That's about 20 seconds it would have taken to answer your own question.

    If you had done that then you'd know that, in order to include a field in a Handles clause, you have to declare it WithEvents. You've even got examples already in your code, if you cared to look at the designer code for the Buttons, TextBoxes and other controls you added in the designer. It's as simple as this:
    vb.net Code:
    1. Private WithEvents adGemstones As New SQLiteDataAdapter(Adapter1, dbCon)

  3. #3
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    109,288

    Re: Retrieve the autoID # when adding a new row

    As for what's going on overall, if you want to update or delete a record in the database then you need to identify that record by it's primary key value, which you can't do if you don't have that value. You initially add a record to your DataTable and then you use your data adapter to save all your changes (inserts, updates and deletes) to the database. At that point, the database generates a PK value for each new record. What you're doing above is retrieving those PK values back into your DataTable so that any modifications you make to the data can then be saved to the database using those PK values. The data adapter raises that event for each row it saves to the database. The code is checking whether the type of change being saved is an insert and, if it is, it is asking the database to give it the last auto-generated ID value. That value is then copied into the ID column of that row in your DataTable, enabling you to use the same value as the database to identify that row.

  4. #4

    Thread Starter
    Addicted Member
    Join Date
    Nov 2022
    Location
    Manitoba, Canada
    Posts
    142

    Re: Retrieve the autoID # when adding a new row

    Quote Originally Posted by jmcilhinney View Post
    As for what's going on overall, if you want to update or delete a record in the database then you need to identify that record by it's primary key value, which you can't do if you don't have that value. You initially add a record to your DataTable and then you use your data adapter to save all your changes (inserts, updates and deletes) to the database. At that point, the database generates a PK value for each new record. What you're doing above is retrieving those PK values back into your DataTable so that any modifications you make to the data can then be saved to the database using those PK values. The data adapter raises that event for each row it saves to the database. The code is checking whether the type of change being saved is an insert and, if it is, it is asking the database to give it the last auto-generated ID value. That value is then copied into the ID column of that row in your DataTable, enabling you to use the same value as the database to identify that row.
    Well, that sure helps me to understand what and why this needs to be done. Thank you, this should help me. This is the clearest, easiest to understand (for a lay person) explanation of why this is required that I haver read in 3 days.

  5. #5
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,045

    Re: Retrieve the autoID # when adding a new row

    I know you use to declare the dataadapter WithEvents. Did you remove it? If you already have WithEvents then post your current code.

    I don't know why your assigning the return value to a label. If you follow the example I gave you the ID will show up in the dgv and the textbox you have bound the the bindingsource.

    The reason you need to retrieve the last ID is because you the ID, as I said. How is the dataadapter Update command going to update a record without having the ID, just look at the adapter update command. Just because the database creates the new Id number and inserts it into the table that doesn't mean it's also inserted into the associated datatable and bindingsource. Haven't you noticed the ID is missing on the row you added?

  6. #6

    Thread Starter
    Addicted Member
    Join Date
    Nov 2022
    Location
    Manitoba, Canada
    Posts
    142

    Re: Retrieve the autoID # when adding a new row

    Quote Originally Posted by wes4dbt View Post
    I know you use to declare the dataadapter WithEvents. Did you remove it? If you already have WithEvents then post your current code.

    I don't know why your assigning the return value to a label. If you follow the example I gave you the ID will show up in the dgv and the textbox you have bound the the bindingsource.

    The reason you need to retrieve the last ID is because you the ID, as I said. How is the dataadapter Update command going to update a record without having the ID, just look at the adapter update command. Just because the database creates the new Id number and inserts it into the table that doesn't mean it's also inserted into the associated datatable and bindingsource. Haven't you noticed the ID is missing on the row you added?
    Thanks Wes. Somewhere along the line, I suppose I did remove the WithEvents declaration when defining the dataadapter. Why, when or how it was removed, I don't know. I obviously removed it but I can't tell you why I did or when. I have written the WithEvents back into that line defining the data adapter and things appear to work as they should now. As far as I can tell anyway.

    The label thing is just a form object where I'm displaying the ID. Its just a part of the GUI that I will likely remove before long. I'm limiting the columns displayed in the DGV to just the name of the item (for tidiness), other relevant data is displayed in form objects and grouped according to the type of data. Thats all.

    This entire time I was wondering how, why and where I had to add a line with the WithEvents declaration without realizing that it probably should have been there already.

    Thanks for the help.

  7. #7

    Thread Starter
    Addicted Member
    Join Date
    Nov 2022
    Location
    Manitoba, Canada
    Posts
    142

    Re: Retrieve the autoID # when adding a new row

    I can hardly wait to start asking about the code example that jmc offered the forum about 15-16 years ago on how to save image files into the database and then recall them and displaying them in a picturebox... I was reading up on that last week and started playing with it, hesitant to even go down that path.

    Until later.

    Cheers!

  8. #8
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    109,288

    Re: Retrieve the autoID # when adding a new row

    Quote Originally Posted by The_Hobbyist View Post
    I can hardly wait to start asking about the code example that jmc offered the forum about 15-16 years ago on how to save image files into the database
    I posted one on this current topic about 4 years after that too. It was for Access specifically but all the principles are the same. The only real difference is the database function used to get the last ID in SQL. That example shows how to do it with a typed DataSet as well, which may or may not be of use to you.

  9. #9

    Thread Starter
    Addicted Member
    Join Date
    Nov 2022
    Location
    Manitoba, Canada
    Posts
    142

    Re: Retrieve the autoID # when adding a new row

    Quote Originally Posted by jmcilhinney View Post
    I posted one on this current topic about 4 years after that too.
    I'm having trouble finding it. The forum's search function seems to return a very broad assortment of threads but this is likely more due to my inability to structure the search properly. Proper language and terms matter and I have a tendency to be lacking in the language and terms department... Its not from a lack of effort I assure you.

  10. #10
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,045

    Re: Retrieve the autoID # when adding a new row

    Saving/displaying images is a different topic and you should start a new thread.

    There is ton of information on the subject,
    Google, "visual basic save image to database"

    Or search this forum, "save image to database"

    Most results are going to be for SQLO Sever, I don't know if SQLite handles it differently, probably not. The one thing you need to decide is if you want to storage the image in the database or just store the path to the image in the database. Your db probably wont have 10,000's of records so either way should be fine.

  11. #11

    Thread Starter
    Addicted Member
    Join Date
    Nov 2022
    Location
    Manitoba, Canada
    Posts
    142

    Re: Retrieve the autoID # when adding a new row

    Quote Originally Posted by wes4dbt View Post
    Saving/displaying images is a different topic and you should start a new thread.

    There is ton of information on the subject,
    Google, "visual basic save image to database"

    Or search this forum, "save image to database"

    Most results are going to be for SQLO Sever, I don't know if SQLite handles it differently, probably not. The one thing you need to decide is if you want to storage the image in the database or just store the path to the image in the database. Your db probably wont have 10,000's of records so either way should be fine.
    Yes, thank you. I had already thought of that. I created a thread in the database forum hoping to discuss the pros and cons of the different methods of image handling before I replied to jmc above. Also, it wasn't my intent to change the course of this thread. I only wanted jmc to know that I've been looking for the thread he cited is all.

    Thanks again Wes.

  12. #12
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    109,288

    Re: Retrieve the autoID # when adding a new row

    Quote Originally Posted by The_Hobbyist View Post
    I'm having trouble finding it.
    There's a link in my signature below to all my CodeBank threads.

  13. #13
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,045

    Re: Retrieve the autoID # when adding a new row

    Quote Originally Posted by The_Hobbyist View Post
    It has been brought to my attention that in order to prevent concurrency errors when editing a newly saved record in the database, that I should retrieve the autoID number of that new record before attempting to save any changes to it. This is how I have come to understand the situation anyway. Please correct me if I am wrong!

    Why I need to be concerned with this was explained to me in these words: "When you add a new record and then Save the change you'll notice the "ID" field is still empty. If you were to Edit the newly added record after it's already been saved , when you try to save the new change you'll get an error, a concurrency error. That's because the ID is missing."

    In any event, a piece of code was offered to me in an effort to alleviate this concern. I am having some trouble with this code and I will share my rendition of that code here before I go on:

    Code:
    Private adGemstones As New SQLiteDataAdapter(Adapter1, dbCon)
    
        Private Sub adGemstones_RowUpdated(sender As Object, e As RowUpdatedEventArgs) Handles adGemstones.RowUpdated
    
            'We are only interested in new records.
            If e.StatementType = StatementType.Insert Then
                'Get the last ID auto-generated by the database.
                Using command As New SQLiteCommand("SELECT Last_Insert_RowId()", dbCon)
                    'Update the ID of the local row.
                    e.Row("ID") = CInt(command.ExecuteScalar())
                    Me.lblID.Text = e.Row("ID").ToString
                End Using
            End If
        End Sub
    Honestly, I don't really understand what is going on here and hope to get some clarification. I'm not even truly sure that I understand why I need this but instead of private messaging the kind chap who suggested I look this code over, I thought I would post in the forum so as to maybe help others (while searching for help myself).

    Regarding this code, it is my belief that when the data adapter's (adGemstones) RowUpdated event activates, this code then initiates a search in the datatable for the last inserted row ID. Again, I'm not really clear on why this is important but it has been indicated to me that it is. Furthermore, I also have it written so as to display that new found ID in a label afterwards but that's not relevant I don't think.

    When I attempt to run the code above I get an error (Handles clause requires a WithEvents variable defined in the containing type or one of its base types). I don't know what this errors is trying to tell me except that it expects to see a WithEvents variable. It should also be noted that in my code window, the first line of the code seems to have an error. Visual Studio is putting the dreaded red, squiggly line under the dataadapter (shown in bold/underline below):

    Code:
    Private Sub adGemstones_RowUpdated(sender As Object, e As RowUpdatedEventArgs) Handles adGemstones.RowUpdated
    I will start off by asking, why is my data adapter being called out as a code error in the first line of code posted above?
    I thought I should let you know that if you set this property on the DataAdapter you wont have to manually retrieve the ID on an Insert in the RowUpdated Event.

    Code:
    MissingSchemaAction = MissingSchemaAction.AddWithKey
    Like this,

    Code:
     Private WithEvents scienceAdapter As New SQLiteDataAdapter("select Id, Name, Density, ChemistryID, MagneticYes, photo From ScientificData", con) With {.MissingSchemaAction = MissingSchemaAction.AddWithKey}
    I thought SQLite was like MS Access database and you would have to retrieve the ID manually. But just tested and it works fine.

    Both ways work.

    EDIT: This is wrong. Please read jmc post below to see why. I will go ahead and leave this post as is just in case someone else makes the same mistake.
    Last edited by wes4dbt; Feb 2nd, 2023 at 11:36 PM.

  14. #14
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    109,288

    Re: Retrieve the autoID # when adding a new row

    Quote Originally Posted by wes4dbt View Post
    I thought I should let you know that if you set this property on the DataAdapter you wont have to manually retrieve the ID on an Insert in the RowUpdated Event.

    Code:
    MissingSchemaAction = MissingSchemaAction.AddWithKey
    Like this,

    Code:
     Private WithEvents scienceAdapter As New SQLiteDataAdapter("select Id, Name, Density, ChemistryID, MagneticYes, photo From ScientificData", con) With {.MissingSchemaAction = MissingSchemaAction.AddWithKey}
    I thought SQLite was like MS Access database and you would have to retrieve the ID manually. But just tested and it works fine.

    Both ways work.
    Really?! I'm going to have to test that to confirm. I thought that all that did was set the PrimaryKey property of the DataTable when you called Fill if it wasn't already set. I had no idea that it would cause auto-generated IDs to be retrieved on insert and, to be frank, I don't believe it. I suspect that what you're actually seeing is that the DataTable is generating temporary IDs itself, courtesy of the AutoIncrement properties of the PK column, and they just happen to match the IDs generated by the database. The way you can test that is to have some records in the database already, then create a DataTable and generate the schema by calling FillSchema on the data adapter rather than Fill, so no data is retrieved. You can then add some rows and you should find that the IDs generated will start at 1 or maybe even at -1 and go backwards, then save the data using the data adapter. I think you'll find that those ID values will not change unless you write code to change them. The main point of those temporary IDs is for use in foreign keys, so you can relate child records to parents before saving them in the database. You need to retrieve the parent ID after saving and propagate that to the foreign key column in the child table?

  15. #15
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,045

    Re: Retrieve the autoID # when adding a new row

    Quote Originally Posted by jmcilhinney View Post
    Really?! I'm going to have to test that to confirm. I thought that all that did was set the PrimaryKey property of the DataTable when you called Fill if it wasn't already set. I had no idea that it would cause auto-generated IDs to be retrieved on insert and, to be frank, I don't believe it. I suspect that what you're actually seeing is that the DataTable is generating temporary IDs itself, courtesy of the AutoIncrement properties of the PK column, and they just happen to match the IDs generated by the database. The way you can test that is to have some records in the database already, then create a DataTable and generate the schema by calling FillSchema on the data adapter rather than Fill, so no data is retrieved. You can then add some rows and you should find that the IDs generated will start at 1 or maybe even at -1 and go backwards, then save the data using the data adapter. I think you'll find that those ID values will not change unless you write code to change them. The main point of those temporary IDs is for use in foreign keys, so you can relate child records to parents before saving them in the database. You need to retrieve the parent ID after saving and propagate that to the foreign key column in the child table?
    Thanks, you were right. I tested and the ID started at 0.

    I was surprised when I noticed the ID showing up on Adding new records but since the ID's matched the database table I thought it was the actual ID. Glad you pointed out the mistake.

    So I was right the first time. Should have left well enough alone. lol


    I should mention when that property is not set datatable doesn't generate as temporary ID.
    Last edited by wes4dbt; Feb 3rd, 2023 at 12:09 AM.

  16. #16
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    109,288

    Re: Retrieve the autoID # when adding a new row

    Quote Originally Posted by wes4dbt View Post
    I should mention when that property is not set datatable doesn't generate as temporary ID.
    That's not strictly true. What that property does is determine what happens with regards to the DataTable schema when you call Fill on the data adapter. If it's set to Add, missing DataColumns will be added automatically based on the query schema. If it's set to AddWithKey, missing primary key information will be added too, including setting the AutoIncrement, AutoIncrementSeed and AutoIncrementStep properties of the PrimaryKey column. Whether the DataTable generates IDs and how is based on those properties and you can set those properties yourself rather than relying on the data adapter to do it for you based on its MissingSchemaAction property.

  17. #17
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,045

    Re: Retrieve the autoID # when adding a new row

    Quote Originally Posted by jmcilhinney View Post
    That's not strictly true. What that property does is determine what happens with regards to the DataTable schema when you call Fill on the data adapter. If it's set to Add, missing DataColumns will be added automatically based on the query schema. If it's set to AddWithKey, missing primary key information will be added too, including setting the AutoIncrement, AutoIncrementSeed and AutoIncrementStep properties of the PrimaryKey column. Whether the DataTable generates IDs and how is based on those properties and you can set those properties yourself rather than relying on the data adapter to do it for you based on its MissingSchemaAction property.
    Good to know. But I can't promise I'll remember. lol

    I believe several years ago I made this same mistake and my guess is your probably the one who pointed it out and explained what was really happening. I've been retrieving the ID manually just out of habit and forgot why.

    Lets hope the people that read this have a better memory than me.

  18. #18
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    109,288

    Re: Retrieve the autoID # when adding a new row

    It's also worth noting that you only need to use this approach when using a database and provider that only supports a single SQL statement per command. If you're using, for instance, SQL Server and SqlClient then you can just include a SELECT statement at the end of the CommandText of your InsertCommand and that will read the identity value back into the same DataRow. To be honest, I'm not sure whether that's supported with SQLite or not. I know it's not with Access and OleDb.

  19. #19
    Lively Member
    Join Date
    Jan 2022
    Posts
    110

    Re: Retrieve the autoID # when adding a new row

    If you are showing a set of data where a user can edit this means that the rowcount should be relatively low. That said, in all practicality, just refill the datatable after an update and be done with it. Not only will this get the ID, but any other rows columns that have been potentially modified by other users.
    Code:
    Private Sub ButtonUpdate_Click(sender As Object, e As EventArgs) Handles ButtonUpdate.Click
        Using SQLConn As New SqlConnection(SQLConnStr)
            Using SQLCB As New SqlCommandBuilder(FormSectionsDA)
                FormSectionsDA.SelectCommand.Connection = SQLConn
                FormSectionsDA.Update(FormSectionsDT)
                FormSectionsDT.Rows.Clear()
                FormSectionsDA.Fill(FormSectionsDT)
            End Using
        End Using
    End Sub
    you can just include a SELECT statement at the end of the CommandText of your InsertCommand and that will read the identity value back into the same DataRow
    ^ I dont think this works in MySQL either.
    Last edited by vbdotnut; Feb 3rd, 2023 at 04:38 AM.

  20. #20
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,045

    Re: Retrieve the autoID # when adding a new row

    Quote Originally Posted by vbdotnut View Post
    If you are showing a set of data where a user can edit this means that the rowcount should be relatively low. That said, in all practicality, just refill the datatable after an update and be done with it. Not only will this get the ID, but any other rows columns that have been potentially modified by other users.
    Code:
    Private Sub ButtonUpdate_Click(sender As Object, e As EventArgs) Handles ButtonUpdate.Click
        Using SQLConn As New SqlConnection(SQLConnStr)
            Using SQLCB As New SqlCommandBuilder(FormSectionsDA)
                FormSectionsDA.SelectCommand.Connection = SQLConn
                FormSectionsDA.Update(FormSectionsDT)
                FormSectionsDT.Rows.Clear()
                FormSectionsDA.Fill(FormSectionsDT)
            End Using
        End Using
    End Sub
    ^ I dont think this works in MySQL either.
    This is a bad idea. It's very inefficient, there is no need to Clear and Fill the DataTable. Also, your current datatable row position is lost. SQLite is a single user DBMS so there will no changes from other users. Simply retrieve the last row ID.

  21. #21
    Lively Member
    Join Date
    Jan 2022
    Posts
    110

    Re: Retrieve the autoID # when adding a new row

    It is by no means not even close to inefficient, unless you consider .0023 ms more efficient....
    position is easy to preserve if you need that.

    The only way I could trust getting the proper ID back otherwise would be to create a guid and get the id back using that guid. integrity > efficiency

  22. #22
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    109,288

    Re: Retrieve the autoID # when adding a new row

    Quote Originally Posted by vbdotnut View Post
    The only way I could trust getting the proper ID back otherwise would be to create a guid and get the id back using that guid. integrity > efficiency
    This is silly. You're trying to solve a problem that doesn't exist. That fact that you don't trust something that works 100% of the time because that's specifically what it was designed for is irrelevant to anyone else.

  23. #23
    Lively Member
    Join Date
    Jan 2022
    Posts
    110

    Re: Retrieve the autoID # when adding a new row

    This is silly. You're trying to solve a problem that doesn't exist. That fact that you don't trust something that works 100% of the time because that's specifically what it was designed for is irrelevant to anyone else.
    Are you sure that # is 100% Youre saying that if you have 1000 people inserting rows all day every day for a year that is is 100% impossible to get the id of someone elses inserted row.

    I guess the point is, that is equally as silly as thinking a refill on the table is inefficient.

  24. #24
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    2,942

    Re: Retrieve the autoID # when adding a new row

    IMO for adding new Records a Form with whatever controls is much better than using a DGV.
    a DGV is for display ; filter data etc..

    @JMC
    to read the next ID in Access Database you can use COM (MS ADO Ext. 6.0 for DDL and Security), there you can use the...
    Code:
    Properties("Seed").Value
    that would return the next possible Autoincrement number
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  25. #25
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    109,288

    Re: Retrieve the autoID # when adding a new row

    Quote Originally Posted by vbdotnut View Post
    Youre saying that if you have 1000 people inserting rows all day every day for a year that is is 100% impossible to get the id of someone elses inserted row.
    How can you get someone else's ID when there's no one else inserting rows? The OP has specifically stated that this is a single-user app. If it was a multi-user app then you'd be better using a proper multi-user database like SQL Server, in which case you can use SCOPE_IDENTITY to ensure that you only get an ID generated in the current scope. That may even be possible in SQLite - I don't know - but it's irrelevant when you're the only one inserting records. Like I said, you're trying to solve a problem that doesn't exist.

  26. #26
    Lively Member
    Join Date
    Jan 2022
    Posts
    110

    Re: Retrieve the autoID # when adding a new row

    Like I said, you're trying to solve a problem that doesn't exist.
    point taken. Not overly familiar with SQLLite, Would this have any benefit over using XML/DataSet? I suppose that you could run queries on the former, but you could always filter the latter. If it is a matter of having more data than you care to store in xml then why not use a local db instead of having the SQLlite dependency?

  27. #27
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    109,288

    Re: Retrieve the autoID # when adding a new row

    Quote Originally Posted by vbdotnut View Post
    point taken. Not overly familiar with SQLLite, Would this have any benefit over using XML/DataSet? I suppose that you could run queries on the former, but you could always filter the latter. If it is a matter of having more data than you care to store in xml then why not use a local db instead of having the SQLlite dependency?
    Using XML would require you to load and save all the data every time, plus do all your queries in VB code. Not the end of the world but not overly efficient for any data set that is remotely large. The advantage of SQLite over a SQL Server data file is that even LocalDB is an extra installation whereas SQLite requires nothing other than your application and a DLL distributed with it. Microsoft even recommend SQLite themselves where they used to provide SQL Server CE.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width