Checking the database before submitting a form 1

[orlandoj]

Hi all,

I have a form where the user can submit their e-mail address. Before the form submits, I'd like to check if the e-mail address already exists in my database, and if it does, open a javascript alert window which says "e-mail already exists". I've been having alot of problems finding a good, clean way to do this.

Any help would be most appreciated..
Thanks,
-Jamie

 

[mwolf00]

You have two options.

Client-side:
send a list of all the email addressed in the DB to the form page and store them in an array so that you can check them onSubmit. This allows every user to see all of the email addresses in your DB by viewing the source code and it can be cumbersome. Not Recommended.

Server-Side:
allow the user to submit the form and query the db to see if the email exists and if it does, repopulate the form and pop a javascript error message. Not as elegant, but better.

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rich Cook

 

[TonyU]

Here's a quick overview built with dreamweaver how to check if an email or whatever field you'd like to check against the DB already exists of not.

Strip the code and the logic is pretty simple...




<%
' *** Validate request to log in to this site.
MM_LoginAction = Request.ServerVariables(&quot;URL&quot
If Request.QueryString<>&quot;&quot; Then MM_LoginAction = MM_LoginAction + &quot;?&quot; + Request.QueryString
MM_valUsername=CStr(Request.Form(&quot;email&quot)
If MM_valUsername <> &quot;&quot; Then
MM_fldUserAuthorization=&quot;&quot;
MM_redirectLoginSuccess=&quot;

http://www.tek-tips.com&quot;

MM_redirectLoginFailed=&quot;

http://your_email_already_exists.asp&quot;

MM_flag=&quot;ADODB.Recordset&quot;
set MM_rsUser = Server.CreateObject(MM_flag)
MM_rsUser.ActiveConnection = MM_MYDSN_STRING
MM_rsUser.Source = &quot;SELECT email, password&quot;
If MM_fldUserAuthorization <> &quot;&quot; Then MM_rsUser.Source = MM_rsUser.Source & &quot;,&quot; & MM_fldUserAuthorization
MM_rsUser.Source = MM_rsUser.Source & &quot; FROM logins_TBL WHERE email='&quot; & MM_valUsername &&quot;' AND password='&quot; & CStr(Request.Form(&quot;email&quot) & &quot;'&quot;
MM_rsUser.CursorType = 0
MM_rsUser.CursorLocation = 2
MM_rsUser.LockType = 3
MM_rsUser.Open
If Not MM_rsUser.EOF Or Not MM_rsUser.BOF Then
' username and password match - this is a valid user
Session(&quot;MM_Username&quot = MM_valUsername
If (MM_fldUserAuthorization <> &quot;&quot Then
Session(&quot;MM_UserAuthorization&quot = CStr(MM_rsUser.Fields.Item(MM_fldUserAuthorization).Value)
Else
Session(&quot;MM_UserAuthorization&quot = &quot;&quot;
End If
if CStr(Request.QueryString(&quot;accessdenied&quot) <> &quot;&quot; And false Then
MM_redirectLoginSuccess = Request.QueryString(&quot;accessdenied&quot
End If
MM_rsUser.Close
Response.Redirect(MM_redirectLoginSuccess)
End If
MM_rsUser.Close
Response.Redirect(MM_redirectLoginFailed)
End If
%>

[sup]

Delete * from brain Where MaxLevel = &quot;Full&quot; and reaction = &quot;Slow&quot; order by StartOver
​

[/sup]

 

[orlandoj]

Thanks,

I had already considered both of those methods before posting. I was just looking for something more simple. (I realize this may not be possible.) Is there some way in my javascript function, that I can call an ASP scriptlet to check if the e-mail address is in the database? This is sorta what I had in mind:

=================
<script type=&quot;text/javascript&quot;>
function verify() {
if(/*function which determines if the form e-mail exists in database*/) {
alert(&quot;oops!&quot;
return false;
}
else {
return true;
}
}
</script>

<form name=&quot;mailing&quot; method=&quot;post&quot; action=&quot;mailingupdated.asp&quot; onsubmit=&quot;return verify()&quot;>
<input name=&quot;email&quot; type=&quot;text&quot;>
</form>
=================

 

[Genimuse]

A tidy way to do it is to have the form submit to itself (the same page). Here's the basic logic in pseudocode (sorry if this comes off as basic -- from your question it sounds like some basic advice would help):

If the email field is blank then
    Set a variable (we'll call it strPopup)
Else
    Check the database to see if the email already exists
    If the email already exists Then
        Set strPopup to the Javascript to pop-up window with a notice
    Else
        Store the email address
        Redirect the user to the next page ("Thank You")
    End If
End If
Put the normal HTML page here, and put the strPopup variable in your <body> tag

Near the top of your page (before all of the HTML including the

<html>

tag) put a bit of ASP code that checks to see if

Trim(Request.Form("MyEmailFieldName")) = ""

.

If it is

= ""

then this is the first time the user has visited the page (or the user just entered a couple of spaces for the email address), so nothing special should happen -- just set your strPopup variable to

""

. When the page is written out (after the final

End If

), the body tag will include the strPopup variable, which will be nothing (so nothing special will happen).

If it's not

= ""

then you'll want to open a database connection and open a recordset with a query along the lines of:

"SELECT * FROM MyEmailTable WHERE MyEmailField = '" & Trim(Request.Form("MyEmailFieldName") & "'"

Then you can simply check to see if

rs.EOF

is true -- if it is then the recordset is empty, which means there is no match. If it's false then that email address is already in the table.

If the email address already exists then you set your strPopup variable to the Javascript code that will pop-up a message saying that the email address is already in use. When the page is written out (after the final

End If

), the body tag will include the strPopup variable, which will be the Javascript to pop up the message (so the message will be displayed).

If the email address doesn't exist then you

INSERT

the trimmed email address into your database, and then use

Response.Redirect

to send your user on to the next page (a &quot;Thank you&quot; page or whatever).

After the final

End If

you write your normal HTML page, adding

<%=strPopup%>

to the

<body>

tag (be certain to put a space character before the

<%=

so the Javascript code won't run into the other

<body>

elements).

Note that to make this a little nicer you can add some error checking to verify that it's a valid email address format and write out a Javascript &quot;Invalid email address&quot; popup if it's bad.

I use a little more complicated version of this technique for all kinds of things, but this should be complex enough to get you going.

 

[Genimuse]

Sorry, my post took so long to write you'd already responded a couple of times!

 

[orlandoj]

Thanks! This should be sufficient for me to do what I needed to do.

-Jamie

 

[theocraticmind]

For a middle road (a little more complicated the the above) you could run your script in a pop-up

onsubmit:
function runscript(){
var scriptWin = window.open('script.asp?addy='+document.frmName.field.value,'','scrollbars=0');

do{
if(!varName){return false;}else{return true;}
}while(!done)
}

script.asp:

<html>
<head>
<%
' check for email in db
if it's there then
response.write &quot;<script>window.opener.good = true;window.close();</script>&quot;
else
response.write &quot;<script>window.opener.good = false;window.opener.done = true;window.close();</script>&quot;
end if
%>
</head>
</html>

 

[theocraticmind]

I forgot:

<html>
<head>
<%
' check for email in db
if it's there then
response.write &quot;<script>window.opener.good = true;window.opener.done = true;window.close();</script>&quot;
else
response.write &quot;<script>window.opener.good = false;window.opener.done = true;window.close();</script>&quot;
end if
%>
</head>
</html>