Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Oct 2002
    Location
    Perth, Western Australia, Australia
    Posts
    13
    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:Inetpubwwwr"& _
    "ootDatabasesStaff 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.CommandBehavio r.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

  2. #2
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting data from an OLEDBDataReader

    Ahhhh - finally a good ASP.NET question!! (We haven't had many ASP.NET questions here...yet)

    You're using a DataReader to read the data. The problem is that a Data Reader relies on an open database connection - which you're closing in the ExecuteReader method's argument (CommandBehavior.CloseConnection).

    You can approach this one of two ways....

    1) Close the connection AFTER doing your business with the Data Reader (i.e. after returning the Data Reader at the end of the function). This is probably your best bet.
    2) Use a Data Adapter and Dataset instead of Data Reader. This will allow you to work with the data while being disconnected from the database... Probably an overkill here, although I use it all the time for other things.

    Post back if neither of these work for you. Hope this helps!

  3. #3
    New Lounger
    Join Date
    Oct 2002
    Location
    Perth, Western Australia, Australia
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting data from an OLEDBDataReader

    Thanks you are a legend it is now going wonderfully!!!

    Thanks Again

    Vicki

Posting Permissions

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