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

Given that the error is on the first line, the rest of the lines are irrelevant... but the code *before* that line - which shows the query - would be *very* relevant. Additionally, you might want to temporarily assign the result to a variable of type `object` and cast *that*, so you can see what the result type actually is... — Jon Skeet, Aug 27 '15 at 10:23

Tim Schmelter · Accepted Answer · 2015-08-27T10:32:42.203

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];
    // ...
}

@user3348368: i have edited my answer, use `var i = infoCount - 1` instead of `var i = infoCount`. But you dont need this query at all, the datatable knows it's size already. — Tim Schmelter, Aug 27 '15 at 10:33
Works, but it's not showing any label or text, or something like that. — Ezekiel, Aug 27 '15 at 10:55

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

1 Answers1