DELETE returns no error

Question

0.00/5 (No votes)

See more:

I am pretty new to this, so please consider that when answering.

I am running a VB.Net program that subscribes and unsubscribes to a newsletter. I have a 1 column table setup that stores an email address as input. I am using an Insert command under the subscribe button and a Delete command under the unsubscribe button. So, if they enter a valid email address and hit the subscribe button, it populates a row on the table. If that email already exists...it throws an error that get's caught in the catch in VB.Net. This makes sense on an Insert because it is trying to insert a duplicate. The problem is...if I use this same logic for the delete, it does not work.

This is effectively what I am using in a stored procedure for the delete:

SQL

DELETE FROM [dbNewsletter].[dbo].[SubscriberT] WHERE Email = 'asdf@asdf.com'

When I run it manually, it comes back with "(0 row(s) affected)" and "Query Executed Successfully". I understand why it's doing this. I am telling it to delete where it finds X. Well, it doesn't find X, so it doesn't delete it. Not an error really, just didn't find X to delete.

I have no idea how to make the stored procedure throw an error if it does NOT find X. I am wanting to throw an error message that says...cannot unsubscribe this email rather than my program saying they were removed.

This is my VB.Net code:

VB

Try
   Dim cmd As New SqlCommand("SubscriberT_Delete", con)
   cmd.Parameters.AddWithValue("@Email", EmailTextBox.Text)
   cmd.CommandType = CommandType.StoredProcedure
   cmd.ExecuteNonQuery()
   lblInfo.Text = "<font color=blue>You are now Unsubscribed! Sorry to see you go!</font>"
   lblInfo.Visible = True
Catch sqlEx As SqlException
   lblInfo.Text = "<font color=red>This email is not currently subscribed, please use a different email address</font>"
   lblInfo.Visible = True
End Try

I want the delete to trigger the Catch if it does not find X.

Please help!

Ron

Posted 9-May-13 8:41am

Ron Walker41

Updated 9-May-13 8:45am

v2

Add a Solution

Comments

Ron Beyer 9-May-13 14:46pm

First, select from your database with something like SELECT COUNT(*) AS Num FROM MyTable WHERE Email = @email then if the Num == 0, tell them they can't unsubscribe because they aren't subscribed.

Ron Walker41 9-May-13 14:49pm

I have looked at doing something like that, but don't know how to code it on the VB side. can you give me an example? Again, am new...

Ron Beyer 9-May-13 14:51pm

cmd.ExecuteNonQuery() returns the number of rows affected, if it is zero, then display your message to the user.

Ron Walker41 9-May-13 14:55pm

Ok, how does it return that number of rows affected? Is there some variable name I need to check? Please demonstrate what the VB code would look like.

Is it:

If cmd.ExecuteNonQuery() = 0
nothing was done...???

Ron Beyer 9-May-13 15:09pm

Yes, I'm not a VB programmer, but its something like:

If cmd.ExecuteNonQuery() = 0 then
'give the user that they were not unsubscribed
Else
'give the user the message that they were unsubscribed
End If

Ron Walker41 9-May-13 15:19pm

Ok, this looks like something I can try easily...will let you know how it goes.

Ron Walker41 9-May-13 15:27pm

I coded it like this and it did not work. It deletes the record, but gives the 'not subscribed' message:

Dim cmd As New SqlCommand("SubscriberT_Delete", con)
cmd.Parameters.AddWithValue("@Email", EmailTextBox.Text)
cmd.CommandType = CommandType.StoredProcedure
cmd.ExecuteNonQuery()
If cmd.ExecuteNonQuery() = 0 Then
lblInfo.Text = "This email is not currently subscribed, please use a different email address"
lblInfo.Visible = True
Else
lblInfo.Text = "You are now Unsubscribed! Sorry to see you go!"
lblInfo.Visible = True
End If

[no name] 9-May-13 14:48pm

Normally you would not do this at all. Why? Because it is not an exception. You should either check the number of affected rows or return some value from your SP that you check in your code.

3 solutions

Solution 1

In your stored procedure, check if the row was deleted, and use RAISEERROR[^] with a Severity >= 11 to cause SQL to throw an SQLException back to your code.

You can check the number of deleted rows by examining the @@ROWCOUNT[^] variable in your SP.

"The stored procedure would not execute when I tried to save it to my database. I assume I had a syntax problem, which is why I asked for an example of it."

You haven't quite got the idea of stored procedures, or SQL syntax really, have you? :laugh:

SQL

CREATE PROC [dbo].Sample
@Name varchar(100) OUTPUT
AS
BEGIN
... (body of SP)
END

Is a skeleton SP. Note the BEGIN and END, one of which is missing from yours!

Then there is the IF:

SQL

IF condition
BEGIN
   ...
END
ELSE
   ...

Which again, if a bit different from yours! The BEGIN after the IF is to denote the code that is executed if the condition is true, not the whole of the IF statement, hence the END before the ELSE.

Posted 9-May-13 8:54am

OriginalGriff

Updated 9-May-13 9:25am

v3

Comments

Ron Walker41 9-May-13 14:58pm

Looked at that RAISEERROR option too but couldn't get it to work. Can you show me an example?

OriginalGriff 9-May-13 15:03pm

They give examples in the link, and it isn't complicated.
What exactly did you try, and what happened that it didn't work?

Ron Walker41 9-May-13 15:11pm

I tried something like this:

CREATE PROC [SubscriberT_Insert]

@email AS VARCHAR(MAX)
AS

DECLARE @isPresent INT

SET @isPresent = (
SELECT COUNT(*)
FROM SubscriberT
WHERE email = @email
)

IF (@isPresent <= 0 )
BEGIN
INSERT INTO SubscriberT
(
email
)
VALUES
(
@email
)
ELSE
RAISEERROR('ERROR MSG',11,1)
END

The stored procedure would not execute when I tried to save it to my database. I assume I had a syntax problem, which is why I asked for an example of it.

OriginalGriff 9-May-13 15:22pm

Answer updated

Solution 3

This is what I ended up with after I made a new Stored Procedure:

VB

Public Function EmailExists(ByVal Email As String) As Boolean
        Dim connectionString As String = WebConfigurationManager.ConnectionStrings("dbNewsletter").ConnectionString
        Dim con As New SqlConnection(connectionString)
        Dim rowCount As Integer

        con.Open()

        Using comm As New SqlCommand("SubscriberT_SelectCount", con)
            comm.Parameters.AddWithValue("@Email", EmailTextBox.Text)
            comm.CommandType = CommandType.StoredProcedure

            rowCount = Convert.ToInt32(comm.ExecuteScalar())
        End Using

        Return (rowCount > 0)

        con.Close()

    End Function

VB

If EmailExists(EmailTextBox.Text) Then
    Try
        Dim cmd As New SqlCommand("SubscriberT_Delete", con)
        cmd.Parameters.AddWithValue("@Email", EmailTextBox.Text)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.ExecuteNonQuery()
        lblInfo.Text = "<b><font color=blue>You are now Unsubscribed! Sorry to see you go!</font></b>"
        lblInfo.Visible = True
    Catch ex As Exception
        lblInfo.Text = "<b><font color=red>" + ex.Message + "</font></b>"
        lblInfo.Visible = True
    End Try
Else
    lblInfo.Text = "<b><font color=red>This email is not currently subscribed, please use a different email address</font></b>"
    lblInfo.Visible = True
End If

Works great!!

Posted 9-May-13 12:26pm

Ron Walker41

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

pdoxtader · Accepted Answer · 2013-05-09T08:57:00

Solution 2

Maybe something like this?

VB

Public Function EmailExists(ByVal emailAddy As String) As Boolean
     Dim query As String = "SELECT Count(*) FROM [dbNewsletter].[dbo].[SubscriberT] WHERE Email=@emailAddy"
     Dim rowCount As Integer

     Using conn As New SqlConnection("your sql connection string")
         conn.Open()

         Using comm As New SqlCommand(query, conn)
             comm.Parameters.AddWithValue("@Email", emailAddy )

             rowCount = Convert.ToInt32(comm.ExecuteScalar())
         End Using
     End Using

     Return (rowCount > 0)
 End Function

If EmailExists("theEmail@address.com") Then
   Dim cmd As New SqlCommand("SubscriberT_Delete", con)
   cmd.Parameters.AddWithValue("@Email", EmailTextBox.Text)
   cmd.CommandType = CommandType.StoredProcedure
   cmd.ExecuteNonQuery()
   lblInfo.Text = "<font color="blue">You are now Unsubscribed! Sorry to see you go!</font>"
   lblInfo.Visible = True
else
   lblInfo.Text = "<font color="red">This email is not currently subscribed, please use a different email address</font>"
   lblInfo.Visible = True
End If

Posted 9-May-13 8:57am

pdoxtader

Updated 9-May-13 9:26am

v3

Comments

Ron Walker41 9-May-13 15:15pm

Ok, I like this idea! I get to learn how to do a function now too!

Please don't give me hard time. I am working my butt off trying to get up to speed. Where do I put the function inside of VB? Do I put it inside the click button or outside that in the class? Or am I completely off with where a function goes in the code?

Thanks in advance,

Ron

Richard C Bishop 9-May-13 15:22pm

It depends. Do you want that code to run when the button is clicked?

pdoxtader 9-May-13 15:24pm

Maybe I misunderstood him, but I think he's asking if the function I gave him should be in the click event... and the answer to that is of course no. He should CALL it from the click event, if that's what he wants though...

Richard C Bishop 9-May-13 15:25pm

Nope, you understood correctly. I was just asking the OP what they were trying to do.

pdoxtader 9-May-13 15:22pm

Outside, in the class.

Ron Walker41 9-May-13 15:33pm

Thanks everyone...I got it. Outside the click event...inside the class. Call the function from within the click event. Getting there...I hope.

Am gonna use this, but try to get it working from the Stored Procedure first.

This stuff is so challenging, but fun at the same time.

Thanks much!

Ron

Ron Walker41 9-May-13 15:36pm

Actually...I think this function idea is the way to go. I don't want to start bad habits. Would you all say it's a best practice in situations like this to keep the SQL SP's simple and handle exceptions on the VB side?

Ron Walker41 9-May-13 16:05pm

Ok, I think I have it working now. Am gonna apply this function for both the insert and delete...

Thanks a TON!!!

pdoxtader 10-May-13 8:46am

Glad it worked out for you.