How to use DataReader to retrive data from the database

[Goran]

I'm a student and I've just started to learn Visual Studio.NET. I have
a project to make a web application.
I have a problem to connect to the database and retriving the PassType
of the tblEmployee.

First I create Bussines Object user.vb and I declare in it:

Public Class EmployeeDetails
Public EmpIdNo As Integer
Public Name As String
Public Pass As String
Public PassType As String
Public Mobile As String
Public Email As String
Public Jobposition As String
Public Section As String
Public Group As String
Public Other As String

End Class

After I need to have a function for getting the employee details that
are already in the Database.

Public Class Employee
Private objConn As New OleDbConnection("Provider=SQLOLEDB.1;" &
_
"Password=test;Persist Security Info=True;User ID=test;" & _
"Initial Catalog=CC_3;Data Source=05PCSTUDENT1")

Public Function GetEmpDetails(ByVal EmpIDNo As Integer) As
EmployeeDetails

Dim objCmd As New OleDbCommand( _
"SELECT * FROM tblEmployee" & _
"WHERE tblEmployee.Name = @Name AND tblEmployee.Pass =
@Pass", objConn)

Dim objReader As OleDbDataReader
Try
objConn.Open()
objReader = objCmd.ExecuteReader
Catch e As OleDbException
Throw e
End Try

Dim objDetails As New EmployeeDetails
While objReader.Read
With objDetails
.EmpIdNo = objReader.GetFloat(0)
.Name = objReader.GetString(1)
.Pass = objReader.GetString(2)
.PassType = objReader.GetString(3)
.Mobile = objReader.GetString(4)
.Email = objReader.GetString(5)
.Jobposition = objReader.GetString(6)
.Section = objReader.GetString(7)
.Group = objReader.GetString(8)
.Other = objReader.GetString(9)
End With
End While
objReader.Close()

Return objDetails

End Function
end class

So from here I need the PassType so that on login I can compare the
Name, Password and Password Type.
I have a login.aspx page for this application. What should I write in
it so that I can retrive the PassType information from the DataBase
that I can after compare and eventualy to login.

Probably I have lots of mistakes but I'm a starter so I don't yet know
the whole syntax.

Thanx,
Goran

 

[Mark Rae]

Private objConn As New OleDbConnection("Provider=SQLOLEDB.1;" &

Firstly, why are you using OleDb to connect to SQL Server instead of the
native .NET data provider?

Dim objCmd As New OleDbCommand( _
"SELECT * FROM tblEmployee" & _
"WHERE tblEmployee.Name = @Name AND tblEmployee.Pass =
@Pass", objConn)

Secondly, try to get into the habit of using stored procedures instead of
dynamic SQL. In this case, are "@Name" and "@Pass" VB.NET variables? If not,
I can't see how this SQL is ever going to work...

Is your DataReader object actually being populated with data?

So from here I need the PassType so that on login I can compare the
Name, Password and Password Type.
I have a login.aspx page for this application. What should I write in
it so that I can retrive the PassType information from the DataBase
that I can after compare and eventualy to login.

Well, you could store the PassType in a Session variable; you could pass it
to the login.aspx page as a QueryString variable, or you could fetch it out
of the database in the Page_Load event of the login.aspx page.

 

[Goran]

OK, I've made a stored procedure for getting all the Employee
information.
But still I don't know what to write in login.aspx to retrive the
PassType value with the DataReader and then to compare it?
I have declared a variable pType as string. I need to store the
PassType value in it so that I can compare it. What should I write?
Can you please write me some example?

Thank you for the previous reply.