Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New to the CF scene
    Join Date
    Aug 2005
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    INSERT & UPDATE tables

    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
    %>
    Last edited by schulza; 09-09-2005 at 10:00 PM.

  • #2
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,071
    Thanks
    4
    Thanked 8 Times in 8 Posts
    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)


    Code:
    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) & "'")

  • #3
    New to the CF scene
    Join Date
    Aug 2005
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Active Directory

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

  • #4
    Regular Coder
    Join Date
    Sep 2004
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts
    @schulza,
    In order to obtain a user's login id, you have to turn off anonymous access for the website under IIS.

    --NeoCool


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •