Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 10 of 10
  1. #1
    New Coder
    Join Date
    Jul 2003
    Location
    Perth WA
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Getting data from an OLEDBDataReader

    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
    When I die I want to go peacefully in my sleep - Just like my Grandfather did............not screaming like the passengers in his car......

  • #2
    Senior Coder
    Join Date
    Jun 2002
    Location
    41 8' 52" N -95 53' 31" W
    Posts
    3,660
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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...

    Last edited by whammy; 11-07-2003 at 04:49 AM.
    Former ASP Forum Moderator - I'm back!

    If you can teach yourself how to learn, you can learn anything. ;)

  • #3
    New Coder
    Join Date
    Jul 2003
    Location
    Perth WA
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    When I die I want to go peacefully in my sleep - Just like my Grandfather did............not screaming like the passengers in his car......

  • #4
    Senior Coder
    Join Date
    Jun 2002
    Location
    41 8' 52" N -95 53' 31" W
    Posts
    3,660
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Last edited by whammy; 11-08-2003 at 04:00 AM.
    Former ASP Forum Moderator - I'm back!

    If you can teach yourself how to learn, you can learn anything. ;)

  • #5
    Regular Coder
    Join Date
    Dec 2002
    Location
    France
    Posts
    522
    Thanks
    0
    Thanked 0 Times in 0 Posts
    '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
    Last edited by angiras; 11-08-2003 at 07:40 AM.

  • #6
    New Coder
    Join Date
    Jul 2003
    Location
    Perth WA
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    When I die I want to go peacefully in my sleep - Just like my Grandfather did............not screaming like the passengers in his car......

  • #7
    Regular Coder
    Join Date
    Dec 2002
    Location
    France
    Posts
    522
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Last edited by angiras; 11-10-2003 at 04:50 AM.

  • #8
    New Coder
    Join Date
    Jul 2003
    Location
    Perth WA
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    When I die I want to go peacefully in my sleep - Just like my Grandfather did............not screaming like the passengers in his car......

  • #9
    Regular Coder
    Join Date
    Dec 2002
    Location
    France
    Posts
    522
    Thanks
    0
    Thanked 0 Times in 0 Posts
    your reader cannot have rows you must tell it before which command running

    dtRead = Comm.ExecuteReader

  • #10
    New Coder
    Join Date
    Jul 2003
    Location
    Perth WA
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    When I die I want to go peacefully in my sleep - Just like my Grandfather did............not screaming like the passengers in his car......


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •