Previous Article
ASP Classic Looping Select Statement
ASP Classic and SQL Server - Check if Email exist if not insert else show message
Go Back To List
SQL (Structured Query Language)
Coding Article #: 84 - Published On: May 17, 2018 @ 04:10:06 AM - Last Updated on: January 01, 1900
This article has been Favorited 0 times
Join today, and add this to your favorites.
Coding Source - Share on MySpace Coding Source - Share With Facebook Coding Source - Share on Twitter Coding Source - Share on Reddit Coding Source - Share on Digg It Coding Source - Share on Stumble Upon It Coding Source - Share on Delicious
Share With Friends (Updated 6-8-2010)

Supported Files
No Files for this Article.
No Screenshot Available
This was a question asked on our YouTube.com/DarkEffectsStudio channel by
Real Life


how to prevent inserting duplicate email id in sql database by using asp classic and give alert.. email is already exists.


View video for this article here https://youtu.be/WVC6uo5tdW4

First, we need our form.
This is a simple form, with just one input and one submit button, and a DIV that will be used to submit too.

Email.asp

<form method="post" id="form" name="form" action="process.asp">
<input type="text" name="email" id="email" />
<input type="submit" name="Submit" id="Submit" value="Check email" />
</form>
<div id="result"></div>


Next, is the AJAX/JQuery that is required so we can submit our page without having to refresh it.
This code will need to go below your FORM, which is why it is listed 2nd in line, in this article.
If you chose to place the code above your form, it will not function as expected.

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
<script type="text/javascript">
$("#Submit").click(function()
{
	$("#form").submit(function(e)
	{
		$("#result").html("<img src='graph/Loading_icon.gif'/>");
		var postData = $(this).serializeArray();
		var formURL = $(this).attr("action");
		$.ajax(
		{
			url : formURL,
			type: "POST",
			data : postData,
			success:function(data, textStatus, jqXHR) 
			{
				$("#result").html('<pre><code class="prettyprint">'+data+'</code></pre>');
			},
			error: function(jqXHR, textStatus, errorThrown) 
			{
				$("#result").html('<pre><code class="prettyprint">AJAX Request Failed<br/> textStatus='+textStatus+', errorThrown='+errorThrown+'</code></pre>');
			}
		});
	    e.preventDefault();	//STOP default action
	    e.unbind();
	});
});
</script>


The next part is our database connection string.
We are using variables to store our important information for our DB String.
This will allow for us to easily change from one DB to another.

ACN.asp

<%
' This is our variable to be used for our connection to our database.
username = "testuser"
password = "testpassword"
getServer =  "carrz-crysis"
getInstance = "carrz2005"
DBName = "testdb"
' This is our connection to our database.
Set Conn = CreateObject("ADODB.Connection")
Conn.Open("Provider=SQLOLEDB; Data Source="&getServer&"\"&getInstance&"; Initial Catalog="&DBName&";User ID="&username&";Password="&Password&";")
%>


This is the script to create the Emails table in your database.

CREATE TABLE Emails(
	EId INT IDENTITY PRIMARY KEY,
	Email varchar(50) NOT NULL);


Our final code is the processing page. This is where we check if the email address exists, if it does, show a message, if it does not, then insert into the table.

process.asp

<!--#include file="ACN.asp"-->
<%
chEmail = request.form("email")
'response.Write chEmail

Set sqlEmail = Server.CreateObject("ADODB.Command")
sqlEmail.ActiveConnection=Conn
sqlEmail.Prepared = true
sqlEmail.commandtext="select email from emails where email = ?"
sqlEmail.Parameters.Append sqlEmail.CreateParameter("@email", 200, 1, 50, chEmail)
set rsEmail = sqlEmail.execute
if rsEmail.eof then
Set sqlInsEmail = Server.CreateObject("ADODB.Command")
sqlInsEmail.ActiveConnection=Conn
sqlInsEmail.commandtext="insert into Emails (email) values (?)"
sqlInsEmail.Parameters.Append sqlInsEmail.CreateParameter("@email", 200, 1, 50, chEmail)
sqlInsEmail.execute
response.Write "Email address <strong>"&chEmail&"</strong> Added successfully!"
else
Response.Write "The Email address you provided already exist in our database.<br />Please Login to your account.<br />If you cannot remember your password, please use the Password reset form."
end if
%>


Using all the code above, you should be able to run this script and insert a record into your emails table.

Post to Facebook about: ASP Classic and SQL Server - Check if Email exist if not insert else show message