CODEWITHSUNDEEP
c#mysqlsqlvisual-studio
Waqas Ovais
Waqas Ovais

Reputation: 13

How to get multiple rows from access database

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++;
}

Upvotes: 0

Views: 1911

Answers (2)

GarethD
GarethD

Reputation: 69759

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();
}

Upvotes: 3

MUG4N
MUG4N

Reputation: 19717

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 { }

}

Upvotes: 0

Related Questions