(Resolved) Update Database through Datagridview

[FastEddie]

I am trying to update the database through my datagrid view and keep getting an error. What am I doing wrong?

Public Class frmMain

    Private Sub frmMain_Load( _
        ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles MyBase.Load

        Me.daCoupons.Fill(Me.dsCoupons.tblCouponRedeptionData)
        With dgvCouponCodes
            .Columns.Item(0).HeaderText = "Coupon ID"
            .Columns.Item(0).Width = 75
            .Columns.Item(1).HeaderText = "Group"
            .Columns.Item(1).Width = 80
            .Columns.Item(2).HeaderText = "Vehicle"
            .Columns.Item(2).Width = 135
            .Columns.Item(3).HeaderText = "Distribution"
            .Columns.Item(3).Width = 70
            .Columns.Item(4).HeaderText = "Cost"
            .Columns.Item(4).Width = 70
            .Columns.Item(5).HeaderText = "Notes"
            .Columns.Item(5).Width = 225
            .Columns.Item(6).HeaderText = "In House?"
            .Columns.Item(6).Width = 75
            .Columns.Item(7).HeaderText = "Show Coupon?"
            .Columns.Item(7).Width = 85
        End With
    End Sub

    Private Sub btnSave_Click( _
        ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles btnSave.Click

        Dim answer As Integer
        If dsCoupons.HasChanges Then
            answer = MessageBox.Show( _
                "Confirm Update", _
                "Update Confirmation", _
                MessageBoxButtons.YesNo)
            If answer = vbYes Then
                Try
                    dsCoupons.AcceptChanges()
                    daCoupons.Update(dsCoupons)
                Catch ex As Exception
                    MessageBox.Show( _
                        ex.Message & vbNewLine & _
                        ex.Source, "Error", _
                        MessageBoxButtons.OK, _
                        MessageBoxIcon.Error)
                End Try
            End If
        End If
    End Sub
End Class

[nmadd]

Would you like to say what error you are getting?

[onlyGirl]

There are plenty of examples dealing with updating data by the datagrid. Search the forum.

[FastEddie]

Oops. I just realized I fixed the error just before I posted this code. My problem is that the code appears to work i.e. no errors but the data changes are not saved back to the database.

[FastEddie]

onlyGirl I have searched the forum along with Google, which is how I put together the code I have so far. This particular datagridview is filled with data created by the VS 2005 Wizard and I so far I have been unable to make code samples geared toward building the ds in code work with the wizard.

[nmadd]

Oops. I just realized I fixed the error just before I posted this code. My problem is that the code appears to work i.e. no errors but the data changes are not saved back to the database.

Since you are receiving no errors, here's a guess: When you added you database to your project, you clicked "Yes" when asked if you wanted to copy your database to the output directory every time your project was run. That is it's possible you are making the changes correctly, but not seeing them because your database is being overwritten every time your run your project.

See the "Every time I test my application and modify data, my changes are gone the next time I run my application" issue at the bottom of this article:
http://msdn2.microsoft.com/en-us/lib...17(VS.80).aspx

[FastEddie]

nmadd thanks but I don't think that is the issue. This is a SQL server database (I think copying the file localy only happens with Access) and as a double check there is no database file located in my Solution Grouping in Solution Explorer.

[FastEddie]

Never mind. I just deleted all the Wizard stuff and wired it by hand.

        If ds.HasChanges() Then
            Dim response As Integer
            response = MessageBox.Show("Save changes?", "Action Prompt", MessageBoxButtons.YesNo)
            If response = MsgBoxResult.Yes Then

                Dim da As SqlDataAdapter
                Dim cb As SqlCommandBuilder
                da = New SqlDataAdapter("Select " & _
                "CouponID," & _
                "CouponGroup," & _
                "MarketingLabel," & _
                "DistributionQty," & _
                "ProgramCost," & _
                "Notes," & _
                "IsInHouse," & _
                "IsUseInReport " & _
                "From tblCouponRedeptionData Order by CouponID", strCon)
                da.TableMappings.Add("Table", "Coupons")
                Try
                    cb = New SqlCommandBuilder(da)
                    da.Update(ds)
                Catch ex As Exception
                    MsgBox("Source: " & ex.Source & "." & "Message: " & _
                        ex.Message & ".", MsgBoxStyle.Critical, "SQL Error")
                End Try
            Else
                Exit Sub
            End If
        Else
            MessageBox.Show("No Changes were Made", "No Changes", MessageBoxButtons.OK)
        End If
        dgvCouponCodes.Refresh()
    End Sub