...

View Full Version : INSERT & UPDATE tables



schulza
09-09-2005, 10:31 PM
I have been working on a web app that will allow a user to enter thier information (first name, last name, email ...) and then they will be assigned a number that will be emailed to to them.

I am using two tables:
Table 1: users - contains first name, last name, email, user code
Table 2: codes - contains a list of all available codes

The issue that i am facing is when the user signs up it writes all data to the field execpt for the number from the codes table. Once i get the code to update the user table with the first number in the table it will need to be removed from the codes table so that it's not used twice.

I hope this was a clear enought discription. Any and all help is appricated.

Response.expires = 0
Response.expiresabsolute = Now() - 1
Response.addHeader "pragma", "no-cache"
Response.addHeader "cache-control", "private"
Response.CacheControl = "no-cache"
%>
<!--#include file="db.asp"-->
<!--#include file="admin/aspmkrfn.asp"-->
<%

' Initialize common variables
x_ID = Null
x_E2DMail = Null
c_E2DMail = Null ' Initialize Password Confirmation Field
x_First_Name = Null
x_Last_Name = Null
x_Phone_Number = Null
x_u_codes = Null
x_numbers = Null
Response.Buffer = True

' Get action
sAction = Request.Form("a_register")
If (sAction = "" Or IsNull(sAction)) Then
sAction = "I" ' Display blank record
Else

' Get fields from form
x_ID = Request.Form("x_ID")
x_E2DMail = Request.Form("x_E2DMail")
x_First_Name = Request.Form("x_First_Name")
x_Last_Name = Request.Form("x_Last_Name")
x_Phone_Number = Request.Form("x_Phone_Number")
x_u_codes = Request.Form("x_u_codes")
End If

' Open connection to the database
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open xDb_Conn_Str
bUserExists = False
Select Case sAction
Case "A": ' Add

' Check for Duplicate User ID
sUserSql = "SELECT * FROM [users]"
sUserSql = sUserSql & " WHERE [E-Mail] = '" & AdjustSql(x_E2DMail) & "'"
Set rs = conn.Execute(sUserSql)
bUserExists = Not rs.Eof
rs.Close
Set rs = Nothing
If Not bUserExists Then
If AddData() Then ' Add New Record

' Load Registrant Email
sEmail = x_E2DMail

' Load Email Content
If sEmail <> "" Then
sEmailSubject = ""
sEmailFrom = ""
sEmailTo = ""
sEmailCc = ""
sEmailBcc = ""
sEmailFormat = ""
sEmailContent = ""
Call LoadEmail("register.txt")
sEmailFrom = Replace(sEmailFrom, "<!--$From-->", "webmaster@psd401.net") ' Replace Sender
sEmailTo = Replace(sEmailTo, "<!--$To-->", sEmail) ' Replace Receiver
'sEmailContent = Replace(sEmailContent, "<!--numbers-->", x_numbers)
sEmailContent = Replace(sEmailContent, "<!--E-Mail-->", x_E2DMail)
sEmailContent = Replace(sEmailContent, "<!--First Name-->", x_First_Name)
sEmailContent = Replace(sEmailContent, "<!--Last Name-->", x_Last_Name)
sEmailContent = Replace(sEmailContent, "<!--Phone Number-->", x_Phone_Number)

' Send Email
Call Send_Email(sEmailFrom, sEmailTo, sEmailCc, sEmailBcc, sEmailSubject, sEmailContent, sEmailFormat)
End If
Session("ewmsg") = "Registration Successful"
conn.Close
Set conn = Nothing
Response.Redirect "thankyou.asp"
End If
End If
End Select
%>

<%

sUserSql = "SELECT * FROM [codes]"
Set rs = conn.Execute(sUserSql)

%>


<script type="text/javascript" src="admin/ew.js"></script>
<script type="text/javascript">
<!--
EW_dateSep = "/"; // set date separator
//-->
</script>
<script type="text/javascript">
<!--
function EW_checkMyForm(EW_this) {
if (EW_this.x_E2DMail && !EW_hasValue(EW_this.x_E2DMail, "TEXT" )) {
if (!EW_onError(EW_this, EW_this.x_E2DMail, "TEXT", "Please enter password"))
return false;
}
if (EW_this.c_E2DMail.value != EW_this.x_E2DMail.value) {
if (!EW_onError(EW_this, EW_this.c_E2DMail, "TEXT", "Mismatch Password"))
return false;
}
return true;
}
//-->
</script>
<h4>
Long Distance Code Registration Page</h4>
<% If bUserExists Then %>
<p><span class="aspmaker" style="color: Red;">User Already Exists!</span></p>
<% End If %>
<form name="usersregister" id="usersregister" action="register.asp" method="post" onSubmit="return EW_checkMyForm(this);">
<p>
<input type="hidden" name="a_register" value="A">
<table border="0" cellspacing="1" cellpadding="4" bgcolor="#CCCCCC">
<tr>
<td bgcolor="#3366CC"><span class="aspmaker" style="color: #FFFFFF;">First Name&nbsp;</span></td>
<td bgcolor="#F5F5F5"><span class="aspmaker">
<input type="text" name="x_First_Name" id="x_First_Name" size="30" maxlength="50" value="<%= Server.HTMLEncode(x_First_Name&"") %>">
&nbsp;</span></td>
</tr>
<tr>
<td bgcolor="#3366CC"><span class="aspmaker" style="color: #FFFFFF;">Last Name&nbsp;</span></td>
<td bgcolor="#F5F5F5"><span class="aspmaker">
<input type="text" name="x_Last_Name" id="x_Last_Name" size="30" maxlength="50" value="<%= Server.HTMLEncode(x_Last_Name&"") %>">
&nbsp;</span></td>
</tr>
<tr>
<td bgcolor="#3366CC"><span class="aspmaker" style="color: #FFFFFF;">Phone Number&nbsp;</span></td>
<td bgcolor="#F5F5F5"><span class="aspmaker">
<input type="text" name="x_Phone_Number" id="x_Phone_Number" size="30" maxlength="50" value="<%= Server.HTMLEncode(x_Phone_Number&"") %>">
&nbsp;</span></td>
</tr>
<tr>
<td bgcolor="#3366CC"><span class="aspmaker" style="color: #FFFFFF;">E-Mail&nbsp;</span></td>
<td bgcolor="#F5F5F5"><span class="aspmaker">
<input type="text" name="x_E2DMail" id="x_E2DMail" size="30" maxlength="50" value="<%= Server.HTMLEncode(x_E2DMail&"") %>">
&nbsp;</span></td>
</tr>
<tr>
<td bgcolor="#3366CC"><span class="aspmaker" style="color: #FFFFFF;">Confirm E-Mail&nbsp;</span></td>
<td bgcolor="#F5F5F5"><span class="aspmaker">
<input type="text" name="c_E2DMail" id="c_E2DMail" size="30" maxlength="50" value="<%= Server.HTMLEncode(c_E2DMail&"") %>">
&nbsp;</span></td>
</tr>

</table>
<p>
<input type="submit" name="Action" value="Register">
</form>

<%

'-------------------------------------------------------------------------------
' Function AddData
' - Add Data
' - Variables used: field variables

Function AddData()
Dim sSql, rs, sWhere, sGroupBy, sHaving, sOrderBy

' Add New Record
sSql = "SELECT * FROM [users]"
sSql = sSql & " WHERE 0 = 1"
sGroupBy = ""
sHaving = ""
sOrderBy = ""
If sGroupBy <> "" Then
sSql = sSql & " GROUP BY " & sGroupBy
End If
If sHaving <> "" Then
sSql = sSql & " HAVING " & sHaving
End If
If sOrderBy <> "" Then
sSql = sSql & " ORDER BY " & sOrderBy
End If
Set rs = Server.CreateObject("ADODB.Recordset")
rs.CursorLocation = 3
rs.Open sSql, conn, 1, 2
rs.AddNew

' Field E-Mail
sTmp = Trim(x_E2DMail)
If Trim(sTmp) = "" Then sTmp = Null
rs("E-Mail") = sTmp

' Field First Name
sTmp = Trim(x_First_Name)
If Trim(sTmp) = "" Then sTmp = Null
rs("First Name") = sTmp

' Field Last Name
sTmp = Trim(x_Last_Name)
If Trim(sTmp) = "" Then sTmp = Null
rs("Last Name") = sTmp

' Field Phone Number
sTmp = Trim(x_Phone_Number)
If Trim(sTmp) = "" Then sTmp = Null
rs("Phone Number") = sTmp

' Field Numbers
'sTmp = Trim(x_numbers)
'If Trim(sTmp) = "" Then sTmp = Null
'rs("numbers") = sTmp

rs.Update
rs.Close
Set rs = Nothing
AddData = True
End Function
%>

miranda
09-12-2005, 01:34 AM
1.) check if user exists (you do that)
2.) if user doesn't exist create new record(you do that)
3.) get code from codes table. put it into a variable delete it from table
4.) take previosuly mentioned variable and update your users table.
5.) Send email message (you do that)



Set rs - conn.Execute("SELECT theCode FROM codes")
dim sCode: sCode = rs("theCode")
rs.Close
set rs = nothing

'if theCode is a numeric data type then use the following
conn.Execute("DELETE FROM codes WHERE theCode = " & sCode)
conn.Execute("UPDATE users SET userCodeColumn = " & sCode& " WHERE [E-Mail] = '" & AdjustSql(x_E2DMail) & "'")

'otherwise
conn.Execute("DELETE FROM codes WHERE theCode = '" & sCode & "'")
conn.Execute("UPDATE users SET userCodeColumn = '" & sCode & "' WHERE [E-Mail] = '" & AdjustSql(x_E2DMail) & "'")

schulza
09-12-2005, 08:08 PM
Thank you very much Miranda,

I was able to utilize your code and after some tweaking I was able to accomplish what I needed.

After I made the changes I moved towards pulling the user information from Active Directory and was hoping you could possibly help me with an additional issue.

I am able to connect to AD pull specific account info; the issue is trying to get the user who is logged in on the machine that hits my page. Below is the syntax for the samaccoutname and I am trying to get the remote_user into this line.


"WHERE samaccountname='schulza'"
Request.Servervariables("REMOTE_USER")

neocool00
09-13-2005, 03:33 PM
@schulza,
In order to obtain a user's login id, you have to turn off anonymous access for the website under IIS.

--NeoCool



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum