lundi 23 novembre 2015

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 MessageBox"record successfully deleted"! now 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

Aucun commentaire:

Enregistrer un commentaire