By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,467 Members | 1,448 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,467 IT Pros & Developers. It's quick & easy.

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

P: n/a
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...
Jul 14 '08 #1
Share this Question
Share on Google+
4 Replies


P: n/a

"Todd Jaspers" <To*********@discussions.microsoft.comwrote in message
news:CA**********************************@microsof t.com...
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(*)".
Jul 14 '08 #2

P: n/a
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;

Jul 14 '08 #3

P: n/a
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.
Jul 14 '08 #4

P: n/a
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...
In addition to what has been said here, I'd like to add that my
strCurrentPlayerID is "foo');DROP TABLE ScoutReportsNFS;--"

Alun Harford
Jul 14 '08 #5

This discussion thread is closed

Replies have been disabled for this discussion.