...

View Full Version : Getting .NET data from an OLEDBDataReader



vicki_b
11-07-2003, 03:30 AM
Hi All,

On my login screen I have the usual username and password fields with a login button. On the click event of the button I am running a query that queries the user table in my database to ascertain whether the user is logged in or not. This all works fine however what I want to do now is create a cookie (which I can do) and save one of the column values into the cookie. This is where I have come unstuck! The data reader should return 4 columns - user id, password, Staff Id and User Level. I need to save the Staff ID value and the User Level value from the data reader into variables which I can then use to add data to the cookie.

Below is the code that I currently have however when I run it I get an error message stating that "no data exists for the row/column." Now considering that I have this nested within an If statement that checks if there is a record to begin with I can't understand why I can't access the columns. Any help would be greatly appreciated.

Sub LoginBtn_Click(Sender As Object, E As EventArgs)

call varVarification(UserName.Text, UserPass.text)



End Sub


Function varVarification(ByVal userId As String, ByVal password As String) As System.Data.IDataReader
Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data Source=C:\Inetpub\wwwr"& _
"oot\Databases\Staff Details.mdb"
Dim dbConnection As System.Data.IDbConnection = New System.Data.OleDb.OleDbConnection(connectionString)

Dim queryString As String = "SELECT [tlog_LogonInformation].[User Id], [tlog_LogonInformation].[Password], [tl"& _
"og_LogonInformation].[Staff Id], [tlog_LogonInformation].[User Level] FROM [tlog"& _
"_LogonInformation] WHERE (([tlog_LogonInformation].[User Id] = @UserId) AND ([tl"& _
"og_LogonInformation].[Password] = @Password))"
Dim dbCommand As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Dim dbParam_userId As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
dbParam_userId.ParameterName = "@UserId"
dbParam_userId.Value = userId
dbParam_userId.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_userId)
Dim dbParam_password As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
dbParam_password.ParameterName = "@Password"
dbParam_password.Value = password
dbParam_password.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_password)

dbConnection.Open
Dim dataReader1 As System.Data.OleDb.OleDbDataReader = dbCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

If dataReader1.hasrows then
Dim varStaffId as Integer
varStaffID = dataReader1.GetString(3)
Msg.Text = varStaffID

dim CSIWA as New System.Web.HttpCookie("CSIWA Website")
CSIWA.Expires = Now.AddDays(365)
Response.Cookies.Add(CSIWA)

CSIWA.Values.Add("UserID",UserId)
Else
Msg.Text = "Invalid Credentials: Please try again"
End If

Return dataReader1
End Function

Thanks

Vicki

whammy
11-07-2003, 05:34 AM
Ok, I've read your post and I'm somewhat confused.

Can you explain a bit more about your logic flow, and when you are hitting the server? BTW Sorry if I seem like a moron but I really despise any kind of VB code, and that doesn't even look similar to anything I've coded in C# using OOP...

:confused:

vicki_b
11-07-2003, 06:04 AM
What I am attempting to do is check the users username and password against the records in a database which I have successfully managed to do by creating a record set. Once I have created this I then check to see if anything has been returned and if something has been returned then obviously they have entered a correct username and password. This all works fine.

In the query that I am running to create the record set I ask for the username, password, staff id and access level fields. What I am now attempting to do is get the data in the staff id and access level fields so that I can save them into a cookie which I can then use on other pages within my site.

My logic is telling me that I need to access these field results and store them in 2 variables. This is fine however using the code that I found on the asp.net web site to access the columns containing the data I get an error on my machine that says that there is no data in the data set. This would not bother me except for the fact that I have placed this code within an IF statement that checks if a record has been returned (I have tested this If statement and it would appear to work OK) Thus the only thing that I can come up with is the fact that I am not referencing the column correctly or that I am using the wrong method to access the data.

At the end of the day this may not be the best way to do what I am wanting to do so I am open to any suggestions. At the end of the day all I really want to do is check the users username and password agains a table within an access database and then to save the user id and access level fields so that I can use them on other pages within my web site.

I hope that this makes sense. If it doesn't make any sense or if I haven't answered your question please let me know.

Thanks

Vicki

whammy
11-08-2003, 04:56 AM
Wow... if noone else answers this post, and you're still stuck let me know.

I'm going to have a couple of colleagues who use .NET stuff daily look at this code and see what they think. :|

Unfortunately I don't have much time lately to experiment as I'd like with C# or .NET in general as I'm too busy putting out fires thanks to other programmers having been rushed in classic ASP. :(

Guess who get stuck fixing it!

In the meantime you might also want to post on:

http://www.asp.net
http://www.experts-exchange.com

Someone there might be able to help you with a quick solution.

angiras
11-08-2003, 08:36 AM
'you could simplify a lot your code

Const LOG_QUERY As String = "Select * from any where pw ={0} and login={1}"
Dim cmd As OleDbCommand
Dim reader As OleDbDataReader
Dim con As New OleDbConnection(connectionstring)


cmd = New OleDbCommand(con, String.Format(LOG_QUERY, tbox.user, tbox.Login))

If Reader.HasRows Then
While Reader.Read
'get infos
End While
End If

vicki_b
11-10-2003, 03:12 AM
Ok this is the code that I now have (I had to make a few changes to get past some error messages but this last error I can't seem to get around!!)

Sub LoginBtn_Click(Sender As Object, E As EventArgs)

call varVarification(UserName.Text, UserPass.text)



End Sub


Function varVarification(ByVal userId As String, ByVal password As String) As System.Data.IDataReader
Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data Source=C:\Inetpub\wwwr"& _
"oot\Databases\Staff Details.mdb"
const Log_Query as string = "SELECT [tlog_LogonInformation].[User Id], [tlog_LogonInformation].[Password], [tl"& _
"og_LogonInformation].[Staff Id], [tlog_LogonInformation].[User Level] FROM [tlog"& _
"_LogonInformation] WHERE (([tlog_LogonInformation].[User Id] = @userId) AND ([tl"& _
"og_LogonInformation].[Password] = @Password))"
Dim Reader as System.Data.OleDb.OleDbDataReader
Dim Con as new System.Data.OleDb.OleDbConnection(connectionString)
Dim cmd As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand
cmd.CommandText = Log_Query
cmd.Connection = Con

If Reader.hasrows then
While Reader.Read
Dim varStaffId as Integer
varStaffID = Reader.GetString(3)
Msg.Text = varStaffID

dim CSIWA as New System.Web.HttpCookie("CSIWA Website")
CSIWA.Expires = Now.AddDays(365)
Response.Cookies.Add(CSIWA)

CSIWA.Values.Add("UserID",UserId)
end while
Else
Msg.Text = "Invalid Credentials: Please try again"
End If


End Function

However when I run it I am getting an error that states that "Object reference not set to an instance of an object"

Have I miss understood what I was supposed to do with your code or have I missed doing something??

Any help would be greatly appreciated!!

Thanks

Vicki

angiras
11-10-2003, 05:47 AM
you don't need the call keyword

and put your cookie method out of the while block

your reader doesn't read any cmd As System.Data.IDbCommand

you must use

reader = cmd.execute..........


try to put many try catch blocks every where to know where you error comes from



try
const Log_Query as string = "SELECT [tlog_LogonInformation].[User Id], [tlog_LogonInformation].[Password], [tl"& _
"og_LogonInformation].[Staff Id], [tlog_LogonInformation].[User Level] FROM [tlog"& _
"_LogonInformation] WHERE (([tlog_LogonInformation].[User Id] = @userId) AND ([tl"& _
"og_LogonInformation].[Password] = @Password))"
catch

Throw new exception(" 1 !")

end try

Dim Reader as System.Data.OleDb.OleDbDataReader
Dim Con as new System.Data.OleDb.OleDbConnection(connectionString)
Dim cmd As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand
cmd.CommandText = Log_Query
cmd.Connection = Con

Dim varStaffId as Integer

If Reader.hasrows then
While Reader.Read
try
varStaffID = cint(Reader.GetString(3))

catch

Throw new exception(" 2 !")

end try

end while
Else
Msg.Text = "Invalid Credentials: Please try again"
End If

Msg.Text = varStaffID

try
dim CSIWA as New System.Web.HttpCookie("CSIWA Website")
CSIWA.Expires = Now.AddDays(365)
Response.Cookies.Add(CSIWA)
Msg.Text = varStaffID

dim CSIWA as New System.Web.HttpCookie("CSIWA Website")
CSIWA.Expires = Now.AddDays(365)
Response.Cookies.Add(CSIWA)

CSIWA.Values.Add("UserID",UserId)


catch

Throw new exception(" 3 !")

end try

End Function

vicki_b
11-10-2003, 07:18 AM
I am still getting the same error message "Object reference not set to an instance of an object" It is stopping on the "If Reader.HasRows Then" line. It loads up OK but when I stick in a username and password it comes up with this error.

Thanks for all your help it is greatly appreciated!!!

Cheers

Vicki

angiras
11-10-2003, 07:26 AM
your reader cannot have rows you must tell it before which command running

dtRead = Comm.ExecuteReader

vicki_b
11-10-2003, 08:09 AM
Don't worry, It works if I have the data field in the database defined as the correct thing!!! What can I say some days are blonder than others!!!

Thanks for all the help though!!

Vicki:thumbsup:



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum