dataReader just returning 1st record in DB

[Guest]

H

I'm building an app which has a public area and private area with a SQL Server 2000 DB

The Log in page code is below, and it is maybe inefficient, I don't care - it just has to work

In the Log-In.aspx file I am

1) check to see if someone entered a valid email/password. If so, continue to get the detail
2) pull the title, firstname, surname, contact type and email of the contact from the D
3) initalise a authenitcation ticket called 'stateEmail' (so can roam the directory with a web.config file within)
4a) get the contact informaiton from the D
4b) merge the 'Title' and 'Surname' columns to produce a stateName e.g. 'Dr Smith
4c) initalise 2 pieces of cookieless sessions called 'stateName' and 'contact Type'

The form works and does most, but when re-directed to the secure, logged-In area directory the
- authenitacion Email works correctly (stateEmail) by bringing up the correct email address of the use
- BUT the stateName only returns whatever is the FIRST record in my DB i.e. not pulling the correct title/surnam

*** CODE ***

Dim stateName As Strin
Dim stateEmail As Strin
Dim stateDoctorType As Strin

Private Sub butEnter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles butEnter.Clic

'checking if password exists, if so, return Title, Surnam
Dim strConn, strSQL As Strin
strConn = "xxxxxxx
Dim cn As New OleDbConnection(strConn
cn.Open(
Dim cmd As OleDbCommand = cn.CreateCommand(
cmd.CommandText = "SELECT COUNT (*) FROM DoctorDetails WHERE Email = '" & TxtUserName.Text & "' AND Password = '" & TxtPassword.Text & "'

'if username or password not in, return comment on pag
Dim validEmailPass As Boolean = CInt(cmd.ExecuteScalar()
If validEmailPass = 0 The
lblMembers.Text = "Sorry, Email or Password not recognised.</br>Please try again
LblForgot.Text = "
TxtUserName.Text = "
TxtPassword.Text = "
cn.Close(
Els
'run a query which pulls the correct username informatio
cn.Close(
cn.Open()
Dim cmd2 As New OleDbCommand(
cmd2 = cn.CreateCommand(
cmd2.CommandText = "SELECT MemberType, Title, Surname, Email FROM DoctorDetails WHERE Email = '" & TxtUserName.Text & "' AND Password = '" & TxtPassword.Text & "'

Dim rdr2 As OleDbDataReader = cmd2.ExecuteReader(
Dim stateTitle As Strin
Dim stateSurname As Strin

'binding the information
While rdr2.Read(
stateDoctorType = rdr2("MemberType"
stateTitle = rdr2("Title"
stateSurname = rdr2("Surname"
stateEmail = rdr2("Email"
End Whil
stateName = stateTitle & " " & stateSurnam

rdr2.Close(
cn.Close(

'initialise viewstat
Session("doctorSession") = stateNam
Session("doctorTypeSession") = stateDoctorTyp

'initalise authenticatio
FormsAuthentication.SetAuthCookie(stateEmail, False
Response.Redirect("../DoctorsMemberArea/WelcomeDoctor.aspx"

End I

End Su

*** Appreciate your help. Thanks in advance.

 

[Miha Markic [MVP C#]]

Hi,

While rdr2.Read()
stateDoctorType = rdr2("MemberType")
stateTitle = rdr2("Title")
stateSurname = rdr2("Surname")
stateEmail = rdr2("Email")
End While

You seem to loop and set the same variables over and over again...

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

Hi

I'm building an app which has a public area and private area with a SQL Server 2000 DB.

The Log in page code is below, and it is maybe inefficient, I don't care - it just has to work!

In the Log-In.aspx file I am :

1) check to see if someone entered a valid email/password. If so, continue to get the details
2) pull the title, firstname, surname, contact type and email of the contact from the DB
3) initalise a authenitcation ticket called 'stateEmail' (so can roam the

directory with a web.config file within)

4a) get the contact informaiton from the DB
4b) merge the 'Title' and 'Surname' columns to produce a stateName e.g. 'Dr Smith'
4c) initalise 2 pieces of cookieless sessions called 'stateName' and 'contact Type'


The form works and does most, but when re-directed to the secure, logged-In area directory the:
- authenitacion Email works correctly (stateEmail) by bringing up the

correct email address of the user

- BUT the stateName only returns whatever is the FIRST record in my DB

i.e. not pulling the correct title/surname

*** CODE ****

Dim stateName As String
Dim stateEmail As String
Dim stateDoctorType As String

Private Sub butEnter_Click(ByVal sender As System.Object, ByVal e As

System.EventArgs) Handles butEnter.Click

'checking if password exists, if so, return Title, Surname
Dim strConn, strSQL As String
strConn = "xxxxxxx"
Dim cn As New OleDbConnection(strConn)
cn.Open()
Dim cmd As OleDbCommand = cn.CreateCommand()
cmd.CommandText = "SELECT COUNT (*) FROM DoctorDetails WHERE Email

= '" & TxtUserName.Text & "' AND Password = '" & TxtPassword.Text & "'"

'if username or password not in, return comment on page
Dim validEmailPass As Boolean = CInt(cmd.ExecuteScalar())
If validEmailPass = 0 Then
lblMembers.Text = "Sorry, Email or Password not

LblForgot.Text = ""
TxtUserName.Text = ""
TxtPassword.Text = ""
cn.Close()
Else
'run a query which pulls the correct username information
cn.Close()
cn.Open()
Dim cmd2 As New OleDbCommand()
cmd2 = cn.CreateCommand()
cmd2.CommandText = "SELECT MemberType, Title, Surname, Email

FROM DoctorDetails WHERE Email = '" & TxtUserName.Text & "' AND Password =
'" & TxtPassword.Text & "'"

 

[Guest]

Hi

Why not concatenate in sql? like
cmd2.CommandText = "SELECT MemberType, Title, Surname, Email, Title + ' ' + Surname as Name FROM DoctorDetails WHERE Email = '" & TxtUserName.Text & "' AND Password = '" & TxtPassword.Text & "'

Bin Song, MC

 

[Guest]

Thanks Bi

You've taught me some SQL syntax. I wasn't sure the addition sign could work with non-numbers. But tried what you suggested and it works just fine. Thanks

Will be returning to this community alot!