Query timeout expired when doing update to password to a field

[eribros]

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