CODEWITHSUNDEEP
c#sql
Firesky
Firesky

Reputation: 17

Selecting and retrieving multiple cells in different rows

I'm trying to retrieve multiple cells in different rows where the correct owner exists, but I'm only being able to retrieve the first match and it stops there, I've tried using it with a for, but I don't think .ExecuteScalar() is the way to do this. Maybe I'm just stupid and doing it completely wrong.

Code:

checkPlayerName = API.getPlayerName(player);
string checkOwnedCars = "SELECT COUNT(*) FROM [carOwners] WHERE Owner='" + checkPlayerName + "'";
            con.Open();
            SqlCommand checkCarsCount = new SqlCommand(checkOwnedCars, con);
            int carsCountToVar = Convert.ToInt32(checkCarsCount.ExecuteScalar());
            con.Close();
            for (int i = 0; i < carsCountToVar; i++)
            {
                string displayCars = "SELECT LP FROM [carOwners] WHERE Owner='" + checkPlayerName + "'";
                con.Open();
                SqlCommand displayCarsCMD = new SqlCommand(displayCars, con);
                string displayCarsToVar = displayCarsCMD.ExecuteReader().ToString();
                API.sendChatMessageToPlayer(player, "Owned Vehicle: " + displayCarsToVar.ToString());
                con.Close();
            }

Table

For example, LP on 2nd and 3rd row are the ones that I want to store since both belong to the same owner, yet only first cell data (1337) is displaying.

Upvotes: 0

Views: 96

Answers (1)

Ehsan Ullah Nazir
Ehsan Ullah Nazir

Reputation: 1917

You are not iterating the results you are getting from query.

Plus always use Parameterized queries to prevent SQL Injection Attacks 

SqlCommand command = new SqlCommand("SELECT LP FROM [carOwners] WHERE Owner=@checkPlayerName", con);
command.Parameters.AddWithValue("@checkPlayerName",checkPlayerName);

using (SqlDataReader reader = command.ExecuteReader())
{
  while (reader.Read())
  {
     Console.WriteLine(String.Format("{0}",reader["id"]));
     //API.sendChatMessageToPlayer(player, "Owned Vehicle: " + reader["id"].ToString());
   }
}

conn.Close();

Upvotes: 2

Related Questions