Question Delete a record from a database

[elianeasmar]

Hello. I have this code to delete a record from m database(i am using SQL Server)
am i missing something. Because it wont delete the record
And i have no errors. It just don't delete the record.

   Private Sub cmdDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdDelete.Click
        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()
        cmd.CommandText = "delete from tblCustomer where ID=" & txtCurrent.Text & ";"
        cmd.Connection = _cn


        If MessageBox.Show("Do you really want to Delete this Record?", "Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = DialogResult.No Then
            MsgBox("Operation Cancelled")
            Exit Sub
        Else
            cmd.ExecuteNonQuery()
            Me._DataAdapter.Fill(Me._DataSet)
        End If
End Sub

Am i missing something ?
Thank you

 

[jmcilhinney]

If you have a DataSet then why are you using ExecuteNonQuery? Call the Delete method of the appropriate DataRow and then call Update on your data adapter. That's what it's for. If you are binding your data then bind it to a BindingSource and bind that to your control(s). You can then call RemoveCurrent on the BindingSource to delete the current record.

 

[elianeasmar]

It worked .

Dim cmd As New SqlCommand
If _cn.State = ConnectionState.Closed Then _cn.Open()
cmd.Connection = _cn
cmd.CommandText = "delete from tblcustomer where serial = " & Val(txtserial.Text)
cmd.ExecuteNonQuery()

 

[jmcilhinney]

It worked .

I once saw a picture of someone towing a trailer with a big thick tree branch where one of the wheels should have been. It worked. Does that mean that everyone should use tree branches instead of wheels on their trailers? What you are doing is wrong. It may get the job done but it is wrong. If you're happy to do it wrong then that's up to you but, if you want to learn, I suggest that you learn how to do it right.

 

[elianeasmar]

Ok. Can you tell me what's wrong Please?
Thank you

Private Sub cmdDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdDelete.Click
        deletecust()
        cmdRequery.PerformClick()
        cmdLast.PerformClick()
    End Sub
    Public Sub deletecust()
        Dim cmd As New SqlCommand
        If _cn.State = ConnectionState.Closed Then _cn.Open()
        cmd.Connection = _cn
        cmd.CommandText = "delete from tblcustomer where serial = " & Val(txtserial.Text)
        cmd.ExecuteNonQuery()
        txtCurrent.Text = (txtCount.Text) - 1
        RefreshData(True)
    End Sub

 

[jmcilhinney]

What's wrong is that you already have all the data in a DataSet so you should be performing any modification of that data in that DataSet and then saving those changes to the database. The DataSet changes first and then the database changes. You DO NOT make changes directly to the database and reget all the data that you already have along with the changes that you just made, which you also already have. As far as I can see, there is no good reason to do it the way you are. Do it the way it was intended to be done. It was intended to be that way for a reason.

 

[elianeasmar]

Like this?

Private Sub cmdDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdDelete.Click
        BindingContext(_DataSet.Tables(0)).RemoveAt(BindingContext(_DataSet.Tables(0)).Position)
        txtCount.Text = CStr(CInt(txtCount.Text) - 1)
        RefreshData(True)
        cmdSave.Enabled = True
    End Sub

 

[jmcilhinney]

Like this?

Private Sub cmdDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdDelete.Click
        BindingContext(_DataSet.Tables(0)).RemoveAt(BindingContext(_DataSet.Tables(0)).Position)
        txtCount.Text = CStr(CInt(txtCount.Text) - 1)
        RefreshData(True)
        cmdSave.Enabled = True
    End Sub

No, not like that. Firstly, you should be using a BindingSource, as I said in post #2. As for saving the changes, I assume that your RefreshData method is retrieving data from the database and there is no need to retrieve any data because you already have the data. You should be calling Update on your adapter, not Fill.

 

[elianeasmar]

If MsgBox("are you sure you want to delete  current record?", MsgBoxStyle.OkCancel) = MsgBoxResult.Cancel Then Exit Sub
        'delete from dataset
        _DataSet.Tables(0).Rows.Item(Me.txtCurrent.Text - 1).Delete()

End Sub

Thank you

 

[jmcilhinney]

If MsgBox("are you sure you want to delete  current record?", MsgBoxStyle.OkCancel) = MsgBoxResult.Cancel Then Exit Sub
        'delete from dataset
        _DataSet.Tables(0).Rows.Item(Me.txtCurrent.Text - 1).Delete()

End Sub

Thank you

Why thank me when you're still not doing what I said?