oleDBdataReader Question

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.

Answer 1

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() + ", ";

Answer 2

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

Answer 3

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?

Answer 4

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

Answer 5

User-1618168713 posted

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

Thanks

Answer 6

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)

Answer 7

User-1618168713 posted

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

Genius! thanks.