Datagridview help needed - SQL data problems

[o9z]

The problem I am having is this:

I have 2 save buttons on my form. 1 is part of the bindingnavigator and the other is just a simple button(btnSave). btnSave is used to save changes/new records in the datagridview back to the SQL table. The binding navigator save button is used to save Invoice records to a seperate SQL table. When a new invoice is entered, the bindingnavigator save button is clicked, and on this click event, a stored procedure fires and grabs some data from Access that matches the Shipping Report number entered in the data entry part of the app. 1 record is found for each new invoice entered and is then saved to a table called t_groupdata. This record is also displayed in the DGV.

The user can then make any changes to this record in the DGV or add an additional record if needed. Once this is done, they click btnSave to save those changes back to t_groupdata. I am getting multiple records if I save the changes in the DGV and then come back later and make changes to the original Data Entry and use the binding navigator save button. I get double records with each click of the binding navigator save button. Here is my code

    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 btnSave_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

Any explanation or solution to this would be greatly appreciated. I am running out of time and in quite a pickle!

 

[o9z]

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?

 

[cjard]

You are on .NET 2, and mention BindingNavigator which seems to imply that youre doing data access correctly,but then your button click handler code is full of connection strings, and sql statements and parameter addition etc, which kinda implies youre doing your data access incorrectly (old way)

Umm.. so basically, I'd sort that part out and do it properly.. ie Do it consistently .NET 2 way.. Use the tableadapter to save the records.
I would also consider redesigning the app NOT to have 2 save buttons that do different things!