[RESOLVED] Duplicate records in a table

[gwboolean]

Recently made the move to from ACCESS to SQL and have run into a problem that I completely do not understand. The table is structured as can be seen below.

I use the table for a dgv list that has frequently changing data. The table displays selected elements of the table when required. The rows consist of information from two different tables.

This table is displayed as can be seen below. One of the choices with the list is the ability to add a record. I had already worked out the part about adding a record. However, I was at the point that I needed to figure out how to keep the application from duplicating any existing records. As part of that process, I intentionally duplicated one of the records. This is where the problem with the database occurred.

The problem is that now that I have duplicate records, I am unable to delete either one or both of them and get the error seen below. That, by the way, is the error thrown from SQLExpress. The error that is thrown when I try to do the same thing in Visual Studio is worded a bit differently but says the same thing. Visual Studio did give me a place to look up information, but that was completely unhelpful and irrelevant, as far as I could tell.

So here is my question. I have two identical records in the same table. I am unable to delete either one or both of them. I have no idea how to correct this and be able to use the table. I would also like to know why the table will not accept identical records. I have set nothing up to keep it from doing that and do not understand why the table would quibble over having a duplicate record. I can think of many reasons why that might sometimes be desirable, although not for my application.

[jmcilhinney]

Recently made the move to from ACCESS to SQL

Presumably you actually mean SQL Server. SQL is a language used to write queries against nearly every database, including Access. SQL Server is Microsoft's enterprise grade RDBMS. Far too many people lazily refer to it as just SQL and this often causes confusion. At the very least, I recommend referring to it as MS SQL, as many people do, so it's clear that you're not talking about the language or one of the many other databases with SQL in the name. For example, there's often confusion between MySQL and SQL Server because so many people are lazy with the names. /end rant

[jmcilhinney]

As for the problem, we're going to need to see some code because that's where the issue lies. It seems clear that you're generating multiple rows in your output from a single row in your input and that is creating an issue when you try to delete a row that cannot be uniquely identified. The specific solution depends on the specifics of what you're doing though, so we need to see that. We need to code to load the data, including SQL code, and code to save changes.

[gwboolean]

I don't understand how this can be code related, but here is the code that I am using to actually add a row to the table. I assume that is what you want.

I am still working out how I intend to do this, but this is the process as it is at this moment. Even I can see that there is a lot of crap in there, but I will get to that when I figure out this issue.

When I want to add a record to the table shown above, I have to open another DGV for another table that contains what I am looking for. In this other DGV (not shown), I select the one I want by clicking in the row and then parts from that row are used to populate a new row to the table displayed in the form below. The table is adjVendor and is displayed above. The process works, but clearly needs a lot of cleaning up. I have not yet gotten to the point of determining how to keep from duplicating a record, as I am still figuring out how I want to do that. I was unaware that duplicating a record could even be a problem.

So in this code, I run AddVendor(VendorID), VendorID being derived from that value in the DGV seen below, which simply takes the variables and parameters them into the table.

    Private Sub dgvList_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles dgvList.CellClick
        If e.RowIndex < 0 Or e.ColumnIndex < 0 Then Exit Sub 'PREVENTS OUT OF INDEX RANGE ERRORS
        If MyState <> "Add" Then SystemID = CStr(Me.dgvList.Rows(e.RowIndex).Cells(0).Value.ToString)
        If CheckRec = CStr(Me.dgvList.Rows(e.RowIndex).Cells(0).Value.ToString) Then
            VendorID = CStr(Me.dgvList.Rows(e.RowIndex).Cells(0).Value.ToString)
            Select Case MyState
                Case = "Add"
                    'Called from partRecord for adding an existing vendor as supplier of part.
                    'Add record for adjVendor for this part.
                    If MyState = "Add" Then
                        'partRecord.setVendorQuery(SystemID) 'Finds selected record by systemID from adjVendor
                        partRecord.AddVendor(VendorID)
                        partRecord.setVendorQuery(SystemID)
                        partRecord.Refresh()
                        partRecord.RefreshDGV()
                    End If
                    Me.Close()
                    partRecord.Show()
                Case Else
                    'Called from main menu to display vendList
                    vendRecord.Show()
            End Select
            MyName = CStr(Me.dgvList.Rows(e.RowIndex).Cells(1).Value.ToString)
        Else
            MsgBox("Duplicate Vendor.  Cannot add to list.")
            Me.Close()
            Exit Sub
        End If
    End Sub

    Public Sub AddVendor(ByVal VendorID As String)
#Region "Add Params"
        MasterBase.AddParam("@vendor", VendorID)
        MasterBase.AddParam("@name", MyName)
        MasterBase.AddParam("@recno", SystemID)
#End Region
#Region "Add New Vendor Query"
        MasterBase.MasterBaseQuery("INSERT INTO adjVendor (colVendorID,colName,colSystemID) " &
                                   "Values (@Vendor,@name,@recno); ")
        'Report & Abort on errors
        If ErrorReport.NoErrors(True) = False Then Exit Sub
#End Region
        RefreshDGV()
    End Sub

    Private Sub btnAdd_Click(sender As Object, e As EventArgs) Handles btnAdd.Click
        'add new vendor
        MyState = "Add"
        tlsSelect.Visible = False
        vendList.Show()
        RefreshDGV()
    End Sub

A last note. About your rant. I am glad you got that out of your system. For my own peace of mind, I ignored it because I knew that it would piss me off. I think we were both well served.

[jmcilhinney]

The error message specifically relates to deleting a record but I see nothing about deleting records in the code you provided. I specifically asked for the code that loads the data but I don't see that either. What appears to be happening is that you're loading the same data twice, likely due to a join, and then trying to delete one of those rows but the identifying data fro that row matches another row as well. The system is trying to stop you implicitly deleting data you didn't mean to delete. That's just a guess though, because you haven't actually provided the information requested.

A last note. About your rant. I am glad you got that out of your system. For my own peace of mind, I ignored it because I knew that it would piss me off. I think we were both well served.

So you're not prepared the to make a tiny effort to avoid potential confusion and thus waste your time and that of the people trying to help you? Good to know.

[gwboolean]

I see nothing about deleting records in the code you provided.

Here you go.

    Public Sub RemoveVendor()
        MasterBase.AddParam("@recno", VendorID)
        MasterBase.AddParam("@part", SystemID)
        MasterBase.MasterBaseQuery("DELETE FROM adjVendor " &
                               "WHERE colVendorID=@recno AND colSystemID=@part")
        'Report & Abort on errors
        If ErrorReport.NoErrors(True) = False Then Exit Sub
        setVendorQuery(SystemID)
        RefreshDGV()
    End Sub

I specifically asked for the code that loads the data but I don't see that either.

I thought that the AddVendor() query above did that. Perhaps if you clarified your request. I am unclear what you might be referring to.

What appears to be happening is that you're loading the same data twice

Do you mean that I am adding the same data twice? If that is the case, that is exactly what has been done. The data was already in the adjVendor table from a previous addition. What I did was try to add the data a second time, which was successfully done. I ran the remove routine, and that was how I discovered that there was a problem. I then went to the table and tried to remove them manually, and failed, thus getting the error. There was an equivalent error from Visual Studio.

That's just a guess though, because you haven't actually provided the information requested

I would be very much happy to provide the requested information when I am made aware of what the requested information is. Otherwise, I must guess as to what is required.

So you're not prepared the to make a tiny effort to avoid potential confusion and thus waste your time and that of the people trying to help you? Good to know.

Since you insist, I did review your rant. Perhaps you can inform me as how you are being helpful and a valuable use of my time to read your missive about lazy people, specifically me, that are wasting your time by not speaking the particular jargon that you prefer? You managed to piss me off and at the same time offer nothing of value.

[techgnome]

The problem is the table definition - you're asking SQL Server to delete a row ... but there isn't enough information for it to know which row you want deleted. that's what hte error is telling you. You asked to delete a row with this info, but there's two (or more) rows that fit that criteria, so it's refusing to delete either one. What you should do is add another field to the table to act as a unique key - could an integer that autoincrements, or is a guid that you generate, what ever ... somehting that came be marked as a key for the table, then use that to tell what row(s) to delete.


-tg

[OptionBase1]

Maybe I'm missing something, but this doesn't seem that complicated, and I'm not convinced that any code is directly at fault here. I think the fault is that you seem to not have a Primary Key defined for this table, which would have prevented you from creating a duplicate row like this.

You haven't explicitly stated how you are trying to "fix" this in SQL Management Studio (SSMS), but if you are simply trying to execute the same Delete query in SSMS as you are in VB.NET, then I would expect the outcome you are seeing.

My suggestion would simply be to try this:

Inside of SSMS:
-Drill down into the affected table, right-click the table and choose "Edit Top 200 Rows". If you have more than 200 rows in this table, you'll have to then modify the properties of the query that is ran to increase 200 to whatever the appropriate number of results is.
-Locate the duplicate row, right-click that row, and choose delete.
-If that doesn't work, then try modifying the fields in this row so that it is no longer a "duplicate", move focus to a different row to ensure that the changes take effect, and then try to delete the row that "was" the "duplicate".

[gwboolean]

The problem is the table definition - you're asking SQL Server to delete a row ... but there isn't enough information for it to know which row you want deleted.

I get that. So why would it not just delete both rows?

What you should do is add another field to the table to act as a unique key

I can do that. I have actually had those, in the past, but I never could figure out what the usage would be, in this type of table and used the way this table is used. But with the issue as you describe, I can readily see where an integered numbered, primary key field that is used for nothing else would resolve that.

Maybe I'm missing something, but this doesn't seem that complicated, and I'm not convinced that any code is directly at fault here. I think the fault is that you seem to not have a Primary Key defined for this table, which would have prevented you from creating a duplicate row like this.

That is true. I intentionally have no primary key, as all of the data in the 3 fields will be duplicated.

Locate the duplicate row, right-click that row, and choose delete.

I did that. The table will not allow me to delete either of the rows. That is when the error message displays.

-If that doesn't work, then try modifying the fields in this row so that it is no longer a "duplicate",

Tried that too. It will now let me do that. Short of rebuilding the table, I have found no way to eliminate the extra row.

I think that what I will do, based on your input, is to create a self-filling integer primary key field. However, until I can eliminate the duplicate row, the table won't let me do anything (not true, I can do anything I would normally do with the table, except alter/delete either of the duplicate rows. And whatever I do with either of the two records is not accepted by the table.

I am very unfamiliar with this database. I have been using ACCESS. In ACCESS this field was automatically generated in any table I built. How do I make this primary field self-generate when I create a new record?

So, short of just junking the table and rebuilding it, is there a way that I can deal with the duplicate row? Junking the table would not be a big deal, but I would like to be able to know how to fix this, when I inevitably do this again.

[OptionBase1]

The fix is to design the table correctly before you start populating it.

You seem to contradict yourself in your post, saying you intentionally have no primary key and that the data will be duplicated. Then, you talk about wanting "this primary field" to exist in your table.

Are you saying that you want an auto-populating, auto-incrementing primary key field to exist, but right now that field doesn't exist because you don't know how to create it?

Inside of SSMS:
-Right-click the table and choose "Design"
-Right-click the top field in the list and click "Insert Column" to make this new column the "First" one in the table
-Give this column the desired name and appropriate numeric type
-In the Column Properties window in the bottom of the screen, scroll down and expand the "Identity Specification" section
-Set (Is Identity) to Yes
-Set Identity Increment to 1 if you want the unique ID to increase by 1 for each new record
-Set the Identity Seed value to whatever value you want assigned to the very first record in the table
-Right-click this new field and choose "Set Primary Key"

Good luck.

Edit to add: I'm referencing SSMS 2008 R2 for the above steps. I can't guarantee that the wording and locations of things I outline above will be the same in whatever version of SSMS you are using, if different.

[techgnome]

I think that what I will do, based on your input, is to create a self-filling integer primary key field. However, until I can eliminate the duplicate row, the table won't let me do anything (not true, I can do anything I would normally do with the table, except alter/delete either of the duplicate rows. And whatever I do with either of the two records is not accepted by the table.

I am very unfamiliar with this database. I have been using ACCESS. In ACCESS this field was automatically generated in any table I built. How do I make this primary field self-generate when I create a new record?

So, short of just junking the table and rebuilding it, is there a way that I can deal with the duplicate row? Junking the table would not be a big deal, but I would like to be able to know how to fix this, when I inevitably do this again.

If the data wasn't supposed to be duplicated, and the combination of those three fields will ALWAYS be unique, and you don't want duplicates... you can select all three fields and set them to be a combined unique primary key ... that will prevent duplocates going forward ... but won't clean up existing data ... so to clean it up, you may need to create a new table with the correct structure and keys on it, select DISTINCT data from the old table into the new one ... drop the old table, rename the new one to match the old one ....
Marking the three fields as a composite primary unique key, if you then try to add the same data, you'll get a unique key constraint error ... and it shouldn't allow the entry of the dupe data.

-tg

[gwboolean]

The fix is to design the table correctly before you start populating it.

That is true. But it was not done that way. I am just someone who is a hobbyist playing with some ideas.

You seem to contradict yourself in your post, saying you intentionally have no primary key and that the data will be duplicated. Then, you talk about wanting "this primary field" to exist in your table.

There is no contradiction there. When I built the table I saw no need for a primary key field because of what I use this table for. I have since learned that if I want to keep this issue from occurring, I need to have a field with a primary key, or at least maintain a unique field.

This table contains values from several tables. Those values are primary key fields in the tables they come from. However, in this particular table you might see those values duplicated many times. I did not recognize the need for a primary key in this table until I duplicated a record and found that a table will not accept a duplicate record (that is a misstatement, as the record was duplicated, and I just couldn't get rid of the duplicate record). What I have done to remedy that was to create an extra integer field that I setup in the properties to self-increment by 1.

Are you saying that you want an auto-populating, auto-incrementing primary key field to exist, but right now that field doesn't exist because you don't know how to create it?

Yes, but I have figured that out and implemented that. And thank you for that suggestion, as that was exactly what was needed.
ith a primary key. I have also been able to realize that the colSystemID, and colVendorID, when considered together, must be unique. Prior to this issue, I saw no reason to even have a primary key for this table. Considering the fact that the only value of a primary key, in this particular table, for me is to ensure that I do not commit the error that I have already committed by accidentally, or purposely, creating a duplicate record.

If the data wasn't supposed to be duplicated, and the combination of those three fields will ALWAYS be unique, and you don't want duplicates...

I did end up junking the table and rebuilding a new one. However, I duplicated the data again (this prior to creating an auto-increment primary field) in the new table, and finally figured out how to remove the duplicate without junking the table.

I think that in the long term, your suggestion to take all three fields and make that into a single unique unit is the best solution. Actually, just two of the fields would be what is required, now that I can see what I am really trying to do with the table.

Now I understand why the table will not accept duplicate records and why it is not a good idea not to have a primary key field. Thanks for the input.

[OptionBase1]

This table contains values from several tables. Those values are primary key fields in the tables they come from. However, in this particular table you might see those values duplicated many times.

In that context it is called a foreign key.

I did not recognize the need for a primary key in this table until I duplicated a record and found that a table will not accept a duplicate record (that is a misstatement, as the record was duplicated, and I just couldn't get rid of the duplicate record). What I have done to remedy that was to create an extra integer field that I setup in the properties to self-increment by 1.