Delete record / SQL server using vb.net

[elianeasmar]

Hello. I meed some help with this.
I am trying to delete a record from database(SQL Server)
Am i missing something?

     Private Sub cmdDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdDelete.Click
            _DataSet.Tables(0).Rows(CInt(txtCurrent.Text) - 1).Delete()
            ' tho, it can remove the deleted rows
            ' we cannot call the DataSet.AcceptChanges method
            ' because the DataAdapter would not recognize the delete row
            ' by the time DataAdapter.Update(DataSet) is called.
            EnableNavigation()
            cmdSave.Enabled = True  ' let user update the underlying database
            ' after deleting the current record, the current record still points to the
            ' deleted record (though it cannot be updated). 
            ' The user must MoveNext/Back to view other records.
        End Sub

txtcurrent. is a textbox that contains the current record number

[Begginnerdev]

You are not updating the table, you are just deleting the local copy.

To update the table, issue an update with a data adapter.

da.UpdateCommand = New OleDBCommandBuilder(da).GetUpdateCommand
da.Update(_DataSet)

[elianeasmar]

I am usin SQL server. so what should i put instead of: OleDBCommandBuilder ?
and dais a dataadapter?
Thank you :)

[elianeasmar]

I managed to write this code:

Dim strconnection As String = "Data Source=EASMAR-PC;Initial Catalog=DatabaseConnection;Integrated Security=True;"
    Dim _cn As SqlConnection = New SqlConnection(strconnection)
    Dim cmd As New SqlCommand
    _cn.Open()
    Try
        _DataSet.Tables(0).Rows(CInt(txtCurrent.Text) - 1).Delete()

        Dim adapter As New SqlDataAdapter
        Dim cmdBuilder As New SqlCommandBuilder(adapter)
        Dim DutyDetails As String = "SELECT ID from tblCustomer"

        adapter.SelectCommand = New SqlCommand(DutyDetails, _cn)
        adapter.UpdateCommand = cmdBuilder.GetUpdateCommand
        adapter.DeleteCommand = cmdBuilder.GetDeleteCommand

        Dim cb As SqlCommandBuilder = New SqlCommandBuilder(adapter)

        adapter.Update(_DataSet.Tables("0"))

Am i missing something ?

[Begginnerdev]

You can write a function to issue the update if you wish, something like:

Protected Friend Sub UpdateTable(ByVal da As SqlDataAdapter, ByVal ds As DataSet)
    Try
        If Not IsNothing(da) Then
            If Not IsNothing(ds) Then
                da.UpdateCommand = New SqlCommandBuilder(da).GetUpdateCommand
                da.Update(ds)
            Else
                Throw New ArgumentException("The dataset can not be null!")
            End If
        Else
            Throw New ArgumentException("The data adapter can not be null!")
        End If
    Catch ex As Exception
        MsgBox(ex.ToString)
    End Try
End Sub

Your update code will then look something like:

Private Sub cmdDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdDelete.Click
    Try
        ds.Tables(0).Rows((CInt(txtCurrent.Text) - 1).Delete()
        UpdateTable(New SQLDataAdapter(myConnetion),ds)

        EnableNavigation()
        cmdSave.Enabled = True
    Catch ex As Exception
        MsgBox(ex.ToString)
    End Try
End Sub