Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    18

    Unanswered: checking for existing values in database

    could anyone help me out with this ? i'm stuck with this error for the whole afternoon ..

    im trying to check whether the userID and mobile Number that the user keyed in has already existed in the database. if it exist, then it will redirect back to the form page with the error message on top.

    this is what i did in my action page.

    <%
    Dim obj
    Set obj= Server.CreateObject("ADODB.RecordSet")

    Dim sqlstr
    sqlstr="select userID from Users where userID = '"&Session("userID")&"'"

    obj.open sqlstr, conn


    if obj.eof then
    Session("useridError") = "negative"


    Dim obj2
    Set obj2= Server.CreateObject("ADODB.RecordSet")

    dim sql
    sql = "select mobileNo from Users where mobileNo = "&Session("mobileNo")&""

    obj2.open sql, conn

    if obj2.eof then

    Session("mobileError") = "negative"

    Dim obj3
    Set obj3= Server.CreateObject("ADODB.RecordSet")

    Dim sqlString
    sqlString="insert into Users(userID, firstName, lastName, mobileNo, gender, pwd, reminderAdd, emailAdd, address, postalCode)"
    sqlString=sqlString&"values('"&Session("userID")
    sqlString=sqlString&"','"&Session("firstName")
    sqlString=sqlString&"','"&Session("lastName")
    sqlString=sqlString&"',"&Session("mobileNo")
    sqlString=sqlString&",'"&Session("gender")
    sqlString=sqlString&"','"&Session("pwd")
    sqlString=sqlString&"','"&Session("reminderAdd")
    sqlString=sqlString&"','"&Session("emailAdd")
    sqlString=sqlString&"','"&Session("address")
    sqlString=sqlString&"',"&Session("postalCode")&")"

    obj3.Open sqlString, conn


    %>
    <p class="style1">User <u><span class="style1"><%=Session("userid")%></span></u> has
    been successfully created! <br>
    <br>
    <%
    else

    Response.Redirect("../user/register.asp")
    %>
    </p>
    <p class="style1">
    <%
    end if
    ELSE

    Response.Redirect("../user/register.asp")
    end if
    %>
    </form>

    and this is what i did in the form page ..

    <% if Session("useridError") <> "negative" then %>
    <span class="style2"> user id in used</span>
    <% elseif session("mobileError") <> "negative" then %>
    <span class="style2"> mobile number in used</span>
    <% end if %>


    could anyone help me out with this?

    thanks alot in advance ..

  2. #2
    Join Date
    Dec 2003
    Posts
    454
    I made some changes in the part of ASP code. You do not need to open many record sets. That will reduce the performance. When you finish using a record set, you can close it and then reuse it again. For the INSERT statement, you can not use a record set to execute the sql statement. Insead, you have to use a Connection object to execute it.

    After you finish the part of accessing database, you can use IF statement to check the session variables and then you can know which page should be redirected. Does that give you a help?

    <%
    Dim obj, sqlstr
    Set obj = Server.CreateObject("ADODB.RecordSet")
    sqlstr = "select userID from Users where userID = '" & Session("userID") & "'"
    obj.open sqlstr, conn

    if obj.eof then
    Session("useridError") = "negative"
    obj.Close

    sqlstr = "select mobileNo from Users where mobileNo = '" & Session("mobileNo") & "'"
    obj.open sqlstr, conn

    if obj.eof then
    Session("mobileError") = "negative"
    obj.Close

    sqlstr = "insert into Users(userID, firstName, lastName, mobileNo, gender, pwd, reminderAdd, emailAdd, address, postalCode)"
    sqlstr = sqlstr & "values('"&Session("userID")
    sqlstr = sqlstr & "','"&Session("firstName")
    sqlstr = sqlstr & "','"&Session("lastName")
    sqlstr = sqlstr & "',"&Session("mobileNo")
    sqlstr = sqlstr & ",'"&Session("gender")
    sqlstr = sqlstr & "','"&Session("pwd")
    sqlstr = sqlstr & "','"&Session("reminderAdd")
    sqlstr = sqlstr & "','"&Session("emailAdd")
    sqlstr = sqlstr & "','"&Session("address")
    sqlstr = sqlstr & "',"&Session("postalCode")&")"

    conn.Execute(sqlstr)
    Set obj = Nothing
    conn.Close
    Set Conn = Nothing
    %>

  3. #3
    Join Date
    Mar 2004
    Posts
    18
    yeah .. that certainly helps alot. many thanks !

    but the problem is that, when i tried to check for the session variables, a logic error occurs. the mobile errors is only checked. as in, even when i tried to enter an existing user ID , it still returns the mobile error instead of the user ID error ..

  4. #4
    Join Date
    Dec 2003
    Posts
    454
    What is the type of the field UserID and MobileNo in your table? If UserID is int, you can remove "'" before and after Session("userID"). The same thing is for the field MobileNo.

    <%
    Dim obj, sqlstr
    Set obj = Server.CreateObject("ADODB.RecordSet")

    sqlstr = "select userID from Users where userID = " & Session("userID")
    obj.open sqlstr, conn

    if obj.eof then
    Session("useridError") = "negative"
    else
    Session("useridError") = ""
    obj.Close

    sqlstr = "select mobileNo from Users where mobileNo = " & Session("mobileNo")
    obj.open sqlstr, conn

    if obj.eof then
    Session("mobileError") = "negative"
    else
    Session("mobileError") = ""
    obj.Close

    sqlstr = "insert into Users(userID, firstName, lastName, mobileNo, gender, pwd, reminderAdd, emailAdd, address, postalCode)"
    sqlstr = sqlstr & "values(" & Session("userID")
    sqlstr = sqlstr & ", '" & Session("firstName")
    sqlstr = sqlstr & "', '" & Session("lastName")
    sqlstr = sqlstr & "', " & Session("mobileNo")
    sqlstr = sqlstr & ", '" & Session("gender")
    sqlstr = sqlstr & "', '" & Session("pwd")
    sqlstr = sqlstr & "', '" & Session("reminderAdd")
    sqlstr = sqlstr & "', '" & Session("emailAdd")
    sqlstr = sqlstr & "', '" & Session("address")
    sqlstr = sqlstr & "', " & Session("postalCode") & ")"

    conn.Execute(sqlstr)
    Set obj = Nothing
    conn.Close
    Set Conn = Nothing
    %>

    If you still have the same problem, please post the code which is used to check the session variables.

  5. #5
    Join Date
    Mar 2004
    Posts
    18
    okay .. i'll try that out .

    thanks alot !!! =)

Posting Permissions

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