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

[=?Utf-8?B?VG9kZCBKYXNwZXJz?=]

Here is what I have:

private int NationalCount()
{
Int32 numRecords = 0;
using (SqlConnection dataConnection = new
SqlConnection(G lobalVars.sqlCo nnString))
{
SqlCommand dataCommand = new SqlCommand();
dataCommand.Com mandText = ("SELECT Count(T1.Report Date) FROM
from ScoutReportsNFS T1 WHERE (guidPlayerPers onID = '" + strCurrentPlaye rID +
"') RETURN COUNT(*)");
try
{
dataConnection. Open();
dataCommand.Con nection = dataConnection;
numRecords = (Int32)dataComm and.ExecuteScal ar();
}
catch (Exception errLog) {
MessageBox.Show (Convert.ToStri ng(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]

"Todd Jaspers" <To*********@di scussions.micro soft.comwrote in message
news:CA******** *************** ***********@mic rosoft.com...

Here is what I have:

private int NationalCount()
{
Int32 numRecords = 0;
using (SqlConnection dataConnection = new
SqlConnection(G lobalVars.sqlCo nnString))
{
SqlCommand dataCommand = new SqlCommand();
dataCommand.Com mandText = ("SELECT Count(T1.Report Date)
FROM
from ScoutReportsNFS T1 WHERE (guidPlayerPers onID = '" +
strCurrentPlaye rID +
"') RETURN COUNT(*)");
try
{
dataConnection. Open();
dataCommand.Con nection = dataConnection;
numRecords = (Int32)dataComm and.ExecuteScal ar();
}
catch (Exception errLog) {
MessageBox.Show (Convert.ToStri ng(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(*)".

[=?Utf-8?B?VG9kZCBKYXNwZXJz?=]

Ok, I figured it out:
Int32 numRecords = 0;
using (SqlConnection dataConnection = new
SqlConnection(G lobalVars.sqlCo nnString))
{
SqlCommand dataCommand = new SqlCommand();
dataCommand.Com mandText = ("SELECT Count(T1.Report Date) as
numRecords FROM ScoutReportsNFS T1 WHERE (guidPlayerPers onID = '" +
strCurrentPlaye rID + "')");
try
{
dataConnection. Open();
dataCommand.Con nection = dataConnection;
SqlDataReader dataReader = dataCommand.Exe cuteReader();
while (dataReader.Rea d())
{
if (dataReader["numRecords "].ToString() != null) {
numRecords = Convert.ToInt32 ((dataReader["numRecords "].ToString())); }
}
//numRecords = (Int32)dataComm and.ExecuteScal ar();
}
catch (Exception errLog) {
MessageBox.Show (Convert.ToStri ng(errLog)); }
finally { if (dataConnection .State == ConnectionState .Open)
{ dataConnection. Close(); } }
}
return numRecords;

[Jeroen Mostert]

Todd Jaspers wrote:

Here is what I have:

private int NationalCount()
{
Int32 numRecords = 0;
using (SqlConnection dataConnection = new
SqlConnection(G lobalVars.sqlCo nnString))
{
SqlCommand dataCommand = new SqlCommand();
dataCommand.Com mandText = ("SELECT Count(T1.Report Date) FROM
from ScoutReportsNFS T1 WHERE (guidPlayerPers onID = '" + strCurrentPlaye rID +
"') RETURN COUNT(*)");
try
{
dataConnection. Open();
dataCommand.Con nection = dataConnection;
numRecords = (Int32)dataComm and.ExecuteScal ar();
}
catch (Exception errLog) {
MessageBox.Show (Convert.ToStri ng(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(G lobalVars.sqlCo nnString)) {
dataConnection. Open();
using (SqlCommand dataCommand = dataConnection. CreateCommand() ) {
dataCommand.Com mandText = "SELECT Count(T1.Report Date) FROM
ScoutReportsNFS T1 WHERE guidPlayerPerso nID = @playerID";
dataCommand.Par ameters.AddWith Value("@playerI D", new
Guid(strCurrent PlayerID));
return (int) dataCommand.Exe cuteScalar();
}
}
}

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.

--
J.

[Alun Harford]

Todd Jaspers wrote:

Here is what I have:

private int NationalCount()
{
Int32 numRecords = 0;
using (SqlConnection dataConnection = new
SqlConnection(G lobalVars.sqlCo nnString))
{
SqlCommand dataCommand = new SqlCommand();
dataCommand.Com mandText = ("SELECT Count(T1.Report Date) FROM
from ScoutReportsNFS T1 WHERE (guidPlayerPers onID = '" + strCurrentPlaye rID +
"') RETURN COUNT(*)");
try
{
dataConnection. Open();
dataCommand.Con nection = dataConnection;
numRecords = (Int32)dataComm and.ExecuteScal ar();
}
catch (Exception errLog) {
MessageBox.Show (Convert.ToStri ng(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
strCurrentPlaye rID is "foo');DROP TABLE ScoutReportsNFS ;--"

Alun Harford