www.webdeveloper.com
Results 1 to 6 of 6

Thread: Looping Issue

  1. #1
    Join Date
    Mar 2005
    Posts
    3

    Looping Issue

    Hi Guys!

    Please help me on how to solve this database coding. Below is my coding:

    'Set variables and create objects
    set objRecordset = Server.CreateObject("ADODB.Recordset")
    strRecordset = "SELECT IID FROM eBookUsers WHERE UID = '" &UCASE(miid1) & "'"

    objRecordset.Open strRecordset, eBookConnStr, adOpenKeyset, adLockPessimistic, adCmdText

    do while not objRecordset.eof

    miid2 = objRecordset.Fields.Item("IID").Value

    SQLstmt = "SELECT COUNT(*) As level2 FROM eBookUsers WHERE UID = '" &miid2 & "'"
    Set myConn = Server.CreateObject("ADODB.Connection")
    myPath = Server.MapPath("/mkeBook/ebook.mdb")
    myConn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & myPath & ";"
    mySQL = SQLstmt
    Set myRS = Server.CreateObject("ADODB.RecordSet")
    myRS.Open mySQL, myConn
    SecondValue = myRS("level2")
    myRS.Close
    Set myRS = Nothing
    myConn.Close
    Set myConn = Nothing

    objRecordset.MoveNext
    LOOP

    My intention is to calculate the COUNT when UID = miid2. It do calculate, but only the first data taken into consideration. The next or subsequent one not taken, means it is not doing the loop.

    Help please.

    Thanks.

  2. #2
    Join Date
    Dec 2004
    Posts
    8,637
    Put in Response.Write statements to debug your code while it is running.

  3. #3
    Join Date
    Mar 2005
    Posts
    3
    Originally posted by phpnovice
    Put in Response.Write statements to debug your code while it is running.
    Sorry, I dont quite understand. Explain pls.

  4. #4
    Join Date
    Dec 2004
    Posts
    8,637
    The way to find out what your code is actually doing, while it is running, is to insert several Response.Write statements, at key points in the process, to judiciously display variable names and contents -- so that you can see for yourself why certain decisions are made (e.g., to loop or not to loop) and from where certain results are being seen.

  5. #5
    Join Date
    Jun 2004
    Location
    Kansas City, MO
    Posts
    1,607
    First thing,
    Put your connection strings at the top of the page(better to be put in an include file and call them as needed but)
    Everytime you loop through the first query, you are creating a connection object and a record set, you only need to do this once.
    Your also closing the connection and record set when you are done. Leve them open until your done, close them at the bottom of the page.
    So any way
    chech this out, i removed some stuff so that its more readable.
    Code:
    <%
    'Define you connections up here. 
    Set myConn = Server.CreateObject("ADODB.Connection") 
    myPath = Server.MapPath("/mkeBook/ebook.mdb") 
    myConn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & myPath & ";" 
    
    set objRecordset = Server.CreateObject("ADODB.Recordset")
    strRecordset = "SELECT IID FROM eBookUsers WHERE UID = '" &UCASE(miid1) & "'"
    
    objRecordset.Open strRecordset, eBookConnStr, adOpenKeyset, adLockPessimistic, adCmdText
    
    do while not objRecordset.eof
    	'get the value from the DB
    	miid2 = objRecordset.Fields.Item("IID").Value
    
    	SQLstmt = " SELECT COUNT(*) As level2 " & _ 
    	          " FROM eBookUsers " & _ 
    	          " WHERE UID = '" &miid2 & "'"
    	myRS.Open mySQL, myConn 
    	
    	If myRS.EOF then 
    		Response.Write "No record this time"
    	else
    		'we have some numbers what do you want to do with them. If you want to continue counting then you 
    		'need someting like this
    		'SecondValue = SecondValue + myRS("level12")
    		SecondValue = myRS("level2") 
    	end if 
    	
    	myRS.Close 
    	
    	objRecordset.MoveNext
    LOOP
    
    'close all your connections down here.
    Set myRS = Nothing 
    myConn.Close 
    Set myConn = Nothing
    
    set objRecordset = nothing
    eBookConnStr.Close
    set eBookConnStr = Nothing
    %>

  6. #6
    Join Date
    Mar 2005
    Posts
    3
    thank you very much phpnovice and lmf232s!

    phpnovice advice and lmf232s coding were very much helpful to complete my coding as I wanted.

    keep up ur good intentions.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles