How to get multiple rows from access database

Question

I am trying to store each row of a access database, based on column Veh_ID. The found data may or may not be based on multiple rows. The code I am currently using can copy single row but if there are multiple results I can only get the first result. Can anyone please help me on this? I am noob when it comes to database. I tried to search Google but no one seems to be needing what I need. Here's the code I'm using:

string cmd1 = "SELECT * FROM Veh_checkup WHERE Veh_ID = " + veh_idd + "";
OleDbCommand cmd = new OleDbCommand(cmd1, con);            
OleDbDataReader read = cmd.ExecuteReader();
read.Read();
veh_id=null;

int i=0;

foreach (var a in read)
{
    try
    {
        veh_id = veh_id + " " + read[i].ToString();
    }
    catch { }
    i++;
}

score 2 · Accepted Answer · edited May 23 '17 at 11:44

There are a few things I would point out, some specific to your question, some not:

USE PARAMETERISED QUERIES
Use OleDbDataReader.Read() to move to the next record.
Use a StringBuilder to concatenate strings in a loop, using string = string + "something" will create a new string on the heap with each iteration
Use using blocks on Disposable objects
catch { } is not good practice. You will never know an error occurred. At the very least you should log the error somewhere so you know you need to fix something.
OleDbDataReader[i] will get the data from column i for the current record being read, not the data from row i
Don't use SELECT * in production code, especially if you are only using 1 column. It is unnecessary data retrieval from the database and also unnecessary network traffic.
USE PARAMETERISED QUERIES

Okay, I know I included using parameterised queries twice, but that is how strongly I feel about it!

With the above changes made, your full code will become something like:

static string GetStringData(string vehID)
{
    StringBuilder builder = new StringBuilder();
    string cmd1 = "SELECT Column1 FROM Veh_checkup WHERE Veh_ID = @VehID";
    using (OleDbConnection con = new OleDbConnection("YourConnectionString"))
    using (OleDbCommand cmd = new OleDbCommand(cmd1, con))
    {
        con.Open();
        cmd.Parameters.AddWithValue("@VehID", vehID);

        using (OleDbDataReader reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                builder.Append(" " + reader.GetString(0));
            }
        }
    }
    return builder.ToString();
}

MUG4N · Answer 2 · 2014-04-17T16:27:14.497

0

You are using the datareader in a wrong way. Instead of calling it once like you do, you have to call the datareader in a while loop like this:

while(theDataReader.Read())
{
    // do your stuff in a loop now
}

So using this approach in your code would look something like this:

string cmd1 = "SELECT * FROM Veh_checkup WHERE Veh_ID = " + veh_idd + "";
OleDbCommand cmd = new OleDbCommand(cmd1, con);            
OleDbDataReader read = cmd.ExecuteReader();
veh_id=null;

con.Open();
while(read.Read()) //your reader
{
    try
    {
        veh_id = veh_id + " " + read[i].ToString();
    }
    catch { }

}

edited Apr 17 '14 at 16:27

answered Apr 17 '14 at 16:05

MUG4N

19,377
11
56
83

still no luck. it only returns single row. could you please explain what "theDataReader" variable is for here? I used "read" instead of it (in while loop) – Waqas Ovais Apr 17 '14 at 16:18
well your statement suggests that you are only requesting one datarow. That is the datarow which id is matching the Veh_idd. – MUG4N Apr 17 '14 at 16:19
actually i have multiple results that correspond to same "veh_id" so i need to get them all. can you please suggest how to request multiple rows? – Waqas Ovais Apr 17 '14 at 16:23
That is the code to fetch multiple rows. Check this example for a full example: http://www.akadia.com/services/dotnet_data_reader.html – MUG4N Apr 17 '14 at 16:24
if this or another answer was helpful you should consider to mark it as an answer or post your own answer instead – MUG4N Apr 23 '14 at 15:54

How to get multiple rows from access database

2 Answers2