C# Retrieve COUNT from SQL Query - What am I doing wrong?

[Todd Jaspers]

Here is what I have:

private int NationalCount()
{
Int32 numRecords = 0;
using (SqlConnection dataConnection = new
SqlConnection(GlobalVars.sqlConnString))
{
SqlCommand dataCommand = new SqlCommand();
dataCommand.CommandText = ("SELECT Count(T1.ReportDate) FROM
from ScoutReportsNFS T1 WHERE (guidPlayerPersonID = '" + strCurrentPlayerID +
"') RETURN COUNT(*)");
try
{
dataConnection.Open();
dataCommand.Connection = dataConnection;
numRecords = (Int32)dataCommand.ExecuteScalar();
}
catch (Exception errLog) {
MessageBox.Show(Convert.ToString(errLog)); }
finally { if (dataConnection.State == ConnectionState.Open)
{ dataConnection.Close(); } }
}
return numRecords;
}

It just gives me an error. I believe all my problems are related
specifically to the query. What the heck am I doing wrong??? Thanks guys...

 

[Alberto Poblacion]

Here is what I have:

private int NationalCount()
{
Int32 numRecords = 0;
using (SqlConnection dataConnection = new
SqlConnection(GlobalVars.sqlConnString))
{
SqlCommand dataCommand = new SqlCommand();
dataCommand.CommandText = ("SELECT Count(T1.ReportDate)
FROM
from ScoutReportsNFS T1 WHERE (guidPlayerPersonID = '" +
strCurrentPlayerID +
"') RETURN COUNT(*)");
try
{
dataConnection.Open();
dataCommand.Connection = dataConnection;
numRecords = (Int32)dataCommand.ExecuteScalar();
}
catch (Exception errLog) {
MessageBox.Show(Convert.ToString(errLog)); }
finally { if (dataConnection.State == ConnectionState.Open)
{ dataConnection.Close(); } }
}
return numRecords;
}

It just gives me an error. I believe all my problems are related
specifically to the query. What the heck am I doing wrong??? Thanks
guys...

Your query is a little strange. You only need to do "Select Count(...) From
....". You don't have to add "Return Count(*)".

 

[Todd Jaspers]

Ok, I figured it out:


Int32 numRecords = 0;
using (SqlConnection dataConnection = new
SqlConnection(GlobalVars.sqlConnString))
{
SqlCommand dataCommand = new SqlCommand();
dataCommand.CommandText = ("SELECT Count(T1.ReportDate) as
numRecords FROM ScoutReportsNFS T1 WHERE (guidPlayerPersonID = '" +
strCurrentPlayerID + "')");
try
{
dataConnection.Open();
dataCommand.Connection = dataConnection;
SqlDataReader dataReader = dataCommand.ExecuteReader();
while (dataReader.Read())
{
if (dataReader["numRecords"].ToString() != null) {
numRecords = Convert.ToInt32((dataReader["numRecords"].ToString())); }
}
//numRecords = (Int32)dataCommand.ExecuteScalar();
}
catch (Exception errLog) {
MessageBox.Show(Convert.ToString(errLog)); }
finally { if (dataConnection.State == ConnectionState.Open)
{ dataConnection.Close(); } }
}
return numRecords;

 

[Jeroen Mostert]

Here is what I have:

private int NationalCount()
{
Int32 numRecords = 0;
using (SqlConnection dataConnection = new
SqlConnection(GlobalVars.sqlConnString))
{
SqlCommand dataCommand = new SqlCommand();
dataCommand.CommandText = ("SELECT Count(T1.ReportDate) FROM
from ScoutReportsNFS T1 WHERE (guidPlayerPersonID = '" + strCurrentPlayerID +
"') RETURN COUNT(*)");
try
{
dataConnection.Open();
dataCommand.Connection = dataConnection;
numRecords = (Int32)dataCommand.ExecuteScalar();
}
catch (Exception errLog) {
MessageBox.Show(Convert.ToString(errLog)); }
finally { if (dataConnection.State == ConnectionState.Open)
{ dataConnection.Close(); } }
}
return numRecords;
}

It just gives me an error. I believe all my problems are related
specifically to the query. What the heck am I doing wrong??? Thanks guys...

Leave out "RETURN COUNT(*)". RETURN statements are only supported from
stored procedures and user-defined functions. .ExecuteScalar() will take the
first column of the first row of the first result set of your query and
return that as the result, so no further action on your part is necessary.
Your query also contains a syntax error in the form of a duplicate "from".

Also, you do not need the "finally" block, as closing the connection will be
taken care of when the using block exits. You *should* wrap the SqlCommand
in a using, though, and you *should* use strongly-typed parameters, not
textual substitution, to pass values. So just make it

private int NationalCount() {
using (SqlConnection dataConnection = new
SqlConnection(GlobalVars.sqlConnString)) {
dataConnection.Open();
using (SqlCommand dataCommand = dataConnection.CreateCommand()) {
dataCommand.CommandText = "SELECT Count(T1.ReportDate) FROM
ScoutReportsNFS T1 WHERE guidPlayerPersonID = @playerID";
dataCommand.Parameters.AddWithValue("@playerID", new
Guid(strCurrentPlayerID));
return (int) dataCommand.ExecuteScalar();
}
}
}

There's no point to catching an exception here to show in a message box;
this isn't dealing with the problem. If you need this, put it in a function
on a higher level.

 

[Alun Harford]

Here is what I have:

private int NationalCount()
{
Int32 numRecords = 0;
using (SqlConnection dataConnection = new
SqlConnection(GlobalVars.sqlConnString))
{
SqlCommand dataCommand = new SqlCommand();
dataCommand.CommandText = ("SELECT Count(T1.ReportDate) FROM
from ScoutReportsNFS T1 WHERE (guidPlayerPersonID = '" + strCurrentPlayerID +
"') RETURN COUNT(*)");
try
{
dataConnection.Open();
dataCommand.Connection = dataConnection;
numRecords = (Int32)dataCommand.ExecuteScalar();
}
catch (Exception errLog) {
MessageBox.Show(Convert.ToString(errLog)); }
finally { if (dataConnection.State == ConnectionState.Open)
{ dataConnection.Close(); } }
}
return numRecords;
}

It just gives me an error. I believe all my problems are related
specifically to the query. What the heck am I doing wrong??? Thanks guys...

In addition to what has been said here, I'd like to add that my
strCurrentPlayerID is "foo');DROP TABLE ScoutReportsNFS;--"

Alun Harford