delete cmd

[rockyy]

can anyone tell me the right way

its showing the msg even when the author id doesn't exist

i want it sshould delete only those id's which are present in the database
else it shld give msg no recorsd found

my code please correct me: )

If TextBox2.Text <> "" Then

            connectionString = "Data Source=hello\SQLEXPRESS;Initial Catalog=mytanveer;Integrated Security=True"

            SQLStr = "DELETE FROM author WHERE author='" & TextBox1.Text & "'"

            'Write to SQL

            SQLConn.ConnectionString = connectionString             SQLConn.Open() 

            SQLCmd.Connection = SQLConn 
            SQLCmd.CommandText = SQLStr 
            SQLCmd.ExecuteNonQuery() 

            SQLConn.Close()   
            MsgBox("author data deleted", MsgBoxStyle.OkOnly)
        Else

            MsgBox("Enter the required values:" & vbNewLine & "1. Au_ID")

        End If


    End Sub

 

[Hack]

You can check the rows affected by your DELETE query. If they are greater than 0 then you know your DELETE found records. If the rows affected are 0 then there was no record that matched, and nothing was deleted. Try this

Dim RowsAffected As Integer = 0
'your code down to where you execute the query
RowsAffected = SQLCmd.ExecuteNonQuery() 
If RowsAffected > 0 Then
   'messagebox for when delete was successful
Else
   'messagebox for when delete didn't find anything
End If

 

[rockyy]

please check my code now everytime i click on delete its showing msgbox(" no record found")

Dim RowsAffected As Integer = 0
        connectionString = "Data Source=TANVEER-PC\SQLEXPRESS;Initial Catalog=mytanveer;Integrated Security=True"
        SQLStr = "DELETE FROM author WHERE au_id='" & TextBox2.Text & "'"
        
        SQLConn.ConnectionString = connectionString
        SQLConn.Open()
        SQLCmd.Connection = SQLConn
        SQLCmd.CommandText = SQLStr
        SQLCmd.ExecuteNonQuery()

        RowsAffected = SQLCmd.ExecuteNonQuery()
        
        If RowsAffected > 0 Then

            MsgBox("Convict data deleted", MsgBoxStyle.OkOnly)

        Else
            MsgBox("No record found please provide correct author id:" & vbNewLine & "1. Au_ID")

        End If


        SQLConn.Close() 'Close the connection  

        
        
    End Sub

 

[Hack]

And if you did

SQLStr = "SELECT * FROM author WHERE au_id='" & TextBox2.Text & "'"

How many records would you get back?

 

[rockyy]

its showing the all records which are present in database even if i put the corrent id
see

even if i m putting id as 101

 

[Hack]

Ok, that tells me that your WHERE clause is not working.

What kind of field is au_id?

 

[rockyy]

nothing to do with the field bro
it was working earlier rather but the problem was it was deleting the records even which are not present in the databse as u can see in above pic in my first post

even if u want to check see

 

[rockyy]

anyone help please hack if u are online please reply

 

[Hack]

The reason I didn't come back is because I can't replicate your issue.

I whipped up a test project with a quickie Access DB as a backend, and using the exact same code you are using (SQLStr = "DELETE FROM author WHERE au_id='" & TextBox2.Text & "'") I am deleting records just fine. So, without being able to physically look at your project, I don't know what else to tell you.

 

[rockyy]

hmmmn no issue bro, i thinking of calling function and check let me see wht will be result
thanx