Can't find bug

[jakyra]

I have this script

Code: Select all

vSQL= join(array("SELECT", selectItem, _
							"FROM", tableName, _
							"WHERE", criteriaName, "=", ID), " ")
response.write "

" & vSQL

	set valueClient = Server.CreateObject("ADODB.Recordset")
	valueClient.open vSQL, conn,3,3
	
response.write ":" & valueClient.recordcount
if not valueClient.EOF then 
	response.write "fish"
	GetValue=valueClient(0)
else
	response.write "cow"
	GetValue = ""
end if
response.write ":" & GetValue
valueClient.close

and it was working fine
the SQL printed out is right
put it doesn't print out the recordcount or anything after that.

I'm completely baffelled.

I'm sure when the answer comes it will be some stupid answer and I would appreciate any help.

jakyra

[Rob]

Can you post the entire page?...
and also the generated sql statement?

Cheers,

[jakyra]

The I have two SQL statements in the page I'm trying to load.
SELECT PermissionText FROM TblPermissions WHERE PermissionLevel = 4

SELECT NameFirst FROM TblIndividuals WHERE ID = 1850

both test out fine on the local copy of the database (which is synched with the online one.)

the lines immediately proceeding and the calling lines are (respectively):

Session("user") = ID
Session("ForceChange") = ForceChange
Session("myState") = Permissions
Session("myTeacherID") = TeacherID
Session("mySchoolCode") = SchoolCode
myStatus = GetValue(Permissions, "permissions")
myName = GetValue( ID, "reviewer")
Session("myStatus") = myStatus
Session("myName") =myName

which returns the following if printed out
user: 1850
ForceChange: False
myState: 4
myTeacherID:
mySchoolCode:
myStatus:
myName:



The entire function is :

Code: Select all

<% 
	function GetValue(ID, itemType)
		'The ID is the ID number of the item.
		' itemType controls what lookup you use. i.e. culture, level, reviewer
		
		dim  selectItem
		dim tableName
		dim criteriaName

		if ID = ""  or itemType = "" then exit function
		if not isNumeric(ID) then exit function
	
		itemType = LCase(itemType)
			
		SELECT Case itemType
		case "culture"
			selectItem="Culture"
			tableName="TblCultures"
			criteriaName="ID"
		case "level"
			selectItem= "MyLevel"
			tableName="TblLevels"
			criteriaName="LevelID"
		case "reviewer"
			selectItem="NameFirst"
			tableName="TblIndividuals"
			criteriaName="ID"
		case "email"
			selectItem="Email"
			tableName="TblIndividuals"
			criteriaName="ID"
		case "reviewerlocation"
			selectItem = "NameFirst & ' ' & NameLast & iif(Location>'', ', ' & Location, '') as reviewer"
			tableName="TblIndividuals"
			criteriaName="ID"
		case "permissions"
			selectItem="PermissionText"
			tableName="TblPermissions"
			criteriaName="PermissionLevel"
		case else
			exit function
		end Select
		
			vSQL= join(array("SELECT", selectItem, _
										"FROM", tableName, _
										"WHERE", criteriaName, "=", ID), " ")
			response.write "

" & vSQL
			
				set valueClient = Server.CreateObject("ADODB.Recordset")
				valueClient.open vSQL, conn,3,3
				
			response.write ":" & valueClient.recordcount
			if not valueClient.EOF then 
				GetValue=valueClient(0)
			else
				GetValue = ""
			end if
			response.write ":" & GetValue
			valueClient.close
	end function
%>

Is that enough? I'll put the calling page and the relevant function calls in another post so it's easier to read (or ignore)

thanks!
jakyra

[jakyra]

Page of originating code

Code: Select all

<%@LANGUAGE="VBSCRIPT"%>
   
	
	

<% 
	Username=Request.Form("UserName")
	Password=Cryptor.Crypt (strSalt, Request.Form("Password"))
%>
<%

	'check to see if the username and password combo are in the table
	SQL="SELECT * FROM Table1 WHERE Username='" & Username & "' AND Password='" & password & "'"
'	response.write "

" &  SQL
	set myclient = Server.CreateObject("ADODB.Recordset")
	myclient.open SQL, Sconn, 3, 3
	 
	if  myclient.EOF then 'username and password do not match
			myclient.close
			
			'see if this user has tried to login and failed before
			SQL = "SELECT Username, attempts, lockout FROM TblAttempts WHERE Username ='" & Username & "'"
			myclient.open SQL, Sconn, 3, 3
			
		 if myclient.EOF then  'if not, then add them to the table and set the number of tries equal to 1
	'		myclient.close
			Sconn.execute "INSERT INTO TblAttempts (Username, attempts) VALUES ('" & Username & "', 1)"
				response.cookies("ChildLit")("message")  = "Login failed. User names and passwords are case sensitive and cannot contain spaces. Please check your user name and password and try again."
		 else 'if so (user hasn't tried before)
		 	attempts = myclient("attempts")
			if attempts = 2 then  'if this is the third failed login, then lock them out
				myclient.close
				Sconn.execute "UPDATE TblAttempts SET lockout = True WHERE Username='" & Username & "'"
%>
				
<%
				session.abandon
				response.redirect "Lockout.html"
			else 'if not, give them another try
				attempts = attempts+1 
				response.cookies("ChildLit")("message")  = "Login failed. User names and passwords are case sensitive and cannot contain spaces. Please check your user name and password and try again."
				Sconn.execute "UPDATE TblAttempts SET attempts = " & attempts & "  WHERE Username='" & Username & "'"
				
				'record the number of tries in a cookie for the index page
				end if 'if myclient("attempts") = 2 then
		end if 'if myclient.EOF then  'user hasn't tried before

		myclient.close
%>
		
<%
		response.redirect "index.asp"

	else 'Username and password match
		ID = myclient("ID")
		ForceChange = myclient("ForceChange")
		myclient.close
		
		SQL = "INSERT INTO TblSession (UserID, Event) VALUES(" & ID & ", 'Login')"
'		response.write SQL
		Sconn.execute SQL
		
		SQL = "UPDATE TblAttempts SET attempts = 0 WHERE Username='" & Username & "'"
		Sconn.execute  SQL
%>


<%
		SQL = "SELECT Permissions, TeacherID, SchoolCode FROM TblIndividuals WHERE ID = " & ID
		set myclient = Server.CreateObject("ADODB.Recordset")
		myclient.open SQL, conn, 3, 3
		GetFields(myclient)  <----- this is a function that loads a recordset into variables of the same name.
		myclient.close

%>

<%

		Session("user") = ID
		Session("ForceChange") = ForceChange
		Session("myState") = Permissions
		Session("myTeacherID") = TeacherID
		Session("mySchoolCode") = SchoolCode
		myStatus =  GetValue(Permissions, "permissions")
		myName =  GetValue( ID, "reviewer")
'		response.write myStatus & "
" & myName
		Session("myStatus") = myStatus
		Session("myName") =myName
		response.cookies("ChildLit")("message")  = ""
	dim i
	For Each i in Session.Contents
	  Response.Write(i & ": " & Session.Contents(i) & "
" )
	Next
				
		if ForceChange then 
			response.cookies("ChildLit")("FirstLogin") = True
			if SchoolCode =  "StKate" then 
				response.redirect "StKateQuestions.asp"
			else
				response.redirect "PasswordChange.asp"
			end if
		else
			response.cookies("ChildLit")("FirstLogin") = False
			if myStatus= "Staff" then 
	'			response.redirect "StaffMailingListMgt.asp"
			else
	'			response.redirect "ReviewEntry.asp"
			end if
		end if
	end if
	
%>

[Smokie]

im abit concerned about all the times you have included the same file - #include file="Code/SecurityClose.asp"

Are you aware that includes are processed before the asp code?

[jakyra]

The includes file has the code that closes the connection to the database.

It was my understanding that I need to do this before the end of the page if I redirect to another page. I didn't think that the connection would be closed.

Is there a "more correct" way to do this?

Thanks,
jakyra

[Smokie]

nope, your code wont stop running when it gets to a Response.Redirect, you can just open your connection at the top and close it at the bottom.

[jakyra]

I found my error
*hangs head in shame*
I'm too embarassed to say what it was.

Thanks for taking the time to look through this.


jakyra (who's super embarassed)

[jakyra]

Smokie,
Thanks for the info! I guess this is what comes of teaching yourself.

jakyra

[Smokie]

hey no problem, im self taught too