CODEWITHSUNDEEP
sqlvb.netvisual-studioms-access
Ahmed
Ahmed

Reputation: 41

Deleting a record that doesn't exist from MS Access Database using .net

the below function deletes the row from my MS Access database. Everything is working fine except that if I enter a 'txtRiskid.Text' which doesn't exist it runs the else statement. How can I catch this in this this if statement, so if I were to enter a 'txtRiskid.Text' that doesn't exist it would tell me it doesn't exist via a message, similar to what I wrote if it is left empty.

Delete button coding:

Private Sub Deletebtn_Click_1(sender As Object, e As EventArgs) Handles Deletebtn.Click
    If txtRiskid.Text = "" Then
        MsgBox("Please enter a existing 'Risk ID' to delete the specific record from the database", 0, "Warning")
    Else
        Dim Deletequery As String = "delete from Risk_Register where ID=@ID"
        Runquery(Deletequery)
        MsgBox("The record has been Deleted successfully from the database.", 0, "Information")
    End If
End Sub

Runquery coding:

Public Sub Runquery(ByVal query As String)

    con = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\ahmed\OneDrive\Desktop\ProjectDatabase2003.mdb")
    Dim cmd As New OleDbCommand(query, con)

    cmd.Parameters.AddWithValue("@ID", txtRiskid.Text)
    cmd.Parameters.AddWithValue("@Risk_Name", txtRiskname.Text)
    cmd.Parameters.AddWithValue("@Risk_Description", txtRiskdescription.Text)
    cmd.Parameters.AddWithValue("@Owner", txtOwner.Text)
    cmd.Parameters.AddWithValue("@Control", txtControl.Text)
    cmd.Parameters.AddWithValue("@Probability", txtProbability.Text)
    cmd.Parameters.AddWithValue("@Impact", txtImpact.Text)
    cmd.Parameters.AddWithValue("@Risk_Level", txtRisklevel.Text)

    con.Open()
    cmd.ExecuteNonQuery()
    con.Close()

End Sub

Upvotes: 0

Views: 118

Answers (1)

Serg
Serg

Reputation: 22811

ExecuteNonQuery() returns the number of rows affected see https://learn.microsoft.com/en-us/dotnet/api/system.data.oledb.oledbcommand.executenonquery Return it from your Runquery sub and check in the Deletebtn_Click_1 handler.

Kind of

Private Sub Deletebtn_Click_1(sender As Object, e As EventArgs) Handles Deletebtn.Click
    If txtRiskid.Text = "" Then
        MsgBox("Please enter a existing 'Risk ID' to delete the specific record from the database", 0, "Warning")
    Else
        Dim Deletequery As String = "delete from Risk_Register where ID=@ID"
        If Runquery(Deletequery) > 0 Then
            MsgBox("The record has been Deleted successfully from the database.", 0, "Information")
        Else
            MsgBox("The record doesn't exist in the database.", 0, "Information")
        End If
    End If
End Sub

and

Public Function Runquery(ByVal query As String) As Integer

    con = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\ahmed\OneDrive\Desktop\ProjectDatabase2003.mdb")
    Dim cmd As New OleDbCommand(query, con)
    Dim count As Integer
    cmd.Parameters.AddWithValue("@ID", txtRiskid.Text)
    cmd.Parameters.AddWithValue("@Risk_Name", txtRiskname.Text)
    cmd.Parameters.AddWithValue("@Risk_Description", txtRiskdescription.Text)
    cmd.Parameters.AddWithValue("@Owner", txtOwner.Text)
    cmd.Parameters.AddWithValue("@Control", txtControl.Text)
    cmd.Parameters.AddWithValue("@Probability", txtProbability.Text)
    cmd.Parameters.AddWithValue("@Impact", txtImpact.Text)
    cmd.Parameters.AddWithValue("@Risk_Level", txtRisklevel.Text)

    con.Open()
    count = cmd.ExecuteNonQuery()
    con.Close()
    Return count
End Function

Upvotes: 1

Related Questions