在插入或删除之前测试是否存在 [英] Testing if present before insert or delete

问题描述

大家好，



我对编程的网络世界相对较新，这是我在这里发表的第一篇文章。我试图打破我的工作，并使用vb.net做一个简单的Web表单。该项目是订阅和取消订阅时事通讯。我有一个文本字段用于他们的电子邮件地址和2个按钮：一个按钮是订阅，另一个是取消订阅。我已经编写了所有代码并使其具有功能性，但我最后还是坚持了......更多关于我认为的最佳实践。我怀疑你们都能欣赏到这一点！



基本上，我想做的是......我想在插入之前检查电子邮件是否在表格中对于它......告诉他们这封电子邮件已经存在于表格中。相反，如果他们点击Unsubscribe ...我想首先确保电子邮件存在。如果没有，我想给他们一个消息。



我只会向你展示我的订阅按钮代码。我理解校长将是相同的，可以复制取消订阅按钮。



我希望尽可能保持我的代码非常简短和干净。我需要尽快完成这个项目，所以我可以在面试时提供它。我有这个要弄明白，然后我必须将我的内联SQL放入存储过程...然后将其打开。



所以，这是代码：< br $> b $ b

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:

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

真的希望别人可以帮助我。我想到并玩了一个选择计数（*）并尝试捕获计数...如果计数> 0，它被发现......但是不能弄清楚。如果可以，请帮忙。



最好的问候，



Ron

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

推荐答案

您应该使用 EXISTS（Transact-SQL） ） [ ^ ]程序中的陈述。试试这个：

SQL：

You should use EXISTS (Transact-SQL)[^] statement in the procedure. Try this:
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＃

C#

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

希望它有所帮助！

- Amit

首先创建此程序

First Create this Procedure

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

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

这篇关于在插入或删除之前测试是否存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！