CODEWITHSUNDEEP
sql-servervb.netif-statement
Hashmatullah Noorzai
Hashmatullah Noorzai

Reputation: 790

If-else condition in visual basic to delete a record from SQL Server if record exist

I have written code in VB to delete a table record from database using inputbox (through user choice), but I have one problem when user inserts wrong value it still shows the "Record Successfully Deleted" MessageBox!

How to set the "if condition" to display record does not exist instead of showing successful deletion message?

Sorry this is my 1st post here that's why it is long! :p

Here is the code:

Private Sub btndelete_Click(sender As Object, e As EventArgs) Handles btndelete.Click
        Try
            Dim isbn As Long = InputBox("Enter Book ISBN", "Delete")
            'First will delete the dependant record from published_by, book_return, memberbook_issue because
            'it can not be deleted by applying on cascade delete or update cause it has composite primary key.

            cmd = New SqlCommand("delete from published_by where isbn =" & isbn, cn)
            If cn.State = ConnectionState.Closed Then
                cn.Open()
            End If
            cmd.ExecuteNonQuery()

            cmd = New SqlCommand("delete from book_return where isbn =" & isbn, cn)
            If cn.State = ConnectionState.Closed Then
                cn.Open()
            End If
            cmd.ExecuteNonQuery()

            cmd = New SqlCommand("delete from memberbook_issue where isbn =" & isbn, cn)
            If cn.State = ConnectionState.Closed Then
                cn.Open()
            End If
            cmd.ExecuteNonQuery()


            cmd = New SqlCommand("delete from book where isbn = " & isbn, cn)
            If cn.State = ConnectionState.Closed Then
                cn.Open()
            End If
            cmd.ExecuteNonQuery()
            da = New SqlDataAdapter("select b.staff_id, b.pub_id, b.sub_code, b.isbn, b.book_name, b.author, b.price, b.rack_no, b.no_of_books, pby.vol_no, pby.pub_date  from book b join published_by pby on b.isbn = pby.isbn", cn)
            dt = New DataTable
            da.Fill(dt)
            dgvbook.DataSource = dt
            MessageBox.Show("Record Successfully Deleted from current table & dependant table(s)", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Catch ex As Exception
            MessageBox.Show("Not Completed Because OF The Following Error " & "%" & ex.Message & "%", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

Upvotes: 1

Views: 1295

Answers (1)

InbetweenWeekends
InbetweenWeekends

Reputation: 1414

As others have already mentioned, you really need to look into parameterizing your query. You should also look to encapsulate this into a stored procedure for easier reading and maintainablity. Those things aside, you can accomplish all of this in one database call.

This snippet should produce the expected results. Please double check the data type on the SqlParameter -- I guessed what it could be.

cmd = New SqlCommand("IF EXISTS(SELECT * FROM book WHERE isbn = @isbn) " _
    & " BEGIN " _
    & " delete from published_by where isbn = @isbn; " _
    & " delete from book_return where isbn = @isbn; " _
    & " delete from memberbook_issue where isbn = @isbn; " _
    & " delete from book where isbn = @isbn;" _
    & " SELECT 1; " _
    & " END " _
    & " ELSE SELECT 0", cn)

cmd.Parameters.Add(New SqlParameter("@isbn", SqlDBType.VarChar, 20) With {.Value = isbn})

cn.Open()
Dim returnValue as Integer = CInt(cmd.ExecuteScalar())
If returnValue = 1 Then
    MessageBox.Show("Record Successfully Deleted from current table & dependant table(s)", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information)
End

Upvotes: 2

Related Questions