Todd Jaspers wrote:
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.
--
J.