Query timeout expired when doing update to rs

[erioch]

Hi all,

I have this code for the password reset form, It is supposed to generate a new password and the encrypt it to update the database then send an email to the user the new password for the user to log in and change the password.

This is because the password is encrypted in the database and can not be decrypted for the user to get their original passwords.

The problem is that after generating the new password, the database can not be updated since the rs is already used or something, so the Query timeout expires. this is a problem only in the sql server but not access since in access the field is updated with the new encrypted password that has been sent to the user.

the error below is what is generated

Microsoft OLE DB Provider for SQL Server error '80040e31'

Query timeout expired

/Users/remind.asp, line 76

so i do think the problem is coming while trying to execute this line.. dbConnection.Execute "update [Staff Details] set " & AddFieldWrappers(cPasswordField) & "='" & md5(password) & "' where " & sWhere since when you disable it, the new generated password is sent to the user but since the field is not updated, the user can log in due to invalid password.

Does anyone have an idea on how to go about this? I will really appreciate.

Regards

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 (Hab 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
		
		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
			
			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)
%>