Results 1 to 19 of 19

Thread: rows in incorrect order when inserting into SQL database

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Dec 2011
    Posts
    117

    rows in incorrect order when inserting into SQL database

    Hi, I have a piece of software that helps us rapidly change entries in our sql compact database. The issue we are having is when we insert many rows after deleting rows, the inserted rows are not in the correct order.

    To attempt to clarify:

    If we simply add new rows, irregardless of how many, they will always be in the correct order.

    If we start by pulling a set or rows and editing them (possibly removing, adding or changing the content). Our software then deletes that block of rows, and reinserts what we have just changed. It does this instead of updating rows because some may have been removed or some may have been added. If we have only manipulated a small block of rows (say <20ish) it will insert them in the correct order. If it is a large number of rows it will scramble the order.

    We have found a work around where we do this:

    Pull the block of rows from the database into our software for editing.
    After editing, use SDF viewer to manually select and delete those rows.
    Go back to our software and reinsert the edited rows.

    No matter how many rows we manipulate, if we use the work around it will insert them in the correct order.

    We cannot figure out why our software scrambles the rows order.

    Here is the code to delete a block of rows:

    Code:
    Dim connstring As String = "Data Source='" & filelocation.Text & "';Password='***'"
                    Dim conn As New SqlCeConnection(connstring)
                    Dim CommandText As String = "delete From Cat where cat='" + catbox.Text + "'"
                    Dim cmd As New SqlCeCommand(CommandText, conn)
    
                    conn.Open()
    
                    Try
                        cmd.ExecuteNonQuery()
                        Catch ex As Exception
                        MsgBox("Error!")
                    End Try
    
    
                    conn.Close()
    Here is the code to insert:

    Code:
    Dim rowcount As Integer = 0
    
            'build insert statement
    
            Dim connstring As String = "Data Source='" & filelocation.Text & "';Password='***'"
            Dim conn As New SqlCeConnection(connstring)
            Dim sqlstatement As String = ""
            Dim cmd As New SqlCeCommand(sqlstatement, conn)
            Dim dt As New DataTable
            Dim da As New SqlCeDataAdapter(cmd)
    
            conn.Open()
    
            DataGrid.RefreshEdit()
    
            For Each row As DataGridViewRow In DataGrid.Rows
    
                If DataGrid.Rows.Count - 1 > rowcount Then
    
                    If String.IsNullOrEmpty(row.Cells(0).Value) Then
                        row.Cells(0).Value = 0
                    End If
    
                    If String.IsNullOrEmpty(row.Cells(1).Value) Then
                        row.Cells(1).Value = ""
                    End If
    
                    If String.IsNullOrEmpty(row.Cells(2).Value) Then
                        row.Cells(2).Value = ""
                    End If
    
                    If String.IsNullOrEmpty(row.Cells(3).Value) Then
                        row.Cells(3).Value = ""
                    End If
    
                    If String.IsNullOrEmpty(row.Cells(4).Value) Then
                        row.Cells(4).Value = ""
                    End If
    
                    If String.IsNullOrEmpty(row.Cells(5).Value) Then
                        row.Cells(5).Value = ""
                    End If
    
                    If String.IsNullOrEmpty(row.Cells(6).Value) Then
                        row.Cells(6).Value = 0
                    End If
    
                    If String.IsNullOrEmpty(row.Cells(7).Value) Then
                        row.Cells(7).Value = ""
                    End If
    
                    If String.IsNullOrEmpty(row.Cells(8).Value) Then
                        row.Cells(8).Value = ""
                    End If
    
                    If String.IsNullOrEmpty(row.Cells(9).Value) Then
                        row.Cells(9).Value = 0
                    End If
    
                    sqlstatement = "INSERT INTO Cat(ID, Cat, Product, [Key], [Desc], link, visible, jump, prev, user) VALUES('" & idtextbox.Text & "', '" & catbox.Text & "', '" & row.Cells(2).Value.ToString & "', '" & row.Cells(3).Value.ToString & "', @descr, '" & row.Cells(5).Value.ToString & "', '" & visible & "', '" & row.Cells(7).Value.ToString & "', '" & row.Cells(8).Value.ToString & "', '" & user.Text & "')"
    
                    cmd.Parameters.Clear()
                    cmd.Parameters.AddWithValue("@descr", row.Cells(4).Value)
    
                    cmd.CommandText = sqlstatement
    
                    cmd.ExecuteNonQuery()
    
                    rowcount = rowcount + 1
    
                    sqlstatement = ""
    
                End If
    
            Next

    Any help is greatly appreciated!

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: rows in incorrect order when inserting into SQL database

    Quote Originally Posted by victorb17 View Post
    irregardless
    No such word. It's "regardless" or "irrespective". Thus ends the lesson.

  3. #3
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: rows in incorrect order when inserting into SQL database

    Quote Originally Posted by victorb17 View Post
    It does this instead of updating rows because some may have been removed or some may have been added.
    I'm not clear on why that's an issue. Why can't use just use a data adapter to save the entire contents of a DataTable at once, inserting, updating and deleting records as required?

  4. #4

    Thread Starter
    Lively Member
    Join Date
    Dec 2011
    Posts
    117

    Re: rows in incorrect order when inserting into SQL database

    I really appreciate your help and am not trying to start anything.

    https://www.merriam-webster.com/dictionary/irregardless

  5. #5
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,372

    Re: rows in incorrect order when inserting into SQL database

    three things to note here:
    1.) You should take the effort in your app to track if you need to delete/add/Update a record and do so. deleting all records and recreating them may work in a single user environment but is terrible practice
    2.) A database does not know or care about any order. if you do not specify an "order by" in your select query, it may return rows in any random order. (The fact that there is apperently some order comes from how databases work, but you, as the consumer, must not rely on that)
    3.) Your code should be improved in several ways like using sql parameters and datatables, which in fact can take care of your update/delete/add problem.

  6. #6

    Thread Starter
    Lively Member
    Join Date
    Dec 2011
    Posts
    117

    Re: rows in incorrect order when inserting into SQL database

    Quote Originally Posted by jmcilhinney View Post
    I'm not clear on why that's an issue. Why can't use just use a data adapter to save the entire contents of a DataTable at once, inserting, updating and deleting records as required?
    I am not the one directly writing this code. I will look into this. I think it had something to do with the fact that the DB is on a server with many people accessing it at a time, and when a section was being worked on, they wanted it to be inaccessible.

  7. #7

    Thread Starter
    Lively Member
    Join Date
    Dec 2011
    Posts
    117

    Re: rows in incorrect order when inserting into SQL database

    Quote Originally Posted by digitalShaman View Post
    three things to note here:
    1.) You should take the effort in your app to track if you need to delete/add/Update a record and do so. deleting all records and recreating them may work in a single user environment but is terrible practice
    2.) A database does not know or care about any order. if you do not specify an "order by" in your select query, it may return rows in any random order. (The fact that there is apperently some order comes from how databases work, but you, as the consumer, must not rely on that)
    3.) Your code should be improved in several ways like using sql parameters and datatables, which in fact can take care of your update/delete/add problem.
    Thanks, I'll look into this.

  8. #8

    Thread Starter
    Lively Member
    Join Date
    Dec 2011
    Posts
    117

    Re: rows in incorrect order when inserting into SQL database

    Quote Originally Posted by digitalShaman View Post
    three things to note here:
    1.) You should take the effort in your app to track if you need to delete/add/Update a record and do so. deleting all records and recreating them may work in a single user environment but is terrible practice
    2.) A database does not know or care about any order. if you do not specify an "order by" in your select query, it may return rows in any random order. (The fact that there is apperently some order comes from how databases work, but you, as the consumer, must not rely on that)
    3.) Your code should be improved in several ways like using sql parameters and datatables, which in fact can take care of your update/delete/add problem.

    Ok, bear with me here.

    With #1. How would I keep track of this? Set functions on the datagrid such as "rowsadded, rowsremoved" and "cellvaluechanged"? I'm guessing as soon as one of these is flagged then do the appropriate function to update the database?

    #3 Can you elaborate on why parameters and datatables would be a benefit here over what we are doing? We are interested in learning.

    after reading https://docs.microsoft.com/en-us/dot...ew=netcore-3.1 I see that you can bind the datatable to the datagrid that the user interacts with. When the user makes changes are they automatically sent to the source, or must you still send an update command?

    Thanks.

  9. #9
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,045

    Re: rows in incorrect order when inserting into SQL database

    I'm always amazed that Data is deleted completly, what if you have to reconstruct Data ?

    the way I handle this, is that in the Database Table there are 2 extra Fields:
    DeletedBy = here I save the UserName
    DeletedDate = a TimeStamp

    so the the Data isn't deleted, it's just not available for the Users anymore
    until the Admin changes those 2 Fields

    but perhaps that's just me
    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.

  10. #10

    Thread Starter
    Lively Member
    Join Date
    Dec 2011
    Posts
    117

    Re: rows in incorrect order when inserting into SQL database

    Quote Originally Posted by ChrisE View Post
    I'm always amazed that Data is deleted completly, what if you have to reconstruct Data ?

    the way I handle this, is that in the Database Table there are 2 extra Fields:
    DeletedBy = here I save the UserName
    DeletedDate = a TimeStamp

    so the the Data isn't deleted, it's just not available for the Users anymore
    until the Adim changes those 2 Fields

    but perhaps that's just me
    Thanks, but I'm not sure that is needed in our case. There is only one person who is actually changing the data. We preform backups very often, so recreating data shouldn't be a problem.

  11. #11
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,045

    Re: rows in incorrect order when inserting into SQL database

    Quote Originally Posted by victorb17 View Post
    Thanks, but I'm not sure that is needed in our case. There is only one person who is actually changing the data. We preform backups very often, so recreating data shouldn't be a problem.
    still sounds like a nightmare to me
    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.

  12. #12

    Thread Starter
    Lively Member
    Join Date
    Dec 2011
    Posts
    117

    Re: rows in incorrect order when inserting into SQL database

    Quote Originally Posted by ChrisE View Post
    still sounds like a nightmare to me
    Can you elaborate why?

  13. #13
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: rows in incorrect order when inserting into SQL database

    Quote Originally Posted by jmcilhinney View Post
    No such word. It's "regardless" or "irrespective". Thus ends the lesson.
    Regardless Of What You Think, 'Irregardless' Is A Word

    https://www.npr.org/2020/07/07/88764...less-is-a-word

    https://www.dictionary.com/e/is-irregardless-a-word/
    Last edited by TysonLPrice; Sep 24th, 2020 at 01:08 PM.
    Please remember next time...elections matter!

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

    Re: rows in incorrect order when inserting into SQL database

    Nothing you've told us justifies Deleting blocks of data and than re-adding them. The DataAdapter is designed to do everything you want.

    Here is a simple example,

    Code:
    Imports System.Data.SqlClient
    
    Public Class DataAdapterDemo
        'I always store my connectionstrings in the project Setting
        Private con As New SqlConnection(My.Settings.BooksDBConnectionString)
        Private da As New SqlDataAdapter("Select BookId, BookName, Author From Books ORDER BY BookName", con)
        Private dt As New DataTable
    
        'I prefer using a BindingSource with the DataGridView because it has many built in function for working/manipulating the data
        Private bs As New BindingSource
    
        'If your only working with one table then you can use a CommandBuilder to create the SQL Add/Update/Delete commands
        Private cmdBuilder As New SqlCommandBuilder(da)
    
        Private Sub DataAdapterDemo_Load(sender As Object, e As EventArgs) Handles Me.Load
    
            Try
                con.Open()
    
                da.Fill(dt)
                bs.DataSource = dt
                Me.DataGridView1.DataSource = bs
    
            Catch ex As Exception
                MessageBox.Show(ex.ToString)
            End Try
        End Sub
    
        Private Sub SaveButton_Click(sender As Object, e As EventArgs) Handles SaveButton.Click
            Try
                bs.EndEdit()
                da.Update(dt)
            Catch ex As Exception
                MessageBox.Show(ex.ToString)
            End Try
        End Sub
    End Class
    with that simple code you can Add/Update/Delete all you want, then just press the SAVE button. Personally I have the changes saved automatically.

  15. #15
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,045

    Re: rows in incorrect order when inserting into SQL database

    Quote Originally Posted by victorb17 View Post
    Can you elaborate why?
    I've took 2 Job's over the last 10 year's (still don't know why I took the second one) trying
    to reconstruct Database from Backup's... and it was a nightmare....
    the person(only 1 Person) had retired who was in charge of the IT, and they Backup's were not as the should be.
    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.

  16. #16
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: rows in incorrect order when inserting into SQL database

    A SQL database backup is full database only.... you can't recover just some data out of one table by doing a restore.... Can it be done yes would I want to do it it depends on what the person/company will pay me for my time. As a DBA doing consulting for someone I normally charge $175/hour (and I have been told I am under charging)
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  17. #17
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: rows in incorrect order when inserting into SQL database

    We cannot figure out why our software scrambles the rows order.
    I'm not a DBA by any means but if the software is inserting rows wouldn't page splits explain not an exact order versus a clean insert in the maintenance step?
    Please remember next time...elections matter!

  18. #18
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: rows in incorrect order when inserting into SQL database

    When you bulk insert the rows they are placed in the order of the PK of the table... (I would normally used a BIGINT IDENTITY(1,1) to define it). The rows might seem out of order when you select them back but they are in order of PK in the database. Now if you use something else as the PK (GUID (UNIQUEIDENTIFIER) or VARCHAR fields) then the order that they are stored in the database (in the table structure based on the B-Tree of the Primary index key) will be something very different then the order that they are inserted in.
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  19. #19

    Thread Starter
    Lively Member
    Join Date
    Dec 2011
    Posts
    117

    Re: rows in incorrect order when inserting into SQL database

    We will try to implement an auto-incrementing key, and use that to sort by.

    However it is very curious to us that our software works flawlessly IF the section of rows that we are reinserting is deleted by the SDF viewer software. If we delete it with our software then the problem occurs. Additionally, if we add any new data that was never deleted (thus has never been in the db), it also works perfect. Also we have noticed that the scrambled data is not randomly scrambled. It is "disordered" in the same way no matter how many times you repeat the process. It seems as if there is something not obvious to us that is triggering, at specific spots, the disordering. One post on the internet said that they had CRLF's that looked like spaces in the data, and that caused this. We have extensively made sure that this is not the case here.

    How can we explain this? Is there another way to delete data from the db other than the way we are currently doing it? Is it leaving it in some memory somewhere?

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