Query timeout expired when doing update to password to a field

[Erick Otieni]

Hi all,

Does anyone have an idea to solve this problem in asp? I have tried to look everywhere for the last week but no one seems to have the answer.

The issue is that I have the script below that is used to generate new password which is the encripted to update the password field in the database. It then suprisingly times out when used with sql server and the field is not updated, however, if you use the access database, the password field is updated and the new password is sent to the user.

Which means that the issue could be on the rs handling and sql...

Please help, or advice...

Cheers,

Erick

 <!--#include file="include/clean.asp"-->
<!--#include file="include/dbcommon.asp"-->
<!--#include file="include/md5.asp"-->
<!--#include file="libs/xtempl.asp"-->
<%
if SESSION("count_captcha")="" or SESSION("count_captcha")>5 then SESSION("count_captcha")=0
 
dim xt
set xt = new XTempl
cEmailField = "Email (mail)"
reminded=false
strSearchBy="username"
 
strUsername=""
strEmail=""
strMessage=""
 
dbConnection=""
db_connect()
DoEvent "BeforeProcessRemind dbConnection"
 
 
if request.form("btnSubmit") = "Remind" then
    strSearchBy=request.Form("searchby")
    'strUsername=request.Form("username")
    strUsername = cleanName(request.Form("username"))
    strEmail=request.Form("email")
       Set rstemp = server.CreateObject("ADODB.Recordset")
       Set rs = server.CreateObject("ADODB.Recordset")
 
    rstemp.open "select * from [Staff Details] where 1=0",dbConnection,1,2
 
    tosearch=false
    if strSearchBy<>"email" then
        value=strUsername
        if cstr(value)<>"" then tosearch=true
        if FieldNeedQuotes(rstemp,cUserNameField) then 
            value="'" & db_addslashes(value) & "'"
        else
            value=my_numeric(value)
        end if
        sWhere=AddFieldWrappers(cUserNameField) & "=" & value
    else
        value=strEmail
        if cstr(value)<>"" then tosearch=true
        if FieldNeedQuotes(rstemp,cEmailField) then
            value="'" & db_addslashes(value) & "'"
        else
            value=my_numeric(value)
        end if
        sWhere=AddFieldWrappers(cEmailField) & "=" & value
    end if
 
    if tosearch then DoEvent "tosearch = BeforeRemindPassword(strUsername,strEmail)"
 
 
    if tosearch then
 
        strSQL="select " & AddFieldWrappers(cUserNameField) & "," & AddFieldWrappers(cPasswordField) & "," & AddFieldWrappers(cEmailField) & " from [Staff Details] where " & sWhere
        rs.Open strSQL,dbConnection,1,2
        'set rs = server.createobject("adodb.recordset")
        'rs.open strSQL, dbConnection,1,2
        if not rs.EOF then
            password=rs(1)
'//    generate 6 letters length password
            password=""
            randomize
            for ind=0 to 5
                j=rnd(35)
                if j<26 then
                    password=password & chr(asc("a")+j)
                else
                    password=password & chr(asc("0")-26+j)
                end if
 
            next
            dbConnection.Execute "update [Staff Details] set " & AddFieldWrappers(cPasswordField) & "='" & md5(password) & "' where " & sWhere
            'response.Write(password)
            'response.End()
            'j
            url = "http://" & request.ServerVariables("SERVER_NAME")
            if CStr(request.ServerVariables("SERVER_PORT"))<>"80" then _
                url = url & ":" & request.ServerVariables("SERVER_PORT")
            url= url & request.ServerVariables("SCRIPT_NAME")
            message="Password reminder" & vbcrlf
            message=message & "You asked to remind your username and password at" & " " & url & vbcrlf
            message=message & "Username" & " " & rs(0) & vbcrlf
            message=message & "Password" & " " & password & vbcrlf
            sendmail rs(2),"Password reminder",message
            reminded=true
            DoEvent "AfterRemindPassword strUsername,strEmail"
            loginlink_attrs="href=""login.asp"
            if strSearchBy<>"email" then loginlink_attrs=loginlink_attrs & "?username=" & server.HTMLEncode(strUsername)
            loginlink_attrs=loginlink_attrs & """"
            xt.assign "loginlink_attrs",loginlink_attrs
            xt.assign "body",true
            SESSION("count_captcha")=SESSION("count_captcha")+1
            xt.display("remind_success.htm")
            response.End
        end if
    end if
 
    if not reminded then
        if strSearchBy<>"email" then
            strMessage="User" & " <i>" & strUsername & "</i> " & "is not registered."
        else
            strMessage="This email doesn't exist in our database"
        end if
    end if
 
end if
 
emailradio_attrs="onclick=""document.forms.form1.searchby.value='email'; UpdateControls();"""
usernameradio_attrs="onclick=""document.forms.form1.searchby.value='username'; UpdateControls();"""
 
if strSearchBy="username" then
    usernameradio_attrs=usernameradio_attrs & " checked"
    search_disabled = "email"
end if
 
xt.assign "emailradio_attrs",emailradio_attrs
xt.assign "usernameradio_attrs",usernameradio_attrs
 
xt.assign "username_attrs","value=""" & my_htmlspecialchars(strUsername) & """"
xt.assign "email_attrs","value=""" & my_htmlspecialchars(strEmail) & """"
if strMessage<>"" then
    xt.assign "message",strMessage
    xt.assign "message_block",true
    if captcha=1 then SESSION("count_captcha")=SESSION("count_captcha")+1
end if
 
set body=CreateObject("Scripting.Dictionary")
str="<script language = JavaScript>"
str=str & "function OnKeyDown(){"
str=str & "e = window.event;"
str=str & "if (e.keyCode == 13){"
str=str & "e.cancel = true;"
str=str & "document.forms[0].submit();}}"
str=str & "function UpdateControls(){"
str=str & "if (document.forms.form1.searchby.value==""username""){"
str=str & "document.forms.form1.username.style.backgroundColor='white';"
str=str & "document.forms.form1.email.style.backgroundColor='gainsboro';"
str=str & "document.forms.form1.username.disabled=false;"
str=str & "document.forms.form1.email.disabled=true;}else{"
str=str & "document.forms.form1.username.style.backgroundColor='gainsboro';"
str=str & "document.forms.form1.email.style.backgroundColor='white';"
str=str & "document.forms.form1.username.disabled=true;"
str=str & "document.forms.form1.email.disabled=false;}}</script>"
str=str & "<form method=post action=""remind.asp"" id=form1 name=form1>"
str=str & "<input type=hidden name=btnSubmit value=""Remind"">"
str=str & "<input type=""Hidden"" name=""searchby"" value=""" & strSearchBy & """>"
body("begin")=str
body("end")="</form><script language=""JavaScript"">document.forms.form1." & search_disabled & ".disabled=false;    UpdateControls();</script>"
xt.assign "body",body
 
 
templatefile = "remind.htm"
DoEvent "BeforeShowRemindPwd xt,templatefile"
xt.display(templatefile)
%> 

[jhardman]

just some basic troubleshooting steps:

Can you query or execute a static command to your sql server? in other words, does "select * from [Staff Details]" work? Does a static update command like "update [staff Details] set pword = 'asdf1' where idnum=3" work?

Jared

[Erick Otieni]

Dear Jared,

thanks for the reply, I have queried this

update [Staff Details] set [password]='MD5 hash value' where [username]='admin'

which is generated when I did response write in the sql server but yes it is indicating that it is executed but when you use the password which was sent after the generated one it still seems the field was not updated.

There is no error though which is generated, so I still do not understand why the password field does not update with the new encrypted one. The sql query indicates "Query successfully executed" and one "rows affected".

Also, I have tried "select * from [Staff Details]" query and it works pretty well returning all the values in the table

Thanks in advance

Erick