[RESOLVED] DataGridView - Save Button click event replicates data in DGV

[o9z1]

I have changed my code recently, so this post has changed

I have an app that is getting pretty large and one problem I am having is this:

One piece of my code on a save button exceutes a stored procedure and checks a textbox value against a database and looks for an entry in the table matching that text value. If it exists it does nothing....if it doesn't exist, it inserts the record into the database. After that is done, more code is executed and selects the data from the database and populates the datagrid. If I click the save button multiple times, the datagridview keeps increasing with records by 1 per save on a record that already exists in the table. When I close the app and reopen it, it only shows the single record though. Here is the code I am using


I have these declared at class level

    Dim ds1 As New DataSet
    Dim strSQLConn2 As String = "Data Source=HERCULES;Initial Catalog=Carcass2;Integrated Security=True"
    Dim cn2 As New SqlConnection(strSQLConn2)

Private Sub KILSHEETBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bnBtnSave.Click
        Dim strSQLConn1 As String = "Data Source=HERCULES;Initial Catalog=ShippingReportData;Integrated Security=True"
        Dim cn1 As New SqlConnection(strSQLConn1)
        Dim command As SqlCommand = New SqlCommand("sp_CarcCombo", cn1)

        cn1.Open()
        command.CommandType = CommandType.StoredProcedure
        command.Parameters.AddWithValue("@KsID", txtKsId.Text)
        command.ExecuteNonQuery()
        cn1.Close()
        cn1.Dispose()

        Dim strSQLConn2 As String = "Data Source=HERCULES;Initial Catalog=Carcass2;Integrated Security=True"
        Dim cn2 As SqlConnection = New SqlConnection(strSQLConn2)
        Dim da1 As New SqlDataAdapter("SELECT ksID, ShipRptNo, Site, Barn, id, head, weight FROM t_groupdata WHERE ksID = " & txtKsId.Text, cn2)

        da1.SelectCommand.CommandType = CommandType.Text
        da1.AcceptChangesDuringFill = False
        da1.Fill(ds1, "groupdata")

        Me.GroupDataList.DataSource = ds1
        Me.GroupDataList.DataMember = "groupdata"
End Sub

Private Sub btnSaveGroup_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSaveGroup.Click
        Dim da1 As New SqlDataAdapter("SELECT ksID, ShipRptNo, Site, Barn, id, head, weight FROM t_groupdata WHERE ksID = " & txtKsId.Text, cn2)
        Dim cb As New SqlCommandBuilder(da1)
        da1.Update(ds1, "groupdata")
    End Sub
[\code]
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[sp_CarcCombo]
    @KSId int
AS

IF (SELECT Count(*) AS RecCount FROM Carcass2.dbo.t_GroupData
LEFT JOIN Carcass2.dbo.Kilsheet
ON Carcass2.dbo.t_GroupData.ksid = Carcass2.dbo.Kilsheet.kilsheetid
WHERE
Carcass2.dbo.Kilsheet.kilsheetid = @KSid AND Carcass2.dbo.t_GroupData.shiprptno = Carcass2.dbo.Kilsheet.ship_report)
< 1

BEGIN
    INSERT INTO Carcass2.dbo.t_groupdata (ShipRptNo, ksID, Site, Barn, ID, Head, Weight)
    SELECT HeadShipped.ShipRptNo As ShipRptNo, Carcass2.dbo.Kilsheet.KilsheetID AS ksID, HeadShipped.Site, HeadShipped.Barn, ProdData.dbo.vOpenNonDupGroupIDs.LotNum AS ID, HeadShipped.Head AS Head, [Shipping Rpt].LiveWt AS weight
    FROM (HeadShipped LEFT JOIN [Shipping Rpt] ON HeadShipped.ShipRptNo=[Shipping Rpt].ShipRptNo) LEFT JOIN ProdData.dbo.vOpenNonDupGroupIDs ON HeadShipped.Site=ProdData.dbo.vOpenNonDupGroupIDs.fSite AND HeadShipped.Barn=ProdData.dbo.vOpenNonDupGroupIDs.Barn LEFT JOIN Carcass2.dbo.Kilsheet ON Carcass2.dbo.Kilsheet.ship_report=Headshipped.ShipRptNo
    WHERE Carcass2.dbo.Kilsheet.KilsheetID=@KSid
END

And here is the stored procedure I am using

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[sp_CarcCombo]
    @KSId int
AS

IF (SELECT Count(*) AS RecCount FROM Carcass2.dbo.t_GroupData
LEFT JOIN Carcass2.dbo.Kilsheet
ON Carcass2.dbo.t_GroupData.ksid = Carcass2.dbo.Kilsheet.kilsheetid
WHERE
Carcass2.dbo.Kilsheet.kilsheetid = @KSid AND Carcass2.dbo.t_GroupData.shiprptno = Carcass2.dbo.Kilsheet.ship_report)
< 1

BEGIN
    INSERT INTO Carcass2.dbo.t_groupdata (ShipRptNo, ksID, Site, Barn, ID, Head, Weight)
    SELECT HeadShipped.ShipRptNo As ShipRptNo, Carcass2.dbo.Kilsheet.KilsheetID AS ksID, HeadShipped.Site, HeadShipped.Barn, ProdData.dbo.vOpenNonDupGroupIDs.LotNum AS ID, HeadShipped.Head AS Head, [Shipping Rpt].LiveWt AS weight
    FROM (HeadShipped LEFT JOIN [Shipping Rpt] ON HeadShipped.ShipRptNo=[Shipping Rpt].ShipRptNo) LEFT JOIN ProdData.dbo.vOpenNonDupGroupIDs ON HeadShipped.Site=ProdData.dbo.vOpenNonDupGroupIDs.fSite AND HeadShipped.Barn=ProdData.dbo.vOpenNonDupGroupIDs.Barn LEFT JOIN Carcass2.dbo.Kilsheet ON Carcass2.dbo.Kilsheet.ship_report=Headshipped.ShipRptNo
    WHERE Carcass2.dbo.Kilsheet.KilsheetID=@KSid
END

How can I make sure the datagridview only shows the actual number of records in the t_groupdata table while still allowing user interaction of the datagridview as well? I want the end user to be able to manually add data.

[o9z1]

any help with this?

[techgnome]

Everytime you execute the fill.... it adds to the DS already there.... you need to .Clear it first ... preferably after saving the data.

-tg

[o9z1]

This problem is still occuring - I tried putting a clear in there, but every save, the records double. It will start by putting 2 records(identical) in the database, the next save = 4...then 8....then 16....etc. It keeps doubling the records saved and they are identical except the autonumber PK

Here is my whole save button code

         Dim ds1 As New DataSet

    Private Sub KILSHEETBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bnBtnSave.Click
        Me.Validate()
        Me.bsKilSheet.EndEdit()

        Try
            Me.taKilsheet.Update(Me.DsKilsheet.KILSHEET)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        ''-------------------------------------------
        Dim strSQLConn1 As String = "Data Source=HERCULES;Initial Catalog=ShippingReportData;Integrated Security=True"
        Dim cn1 As SqlConnection = New SqlConnection(strSQLConn1)
        Dim command As SqlCommand = New SqlCommand("sp_CarcCombo", cn1)

        cn1.Open()
        command.CommandType = CommandType.StoredProcedure
        command.Parameters.AddWithValue("@KsID", txtKsId.Text)
        command.ExecuteNonQuery()
        cn1.Close()
        cn1.Dispose()

        Dim strSQLConn2 As String = "Data Source=HERCULES;Initial Catalog=Carcass2;Integrated Security=True"
        Dim cn2 As SqlConnection = New SqlConnection(strSQLConn2)
        Dim da1 As New SqlDataAdapter("SELECT ksID, ShipRptNo, Site, Barn, id, head, weight FROM t_groupdata WHERE ksID = " & txtKsId.Text, cn2)
        Dim builder As New SqlCommandBuilder(da1)
        da1.SelectCommand.CommandType = CommandType.Text
        da1.AcceptChangesDuringFill = False
        da1.Fill(ds1, "groupdata")
        da1.Update(ds1, "groupdata")

        Me.GroupDataList.DataSource = ds1
        Me.GroupDataList.DataMember = "groupdata"

        Dim con As Integer

        con = Me.BindingContext(ds1, "groupdata").Count

        If con = 0 Then
            MessageBox.Show("Kilsheet could not be found")
        End If

        GroupDataList.Rows(GroupDataList.CurrentRow.Index).Cells("ID").Value.ToString()
        Dim contractConn As String = "Data Source=HERCULES;Initial Catalog=ProdData;Integrated Security=True"
        Dim conContract As SqlConnection = New SqlConnection(contractConn)
        Dim dsContract As New DataSet
        Dim daContract As New SqlDataAdapter("SELECT WhichContract from vCarcassContract2 WHERE LotNum = '" & GroupDataList.Rows(GroupDataList.CurrentRow.Index).Cells("ID").Value.ToString & "'", conContract)
        daContract.Fill(dsContract, "Contract")
        ContractComboBox.DataSource = dsContract.Tables("Contract")
        ContractComboBox.DisplayMember = "WhichContract"

        ds1.Clear()
        cn2.Dispose()
        cn2.Close()

    End Sub

[o9z1]

I have verified that my stored procedure is producing the results I am wanting. Am I clearing the ds like I should be? I am still getting double records per save. I start with 2 records(should be 1), then if I save again, I have 4...then 8...16...etc.

[techgnome]

What's with this?

        da1.Fill(ds1, "groupdata")
        da1.Update(ds1, "groupdata")

What is it you are trying to accomplish? You fill it.... there's no need to .Update untill AFTER you have 1) added data 2) deleted data or 3) changed it.

-tg

[o9z1]

What I am trying to accomplish is this:
1. Stored proc executes and grabs records from an Access Database which is then displayed in a datagridview(This works)

2. The user then can work with the data within the datagridview(add additional details to the record or add another record with the same tracking number)

3. The data is then saved to a separate SQL database. Regardless of if any changes are made by the user, the original data still has to be saved to SQL.

Everything is working fine(The stored proc is grabbing the correct data and the data is displayed correctly in the dgv) until I try to save the final record(s) to SQL. The records are getting stored, but I am getting multiples. The first save yields 1 record in SQL(Assuming another record wasn't added by the user), but if the record is pulled back up in the application and saved again, it then duplicates everything. SQL now shows 2 records for that particular ID. If I edit and save again, I now have 4. Notice it doesn't increment by 1, but by double.

[o9z1]

should I have my ds.Update in a seperate procedure? Am I trying to do too many things on the save button click event?

[techgnome]

Here's what you should be doing:
1) open the connection
2) Fill your dataset
3) Bind it to the grid
4) Close the connection
Then that's it.,... you're done....
Let the user make changes
Then when they go to save....
1) Open the connection
2) Reconnect your data set to the data adaptor
3) Update
Close everything....

The problem as I see it.... is that you are pulling from Access, but want to save to SQL Server.... which I don't get.

Also, you haven't given the adapter any InsertCommands or UpdateCommands.

I suggest you look throug the Data base section of the forums. There are a number of tutorials on how to do this.

-tg

[o9z1]

Why is there a problem pulling from Access and saving to SQL? SQL can do this just fine, which is why I am using a stored procedure. The stored procedure dumps the original data in SQL, so vb.net is only seeing the SQL side. The reason I do it this way is because I have to. The Access table has to remain unchanged for another system. Any changes made through this app replicate the data and contain the changes as well.

[techgnome]

Here's the deal.... if I pull a record from Access.... make changes.... and dump it into SQL Server.... and then come back, pull that same record again from access... make more changes... and push it to SQL Server.... how does it know what row to update? It doesn't...so new row gets added.....
Meanwhile, you're not .Clear ing before .Fill ing..... so the same record keeps loading into the DataSet... BTW - the .Update you are using after the .Fill sends the changes (even if there aren't any) back to the access table.

-tg

[o9z1]

My stored procedure handles all of that. If the record already exists in SQL, it doesn't add it again. If the record count > 1 then the insert statement doesn't execute. So when the app runs, and I pull up a specific record, the stored proc executes...it notices the record already exists in SQL so doesn't run the insert, and then the rest of my code runs in vb. The datagridview grabs the data already in the SQL table and displays it.

[techgnome]

I see alot of selecting going on, and one stored proc that doesn't return anything...And I don't see any updating going on, except right after the .Fill, which was never cleared out before begin filled.... so I'm still confused about that bit.

-tg

[o9z1]

I am not sure that it matters much, but I was using the CommandBuilder and I somehow left that line out of the code. It was as follows

Dim builder As New SQLCommandBuilder(da1)

I was assuming this would take care of my commands for me.

[o9z1]

Ok, I fixed the problem by removing my update and the command builder. Now I am not replicating data, and 1 row is being saved. The only thing not happening now is when I add another row to the datagrid and try to save it, the database is not updated. Any documentation or tips for that? I have looked a few times.

[o9z1]

I think this is because currently I have no update command and the original record being saved is being done in the stored proc. Since I have 1 row already in the DGV, and I add another, when I do my update, will it start replicating rows again?

The fields I need to update are:
KsID, ShipRptNo, Site, Barn, ID, Head, Weight

Into a SQL table called t_groupdata

The PK of the table is an autonumber field called pkInt

[o9z1]

I have recently changed my code and I am getting the same results. I have no idea what I am doing wrong here.

[o9z1]

I once again tested my SQL stored procedure and it is returning what it should. The code is obviously the problem, but I guess the solution is not too obvious to me! Anyone else have an idea?

[o9z1]

Anyone?

[o9z1]

Ok, I have narrowed the problem down to my save button where the update is being done. I only get duplicate records if I run the update multiple times. Any ideas on the logic I could use to ensure that the record that is stored in SQL remains the lone record?

The binding navigator save button can be pressed as many times as I wanted and the only thing it could be doing to affect the other button is filling the dataset.

[o9z1]

Ok, I had acceptchangesduringfill = false, and by taking this out and letting it default to true, I am no longer getting duplicate records. By doing this though, I am now getting the following error when I try to make changes and save the changes on a record displayed in the DGV.

"Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information"

Now I assume this is because I am not selecting the Primary Key field. What is the best solution to this? The primary key field is simply an autonumber in my SQL table. Would it be best to add the primary key field to the select statement but not display it in the DGV? I am not sure how to do that.

[techgnome]

select it.... then hide to column in the grid.

-tg