CODEWITHSUNDEEP
sql-servervb.netstored-procedures
Hashmatullah Noorzai
Hashmatullah Noorzai

Reputation: 790

Deleting record using SQL stored procedure from VB.net form

I have written below code to delete record from multiple database tables but some how it is not deleting any record and doesn't display any error as well! can anyone help please?

    Private Sub btndelete_Click(sender As Object, e As EventArgs) Handles btndelete.Click
        Try
       cmd = New SqlCommand("IF EXISTS(SELECT * FROM book WHERE isbn = @isbn) " _
    & " BEGIN " _
    & " delete from published_by where isbn = @isbn; " _
    & " delete from return where isbn = @isbn; " _
    & " delete from 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, 13) _ 
 With {.Value = isbn})
            If cn.State = ConnectionState.Closed Then
                cn.Open()
            End If
            Dim returnValue As Integer = CInt(cmd.ExecuteScalar())
            If returnValue = 1 Then
                lblAlert.Text = ""
                lblInfo.Text = "Deleted"
            Else
                lblAlert.Text = "No Record Available"
                lblInfo.Text = ""
            End If
        Catch sqlEx As SqlException
            Select sqlEx.Number
                Case -1, 2, 53
                    MessageBox.Show("Connection Failed to Database")
            End Select
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End If
End Sub

Upvotes: 0

Views: 760

Answers (1)

Yacoub Massad
Yacoub Massad

Reputation: 27861

"return" is a reserved word (see this reference). To fix the issue, put it between square brackets like this:

delete from [return] where isbn ...

Another note about your code is that you are swallowing the SqlException in case the error number was not -1, 2, or 53. To fix this, handle the case where it is not one of these numbers like this:

Select Case sqlEx.Number
    Case -1, 2, 53
        Console.WriteLine("Connection Failed to Database")
    Case Else
        Console.WriteLine(....) 'write something here, you can use sqlEx.Message
End Select

This is the reason why you didn't get an error message in the first place.

Upvotes: 2

Related Questions