Looping Issue

[venthi]

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.

[phpnovice]

Put in Response.Write statements to debug your code while it is running.

[venthi]

Originally posted by phpnovice
Put in Response.Write statements to debug your code while it is running.

Sorry, I dont quite understand. Explain pls.

[phpnovice]

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.

[lmf232s]

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.

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

[venthi]

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.