Testing if present before insert or delete

Question

0.00/5 (No votes)

See more:

Hello everyone,

I am relatively new to the web world of programming and this is my first post here. I am trying to break my way into a job and have to do a simple web form using vb.net. This project is to subscribe and unsubscribe to a newsletter. I have 1 text field for their email address and 2 buttons: one button is to subscribe, the other is to unsubscribe. I have all the code written and have it functional, but I am stuck on one last thing...more about best practices I think. I suspect you all can appreciate that!

Basically, what I want to do is...I want to check whether the email is in the table before doing the insert for it...telling them that this email already exists in the table. Conversely, if they hit Unsubscribe...I want to first make sure that email exists. If it does not, I want to give them a message to that effect.

I am only going to show you my code for the Subscribe button. I understand the principal will be the same and can be duplicated for the Unsubscribe button.

I prefer to keep my code very short and clean as possible. I need to finish this project asap so I can deliver it as part of my job interview. I have this to figure out, then I have to put my inline SQL into stored procedures...then turn it in.

So, here is the code:

VB

Try
    Dim sql As String = "INSERT INTO SubscriberT VALUES(@Email)"
    Dim cmd As New SqlCommand(sql, con)
    cmd.Parameters.AddWithValue("@Email", TextBox1.Text)
    cmd.ExecuteNonQuery()
    lblInfo.Text = "<font color=green>You are now Subscribed! Thank you!</font>"
    lblInfo.Visible = True
Catch ex As Exception
    lblInfo.Text = "<font color=red>This email is already subscribed, please try again</font>"
    lblInfo.Visible = True
End Try

Really hoping someone can help me. I thought of and have played with doing a select count(*) and trying to capture the count...if count > 0, it's found...but can't get that figured out. Please help if you can.

Best Regards,

Ron

Posted 8-May-13 17:48pm

Ron Walker41

Add a Solution

2 solutions

Solution 2

You should use EXISTS (Transact-SQL)[^] statement in the procedure. Try this:
SQL:

SQL

CREATE PROCEDURE spSubNewsletter
    @Email VARCHAR(50)
AS
BEGIN
    IF NOT EXISTS(SELECT * FROM sdfClient WHERE EmailID=@Email)
    BEGIN
        INSERT INTO sdfClient(EmailID) VALUES (@Email)
        SELECT 'You are now Subscribed! Thank you!'
    END
    ELSE
        SELECT 'This email is already subscribed, please try enter a valid Email ID!'
END

C#

VB

Dim cmd As New SqlCommand("spSubNewsletter", con)
cmd.Parameters.AddWithValue("@Email", TextBox1.Text)
cmd.CommandType = CommandType.StoredProcedure
Using con As New SqlConnection(cmd.Connection)
con.Open()
Dim res As String = cmd.ExecuteScalar()
End Using
lblInfo.Text = "<font color=red>"+ res +"</font>"
lblInfo.Visible = True

Hope it helps!

--Amit

Posted 8-May-13 19:36pm

_Amy

Updated 8-May-13 19:37pm

v2

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)

abdussalam143 · Accepted Answer · 2013-05-08T18:52:00

Solution 1

First Create this Procedure

SQL

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
      )
   
END

Then Call it from Your Code and here is the Code

VB

Try
    Dim cmd As New SqlCommand("SubscriberT_Insert", con)
    cmd.Parameters.AddWithValue("@email", TextBox1.Text)
    cmd.CommandType = CommandType.StoredProcedure
    Using con As New SqlConnection(cmd.Connection)
    con.Open()
    cmd.ExecuteNonQuery()
    End Using
Catch ex As Exception
    lblInfo.Text = "<font color="red">"+ ex.Message +"</font>"
    lblInfo.Visible = True
End Try

Posted 8-May-13 18:52pm

abdussalam143

Comments

Ron Walker41 9-May-13 11:25am

I am trying to implement Solution 1. I like it because it allows me to use a stored procedure as well as do the check inside the stored procedure before doing the insert. Really love this idea!

However, I have it all coded and it is not quite working as expected. I am able to Insert the row, but after the email address has been added, it is not sending the message that it already exists. I believe I know what the problem is, but don't know how to fix it.

This is what my stored procedure looks like:

CREATE PROCEDURE [SubscriberT_Insert]

@Email AS NCHAR(50)
AS

DECLARE @isPresent INT

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

IF (@isPresent <= 0 )
BEGIN
INSERT INTO SubscriberT (Email) VALUES (@Email)
END
GO

I think what's happening here is that it never gets to the BEGIN statement if it is present, so it does not throw an error back to be caught in my VB code:

Try

Dim cmd As New SqlCommand("SubscriberT_Insert", con)

cmd.Parameters.AddWithValue("@Email", TextBox1.Text)

cmd.CommandType = CommandType.StoredProcedure

cmd.ExecuteNonQuery()

lblInfo.Text = "You are now Subscribed! Thank you!"

lblInfo.Visible = True

Catch ex As Exception

lblInfo.Text = "This email is already subscribed, please use a different email address"

lblInfo.Visible = True

End Try

There is no 'ex' exception to be 'caught'. I think the solution needs to be adding an ELSE to the 'IF (@isPresent <= 0 )' that will force out some kind of 'ex' exception. Just have no idea how to do that.

What do you think?

Ron

abdussalam143 10-May-13 8:09am

First Create this Procedure

CREATE PROC [SubscriberT_Insert]
@msgCode AS INT OUTPUT,
@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
)
SET @msgCode = 1
END
ELSE
BEGIN
SET @msgCode = -1
END

Then Call it from Your Code and here is the Code

Private Function AddData(email As String) As Integer
Dim msgCode As Integer = 0
Try
Dim cmd As New SqlCommand("SubscriberT_Insert")
cmd.Parameters.AddWithValue("@msgCode", msgCode).Direction = ParameterDirection.Output
cmd.Parameters.AddWithValue("@email", email)
cmd.CommandType = CommandType.StoredProcedure
Using con As New SqlConnection("Connection Will go Here")
con.Open()
cmd.ExecuteScalar()
msgCode = Convert.ToInt32(cmd.Parameters[0].Value.ToString());
End Using
Catch ex As Exception
msgCode = -99
End Try

Return msgCode

End Function

this Function Will Return Inger Number

"1 = Record Added Successfully"
"-1 = Record Already Exist"
"-99 = Some Error Occurred"