PDA

View Full Version : How do I keep a random number from changing when the page is refreshed?

sqlkat
05-03-2007, 01:42 AM
Help! I usually work with T-SQL, and somehow I ended up trying to fix an .asp file for our website. I've looked and looked and can't find an answer to my question. I may not even be in the right place, but here goes...

The page I'm working on displays store and customer info. The customer's phone number is replaced by random codes. The codes are connected to the phone number in a SQL Server table. The problem is, when you refresh the page the same record continues to be displayed only with new phone number codes. We need the codes to remain the same until a new customer is selected.

As far as I can understand, the first part of the below code pulls up the same customer name and info if the current record status is unchanged, while the "else" section pulls up the new customer.

<%
Dim Rs1, Rs2, CSIRs2, Acc, n, val, SId, Sur
Dim Priority, CTime, NoAtt
Dim lastindextime
Dim lastindexdate
Dim SQLStr, RndNum
Dim Accnm, PNo, DetString
Dim NumDialed
Dim CSIRs, CSIRs1

Set Rs1=Server.CreateObject("Adodb.Recordset")
Set Rs=Server.CreateObject("Adodb.Recordset")
Set Rs2=Server.CreateObject("Adodb.Recordset")
Set CSIRs2=Server.CreateObject("Adodb.Recordset")
Rs.Open "Select Top 1 ID, Status from CSI.dbo.CSISurveyAttempts where SurveyId='" & Session("SurveyId") & "' order by Id Desc", Con1
If Not Rs.EOF and Not Rs.BOF Then
If CInt(Rs("Status")) = 0 Then
SQLStr = "Select SurveyID, [Account Name], [Facility Name],[Transaction ID# / Facility#], [Customer First Name], [Customer Last Name], Work#, Home#, [Time Zone], [Date Called], [Time Called] from CSI.dbo.CSIData where SurveyID = '" & Session("SurveyID") & "'"
Rs1.Open SQLStr, Con1
if Rs1.EOF and Rs1.BOF Then
Response.Write("Session Expired")
Response.End()
end if
RndNum = Session("Random")
SId = Session("SurveyID")
Accnm = Session("AccountNm")
FName = Rs1("Customer First Name")
LName = Rs1("Customer Last Name")
TransID = Rs1("Transaction ID# / Facility#")
BusName = Rs1("Facility Name")
If Len(Trim(Rs1("Home#"))) > 0 Then
HPNo = Replace(Replace(Replace(Rs1("Home#"),"(",""),")",""),"-","")
End If
If Len(Trim(Rs1("Work#"))) > 0 Then
WPNo = Replace(Replace(Replace(Rs1("Work#"),"(",""),")",""),"-","")
End If
SQLStr = "EXEC CSI.dbo.sp_InsertRandomNumNew '" & Rs1("Account Name") & "','" & Rs1("SurveyID") & "','" & Right(HPNo,10) & "','" & Right(WPNo,10) & "'"
Rs1.Close
CSIRs2.Open SQLStr, Con1
HRndNum = CSIRs2(0)
WRndNum = CSIRs2(1)
HomeWork = CSIRs2(2)
CSIRs2.Close
else
SQLStr = "EXEC CSI.dbo.sp_GetTopPrioritySurveyRecord"
Rs1.Open SQLStr, Con1
if Rs1.EOF and Rs1.BOF then
Response.Write("No Records found suitable for Calling")
Response.End()
end if
SId = Rs1("SurveyID")
Accnm = Rs1("Account Name")
FName = Rs1("Customer First Name")
LName = Rs1("Customer Last Name")
TransID = Rs1("Transaction ID# / Facility#")
BusName = Rs1("Facility Name")
if Len(Trim(Rs1("Home#"))) > 0 Then
HPNo = Replace(Replace(Replace(Rs1("Home#"),"(",""),")",""),"-","")
end if
if Len(Trim(Rs1("Work#"))) > 0 Then
WPNo = Replace(Replace(Replace(Rs1("Work#"),"(",""),")",""),"-","")
end if
SQLStr = "EXEC CSI.dbo.sp_InsertRandomNumNew '" & Rs1("Account Name") & "','" & Rs1("SurveyID") & "','" & Right(HPNo,10) & "','" & Right(WPNo,10) & "'"
Rs1.Close
CSIRs2.Open SQLStr, Con1
HRndNum = CSIRs2(0)
WRndNum = CSIRs2(1)
HomeWork = CSIRs2(2)
CSIRs2.Close
End If
Rs.Close
Session("myflag")=""
Session("AccountNm") = Accnm
Session("SurveyID") = SId
Session("Randomh") = HRndNum
Session("Randomw") = WRndNum
Session("HW") = HomeWork
Session("Facty") = TransID
CSIRs2.Open "Select Top 1 QuestionSet, Opening, Closing, DisplayName from CSI.dbo.AcnmQS where AccountName = '" & Accnm & "' order by questionset desc", Con1
%>

I've tried everything I can think of. Removing the SQLStr and trying to plug in the correct info just resulted in the hidden surveyId and accountnm values being displayed there. I'm really out of my league. Any suggestions would be appreciated.

Thanks! :)
Spudhead
05-04-2007, 03:35 PM
Ok I've reposted this with code brackets cos reading that lot without indentation is like swimming through spaghetti.


Set Rs1=Server.CreateObject("Adodb.Recordset")
Set Rs=Server.CreateObject("Adodb.Recordset")
Set Rs2=Server.CreateObject("Adodb.Recordset")
Set CSIRs2=Server.CreateObject("Adodb.Recordset")

Rs.Open "Select Top 1 ID, Status from CSI.dbo.CSISurveyAttempts where SurveyId='" & Session("SurveyId") & "' order by Id Desc", Con1

If Not Rs.EOF and Not Rs.BOF Then
If CInt(Rs("Status")) = 0 Then

SQLStr = "Select SurveyID, [Account Name], [Facility Name],[Transaction ID# / Facility#], [Customer First Name], [Customer Last Name], Work#, Home#, [Time Zone], [Date Called], [Time Called] from CSI.dbo.CSIData where SurveyID = '" & Session("SurveyID") & "'"
Rs1.Open SQLStr, Con1

if Rs1.EOF and Rs1.BOF Then
Response.Write("Session Expired")
Response.End()
end if

RndNum = Session("Random")
SId = Session("SurveyID")
Accnm = Session("AccountNm")
FName = Rs1("Customer First Name")
LName = Rs1("Customer Last Name")
TransID = Rs1("Transaction ID# / Facility#")
BusName = Rs1("Facility Name")

If Len(Trim(Rs1("Home#"))) > 0 Then
HPNo = Replace(Replace(Replace(Rs1("Home#"),"(",""),")",""),"-","")
End If

If Len(Trim(Rs1("Work#"))) > 0 Then
WPNo = Replace(Replace(Replace(Rs1("Work#"),"(",""),")",""),"-","")
End If

SQLStr = "EXEC CSI.dbo.sp_InsertRandomNumNew '" & Rs1("Account Name") & "','" & Rs1("SurveyID") & "','" & Right(HPNo,10) & "','" & Right(WPNo,10) & "'"

Rs1.Close

CSIRs2.Open SQLStr, Con1
HRndNum = CSIRs2(0)
WRndNum = CSIRs2(1)
HomeWork = CSIRs2(2)
CSIRs2.Close

else

SQLStr = "EXEC CSI.dbo.sp_GetTopPrioritySurveyRecord"
Rs1.Open SQLStr, Con1

if Rs1.EOF and Rs1.BOF then
Response.Write("No Records found suitable for Calling")
Response.End()
end if

SId = Rs1("SurveyID")
Accnm = Rs1("Account Name")
FName = Rs1("Customer First Name")
LName = Rs1("Customer Last Name")
TransID = Rs1("Transaction ID# / Facility#")
BusName = Rs1("Facility Name")

if Len(Trim(Rs1("Home#"))) > 0 Then
HPNo = Replace(Replace(Replace(Rs1("Home#"),"(",""),")",""),"-","")
end if
if Len(Trim(Rs1("Work#"))) > 0 Then
WPNo = Replace(Replace(Replace(Rs1("Work#"),"(",""),")",""),"-","")
end if

Rs1.Close

SQLStr = "EXEC CSI.dbo.sp_InsertRandomNumNew '" & Rs1("Account Name") & "','" & Rs1("SurveyID") & "','" & Right(HPNo,10) & "','" & Right(WPNo,10) & "'"
CSIRs2.Open SQLStr, Con1
HRndNum = CSIRs2(0)
WRndNum = CSIRs2(1)
HomeWork = CSIRs2(2)
CSIRs2.Close
End If

Rs.Close

Session("myflag")=""
Session("AccountNm") = Accnm
Session("SurveyID") = SId
Session("Randomh") = HRndNum
Session("Randomw") = WRndNum
Session("HW") = HomeWork
Session("Facty") = TransID

CSIRs2.Open "Select Top 1 QuestionSet, Opening, Closing, DisplayName from CSI.dbo.AcnmQS where AccountName = '" & Accnm & "' order by questionset desc", Con1


If I get what you're after, then I think you'll need to change this:


SQLStr = "EXEC CSI.dbo.sp_InsertRandomNumNew '" & Rs1("Account Name") & "','" & Rs1("SurveyID") & "','" & Right(HPNo,10) & "','" & Right(WPNo,10) & "'"
CSIRs2.Open SQLStr, Con1
HRndNum = CSIRs2(0)
WRndNum = CSIRs2(1)
HomeWork = CSIRs2(2)
CSIRs2.Close

If you want these random numbers to be generated the first time the page is fired for each user session but not after, then it makes sense to use session variables.

So:


if session("HRndNum ") <> "" then
' already have values in session
HRndNum = session("HRndNum")
WRndNum = session("WRndNum")
HomeWork = session("HomeWork")
else
' need to get values and populate session variables with them
SQLStr = "EXEC CSI.dbo.sp_InsertRandomNumNew '" & Rs1("Account Name") & "','" & Rs1("SurveyID") & "','" & Right(HPNo,10) & "','" & Right(WPNo,10) & "'"
CSIRs2.Open SQLStr, Con1
HRndNum = CSIRs2(0)
WRndNum = CSIRs2(1)
HomeWork = CSIRs2(2)
CSIRs2.Close
session("HRndNum") = HRndNum
session("WRndNum") = WRndNum
session("HomeWork") = HomeWork
end if


Make sense?