locked
oleDBdataReader Question RRS feed

  • Question

  • User-1618168713 posted

    Hello All,

    Can some explain to me how to get the OleDbDataReader to read every entry in my database rather than what it is currently doing where it is just returning one value?

    Below is my code

       static private string GetConnectionString()
            {
                return "Provider=Microsoft.Jet.OleDb.4.0;Data Source=|DataDirectory|Users.mdb";
            }
    
            protected void Button1_Click(object sender, EventArgs e)
            {
                string UserIDreturnValue = "";
                string userID = "";
                string newUserID = UserID.Text;
    
                if (UserID != null)
                {
                    UserIDreturnValue = getUserID(userID);
                }
                
                string url = "WebResponsePage.aspx?";
                url += "Item=" + newUserID;
                Response.Redirect(url);
    
            }
    
            public string getUserID(string UserIDValue)
            {
               
                string test = "";
                string connectionString = GetConnectionString();
                string queryString =
                "SELECT * FROM tlogLoginAttempt WHERE strUserName = '" + UserID.Text + "'"; ;
                using (OleDbConnection connection =
                           new OleDbConnection(connectionString))
                {
                    OleDbCommand command = connection.CreateCommand();
                    command.CommandText = queryString;
                    try
                    {
                        connection.Open();
    
                        OleDbDataReader reader = command.ExecuteReader();
    
                        
                          while (reader.Read())
                            {
                                test = reader[0].ToString(); 
                                UserIDValue = test;
                            }
                      
                        reader.Close();
                    }
                    catch (Exception ex)
                    {
    
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
                return test;
            }


    I have tried inserting a "i" counter in the while statement to loop to the next record but unfortunately no luck.

    Any suggestions would be great

    Thanks.


    Monday, January 31, 2011 5:29 AM

Answers

  • User-821857111 posted

    You need to reference the other fields in the DataReader. They can be referenced through their index (which you have started to do with reader[0]) so the next field will be reader[1], then reader[2] etc, or their name, which corresponds with the database field names:

    reader["ID"]
    reader[FirstName"] etc.

    while(reader.Read()){
        test += reader["ID"] + ", " + reader["FirstName"] + //etc
    
    


    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 31, 2011 11:13 AM

All replies

  • User-821857111 posted

    Are you trying to get all the values written out to the test variable? If so, you need to append the values rather than over write the value on every iteration:

    test += reader[0].ToString();

    You obviously need to add some kind of separator as well perhaps a comma?

    test += reader[0].ToString() + ", ";

    Monday, January 31, 2011 9:47 AM
  • User-1618168713 posted

    Hello, thanks for replying yes I did want to write the contents of the DB to the test variable.

    Using the method you suggest doesn't quite work correctly, for some reason it writes the first value in the database twice and then comes out of the reader??

    Not too sure why it does that?

    Thanks

    Monday, January 31, 2011 10:32 AM
  • User-1618168713 posted

    No wait a minute, I'm talking rubbish! It does work, however it is reading all the data down in the table instead of across.

    So for example it is returning the value 11596 and 11598 which are the two record matches that should be returned, but the rest of the data from those record numbers arent displayed? any thoughts?

    Monday, January 31, 2011 10:37 AM
  • User-821857111 posted

    You need to reference the other fields in the DataReader. They can be referenced through their index (which you have started to do with reader[0]) so the next field will be reader[1], then reader[2] etc, or their name, which corresponds with the database field names:

    reader["ID"]
    reader[FirstName"] etc.

    while(reader.Read()){
        test += reader["ID"] + ", " + reader["FirstName"] + //etc
    
    


    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 31, 2011 11:13 AM
  • User-1618168713 posted

    Perfect! that makes a lot of sense! thank you very much for taking the time to explain that :-)

    Thanks

    Monday, January 31, 2011 11:22 AM
  • User-821857111 posted

    No problems. However, you would have found it a lot easier to add a GridView to your page, and then simply bound the Reader to it:

    OleDbDataReader reader = command.ExecuteReader();
    GridView1.DataSource = reader;
    GridView1.DataBind();


    But at least you know how to "walk" a DataReader now ;o)



    Monday, January 31, 2011 11:41 AM
  • User-1618168713 posted

    Dude! that is basically what I want to do!! haha now you're just showing off lol!

    Genius! thanks.

    Monday, January 31, 2011 11:53 AM