Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New to the CF scene
    Join Date
    May 2007
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How do I keep a random number from changing when the page is refreshed?

    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!

  • #2
    Senior Coder Spudhead's Avatar
    Join Date
    Jun 2002
    Location
    London, UK
    Posts
    1,856
    Thanks
    8
    Thanked 110 Times in 109 Posts
    Ok I've reposted this with code brackets cos reading that lot without indentation is like swimming through spaghetti.

    Code:
    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:

    Code:
    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:

    Code:
    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?


  •  

    Posting Permissions

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