System.InvalidCastException: Specified cast is not valid at .ExecuteScalar

Question

I'm working on an app linked to a local database. What I want, is to show in a form datae from database, but in labels. I created a list of labels, but I get this error at command.Executescalar(), where I try to get the number of rows entered in the dataBase, so the list can create the exact number of rows. Thanks !

int infoCount = (int)command.ExecuteScalar();
var pozitie = 50; //50 pixeli
for (var i = infoCount ; i >= 0; i--)
{
      //creez si adaug un nou label in form
       Label label = new Label();
       label.Text = dataTable.Rows[i][i].ToString();
       label.Location = new Point(pozitie, 150);
       label.AutoSize = true;

       //afisez in form
       //adaug in colectie
       labels.Add(label);

}

L.E:

var query = "SELECT * FROM grupe WHERE Nume='" + nume + "'";
var command = new SqlCeCommand(query, conn);
var dataAdapter = new SqlCeDataAdapter(command);
var dataTable = new DataTable();
dataAdapter.Fill(dataTable);

Answer 1

This query returns not the number of records:

SELECT * FROM grupe WHERE Nume=@nume

Instead it selects all columns, with ExecuteScalar it returns the value of the first row's first column. Instead you want to use:

SELECT COUNT(*) FROM grupe WHERE Nume=@nume

Also use sql-parameters (as shown above) to prevent sql injection.

using(var command = new SqlCeCommand(query, conn))
{
     command.Parameters.Add("@nume", SqlDbType.VarChar).Value = nume;
}

Also note that indices are zero based in C#, so you access the first item in a list or array via coll[0]. The last item is at coll.Count-1 For that reason you should change your for loop to:

for (int i = infoCount - 1 ; i >= 0; i--)
{
    // ...
}

---

But you don't need to determine the row-count with a separate query at all. You are filling a DataTable which has a table.Rows.Count property. So it's easy:

// ...
var dataTable = new DataTable();
dataAdapter.Fill(dataTable);
for(int i = dataTable.Rows.Count - 1; i >= 0; i--)
{
     DataRow row = dataTable.Rows[i];
    // ...
}