SQL does not return records.

[mych]

I am developing a protal. It will have the option to recognise users from the workstation login details or by using a log-in screen.

The code for recognising the user from the workstation login works great...

glbstrUserLogon = Right(Request.Servervariables("LOGON_USER"),7)

		strSQL = "SELECT * FROM aTblPIDs WHERE UserID= '" & glbstrUserLogon & "';"

		set rsUsers =  my_Conn.Execute (strSQL)

		rsUsers.Open strSQL, my_Conn


		if (rsUsers.EOF or rsUsers.BOF) then
			Session("/"& glbstrTeamName  & "/UserT")= "NotInDB"
		else
			Session("/"& glbstrTeamName  & "/UserPID") = rsUsers("UserID")
			Session("/"& glbstrTeamName  & "/UserT")= rsUsers("AuthCode")
			Session("/"& glbstrTeamName  & "/UserFN")= rsUsers("FullName")
			Session("/"& glbstrTeamName  & "/UsrTheme") = rsUsers("UsrTheme")
			Session("/"& glbstrTeamName  & "/UserUN") = rsUsers("UsrName")
			Session("/"& glbstrTeamName  & "/UsrAllocation") = rsUsers("UsrAloc")
		end if

		rsUsers.Close
		set rsUsers = nothing

		glbstrTheme = Session("/"& glbstrTeamName  & "/UsrTheme")

		glbstrAloc = Session("/"& glbstrTeamName  & "/UsrAllocation")

The code used by the log-in screen is not successful. The page is a form that has the usual 2 inputs User Name and Password. Once I have validated that both are populated I call a sub that uses the values of User Name and Password

The code for the Sub is... I have incuded some Response.Write lines so that I can see where the Sub fails.
The Response.Write lines in green display on the page. The ones in red do not.

Sub ValidateUser(UsrN, UsrP)

	[green]Response.Write("<font color='#ff0000'>Submit, Beginning Validation</font><br />")[/green]

	Session("/"& glbstrTeamName  & "/EncUsrP") = mmdEnCrYpT(UsrP)

	[green]Response.Write(Session("/"& glbstrTeamName  & "/EncUsrP") & "<br />")[/green]

	strSQL = "SELECT * FROM aTblPIDs WHERE UsrName = '" & UsrN & "';"

	[green]Response.Write(strSQL & "<br />")[/green]

	Set rsUsers =  my_Conn.Execute (strSQL)

	rsUsers.Open strSQL, my_Conn


	If (rsUsers.EOF or rsUsers.BOF) Then

		Session("/"& glbstrTeamName  & "/UserPass") = "Fail"
		Session("/"& glbstrTeamName  & "/UserT")= "NotInDB"
	Else

		Session("/"& glbstrTeamName  & "/UserPass") = "Pass"
		Session("/"& glbstrTeamName  & "/UserPID") = rsUsers("UserID")
		Session("/"& glbstrTeamName  & "/UserT")= rsUsers("AuthCode")
		Session("/"& glbstrTeamName  & "/UserFN")= rsUsers("FullName")
		Session("/"& glbstrTeamName  & "/UsrTheme") = rsUsers("UsrTheme")
		Session("/"& glbstrTeamName  & "/UserPwd") = rsUsers("PWrd")
		Session("/"& glbstrTeamName  & "/UserUN") = rsUsers("UsrName")
		Session("/"& glbstrTeamName  & "/UsrAllocation") = rsUsers("UsrAloc")

	End If


	[red]Response.Write(rsUsers("UserID") & "<br />")
	Response.Write(rsUsers("AuthCode") & "<br />")
	Response.Write(rsUsers("FullName") & "<br />")
	Response.Write(rsUsers("UsrTheme") & "<br />")
	Response.Write(rsUsers("PWrd") & "<br />")
	Response.Write(rsUsers("UsrName") & "<br />")
	Response.Write(rsUsers("UsrAloc") & "<br />")


	Response.Write(Session("/"& glbstrTeamName  & "/UserPID") & "<br />")
	Response.Write(Session("/"& glbstrTeamName  & "/UserT") & "<br />")
	Response.Write(Session("/"& glbstrTeamName  & "/UserFN") & "<br />")
	Response.Write(Session("/"& glbstrTeamName  & "/UsrTheme") & "<br />")
	Response.Write(Session("/"& glbstrTeamName  & "/UserPwd") & "<br />")
	Response.Write(Session("/"& glbstrTeamName  & "/UserUN") & "<br />")
	Response.Write(Session("/"& glbstrTeamName  & "/UsrAllocation") & "<br />")

	Response.Write(Session("/"& glbstrTeamName  & "/UserPass") & "<br />")
	Response.Write(Session("/"& glbstrTeamName  & "/EncUsrP") & "<br />")[/red]



	rsUsers.Close
	set rsUsers = nothing

	glbstrTheme = Session("/"& glbstrTeamName  & "/UsrTheme")

	glbstrAloc = Session("/"& glbstrTeamName  & "/UsrAllocation")

End Sub

The same happens if I use a valid user and passord or not.

Can anyone spot where I have gone wrong?

Thanks
Mych

 

[Chopstik]

I think your issue is with your code that is highlighted in bold red below:

Sub ValidateUser(UsrN, UsrP)

    Response.Write("<font color='#ff0000'>Submit, Beginning Validation</font><br />")

    Session("/"& glbstrTeamName  & "/EncUsrP") = mmdEnCrYpT(UsrP)

    Response.Write(Session("/"& glbstrTeamName  & "/EncUsrP") & "<br />")

    strSQL = "SELECT * FROM aTblPIDs WHERE UsrName = '" & UsrN & "';"

    Response.Write(strSQL & "<br />")

    Set rsUsers =  my_Conn.Execute (strSQL)

    rsUsers.Open strSQL, my_Conn


    If (rsUsers.EOF [B][I][COLOR=red yellow]and[/color][/I][/B] rsUsers.BOF) Then

        Session("/"& glbstrTeamName  & "/UserPass") = "Fail"
        Session("/"& glbstrTeamName  & "/UserT")= "NotInDB"
    Else

        Session("/"& glbstrTeamName  & "/UserPass") = "Pass"
        Session("/"& glbstrTeamName  & "/UserPID") = rsUsers("UserID")
        Session("/"& glbstrTeamName  & "/UserT")= rsUsers("AuthCode")
        Session("/"& glbstrTeamName  & "/UserFN")= rsUsers("FullName")
        Session("/"& glbstrTeamName  & "/UsrTheme") = rsUsers("UsrTheme")
        Session("/"& glbstrTeamName  & "/UserPwd") = rsUsers("PWrd")
        Session("/"& glbstrTeamName  & "/UserUN") = rsUsers("UsrName")
        Session("/"& glbstrTeamName  & "/UsrAllocation") = rsUsers("UsrAloc")

    End If


    Response.Write(rsUsers("UserID") & "<br />")
    Response.Write(rsUsers("AuthCode") & "<br />")
    Response.Write(rsUsers("FullName") & "<br />")
    Response.Write(rsUsers("UsrTheme") & "<br />")
    Response.Write(rsUsers("PWrd") & "<br />")
    Response.Write(rsUsers("UsrName") & "<br />")
    Response.Write(rsUsers("UsrAloc") & "<br />")


    Response.Write(Session("/"& glbstrTeamName  & "/UserPID") & "<br />")
    Response.Write(Session("/"& glbstrTeamName  & "/UserT") & "<br />")
    Response.Write(Session("/"& glbstrTeamName  & "/UserFN") & "<br />")
    Response.Write(Session("/"& glbstrTeamName  & "/UsrTheme") & "<br />")
    Response.Write(Session("/"& glbstrTeamName  & "/UserPwd") & "<br />")
    Response.Write(Session("/"& glbstrTeamName  & "/UserUN") & "<br />")
    Response.Write(Session("/"& glbstrTeamName  & "/UsrAllocation") & "<br />")

    Response.Write(Session("/"& glbstrTeamName  & "/UserPass") & "<br />")
    Response.Write(Session("/"& glbstrTeamName  & "/EncUsrP") & "<br />")



    rsUsers.Close
    set rsUsers = nothing

    glbstrTheme = Session("/"& glbstrTeamName  & "/UsrTheme")

    glbstrAloc = Session("/"& glbstrTeamName  & "/UsrAllocation")

End Sub

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[mych]

Chopstik

You have very sharp eyes... I have been staring at this and pulling my hair out....

Thanks

 

[mych]

Sorry,

Still have the same problem... The line Chopstik pointed to is actually correct. It should be...

If (rsUsers.EOF [red]OR[/red] rsUsers.BOF) Then

This should trigger is no records have been found and the ELSE statement should trigger if a record is found.

What seems to happen at the moment is neither trigger.

Mych

 

[Chopstik]

Actually, no, if you use the OR statement, it will trigger if you are at the first record or the last record in the recordset. When you are at both the first and last record in a recordset is when you have no records.

Did you have an error when you tried my original suggestion?

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill