SQL Server/C# Number of Rows...

[trint]

I have a select statement that all I want to do is get the number of
rows returned in my query:

string strSQLAccountIn fo1 = "select * " +
"FROM orders " +
"where user_id = '" + Class1.loginUse rid.Trim() +
"' ";

SqlCommand cmdAL = new SqlCommand(strS QLAccountInfo1,
cnSQLAccountInf o1);
cnSQLAccountInf o1 = new
SqlConnection(C onnectionstring AccountInfo1);
cnSQLAccountInf o1.Open();

SqlCommand cmSQLAccountInf o1;
cmSQLAccountInf o1 = new SqlCommand(strS QLAccountInfo1,
cnSQLAccountInf o1);
drSQLAccountInf o1 = cmSQLAccountInf o1.ExecuteReade r();

int numbOfRowsAffec ted = 0;
numbOfRowsAffec ted =
drSQLAccountInf o1.RecordsAffec ted;

if (drSQLAccountIn fo1.Read())
{

Class1.UseridCo mpany =
drSQLAccountInf o1["order_date "].ToString();
}

Any help is appreciated.
Thanks,
Trint

[Nicholas Paldino [.NET/C# MVP]]

When you execute a reader, you will not be able to get the number of
rows until you have finished cycling through the rows. In order to know the
number of rows beforehand, you will have to execute a call to the count
function in SQL server in order to get a record count.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard. caspershouse.co m

"trint" <tr***********@ gmail.comwrote in message
news:11******** **************@ u30g2000hsc.goo glegroups.com.. .

>I have a select statement that all I want to do is get the number of
rows returned in my query:

string strSQLAccountIn fo1 = "select * " +
"FROM orders " +
"where user_id = '" + Class1.loginUse rid.Trim() +
"' ";

SqlCommand cmdAL = new SqlCommand(strS QLAccountInfo1,
cnSQLAccountInf o1);
cnSQLAccountInf o1 = new
SqlConnection(C onnectionstring AccountInfo1);
cnSQLAccountInf o1.Open();

SqlCommand cmSQLAccountInf o1;
cmSQLAccountInf o1 = new SqlCommand(strS QLAccountInfo1,
cnSQLAccountInf o1);
drSQLAccountInf o1 = cmSQLAccountInf o1.ExecuteReade r();

int numbOfRowsAffec ted = 0;
numbOfRowsAffec ted =
drSQLAccountInf o1.RecordsAffec ted;

if (drSQLAccountIn fo1.Read())
{

Class1.UseridCo mpany =
drSQLAccountInf o1["order_date "].ToString();
}

Any help is appreciated.
Thanks,
Trint

[trint]

On May 18, 10:45 am, "Nicholas Paldino [.NET/C# MVP]"
<[e-mail] d.caspershouse. comwrote:

When you execute a reader, you will not be able to get the number of
rows until you have finished cycling through the rows. In order to know the
number of rows beforehand, you will have to execute a call to the count
function in SQL server in order to get a record count.

--
- Nicholas Paldino [.NET/C# MVP]
- [e-mail] .caspershouse.c om

"trint" <trinity.sm...@ gmail.comwrote in message

news:11******** **************@ u30g2000hsc.goo glegroups.com.. .

I have a select statement that all I want to do is get the number of
rows returned in my query:

string strSQLAccountIn fo1 = "select * " +
"FROM orders " +
"where user_id = '" + Class1.loginUse rid.Trim() +
"' ";

SqlCommand cmdAL = new SqlCommand(strS QLAccountInfo1,
cnSQLAccountInf o1);
cnSQLAccountInf o1 = new
SqlConnection(C onnectionstring AccountInfo1);
cnSQLAccountInf o1.Open();

SqlCommand cmSQLAccountInf o1;
cmSQLAccountInf o1 = new SqlCommand(strS QLAccountInfo1,
cnSQLAccountInf o1);
drSQLAccountInf o1 = cmSQLAccountInf o1.ExecuteReade r();

int numbOfRowsAffec ted = 0;
numbOfRowsAffec ted =
drSQLAccountInf o1.RecordsAffec ted;

if (drSQLAccountIn fo1.Read())
{

Class1.UseridCo mpany =
drSQLAccountInf o1["order_date "].ToString();
}

Any help is appreciated.
Thanks,
Trint- Hide quoted text -

- Show quoted text -

So, in this case, for me to do an increment (i++) while reading is the
best way?
Thanks,
Trint

[Alberto Poblacion]

"trint" <tr***********@ gmail.comwrote in message
news:11******** **************@ u30g2000hsc.goo glegroups.com.. .

>I have a select statement that all I want to do is get the number of
rows returned in my query:

The easiest way would be to execute a "Select COUNT(*) from...". It's
best to use ExecuteScalar() rather than ExecuteNonQuery for this purpose.

The drSQLAccountInf o1.RecordsAffec ted that you are trying to use won't
work. It only counts the reccords for Insert, Update or Delete (not for
Select), and it only gives the result AFTER you have closed the datareader.

[Dom]

Forget CSharp and ADO for the moment. If you were doing this at Query
Analyzer, how would you get the number of rows? You need to use the
aggregate function, Count. The SQL statement is:

Select count (*)
from orders
where user_id = <???>

My experience has been that "RecordsAffecte d" is not very reliable
anyway. Don't know why.

Dom

On May 18, 10:25 am, trint <trinity.sm...@ gmail.comwrote:

I have a select statement that all I want to do is get the number of
rows returned in my query:

string strSQLAccountIn fo1 = "select * " +
"FROM orders " +
"where user_id = '" + Class1.loginUse rid.Trim() +
"' ";

SqlCommand cmdAL = new SqlCommand(strS QLAccountInfo1,
cnSQLAccountInf o1);
cnSQLAccountInf o1 = new
SqlConnection(C onnectionstring AccountInfo1);
cnSQLAccountInf o1.Open();

SqlCommand cmSQLAccountInf o1;
cmSQLAccountInf o1 = new SqlCommand(strS QLAccountInfo1,
cnSQLAccountInf o1);
drSQLAccountInf o1 = cmSQLAccountInf o1.ExecuteReade r();

int numbOfRowsAffec ted = 0;
numbOfRowsAffec ted =
drSQLAccountInf o1.RecordsAffec ted;

if (drSQLAccountIn fo1.Read())
{

Class1.UseridCo mpany =
drSQLAccountInf o1["order_date "].ToString();
}

Any help is appreciated.
Thanks,
Trint

[Nicholas Paldino [.NET/C# MVP]]

Yes, I would say so. If you need to know the value before you iterate
through the results, then you will need to issue the query twice, once to
get the count, once to get the actual results.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard. caspershouse.co m

"trint" <tr***********@ gmail.comwrote in message
news:11******** *************@y 80g2000hsf.goog legroups.com...

On May 18, 10:45 am, "Nicholas Paldino [.NET/C# MVP]"
<[e-mail] d.caspershouse. comwrote:

> When you execute a reader, you will not be able to get the number of
rows until you have finished cycling through the rows. In order to know
the
number of rows beforehand, you will have to execute a call to the count
function in SQL server in order to get a record count.

--
- Nicholas Paldino [.NET/C# MVP]
- [e-mail] .caspershouse.c om

"trint" <trinity.sm...@ gmail.comwrote in message

news:11******* *************** @u30g2000hsc.go oglegroups.com. ..

>I have a select statement that all I want to do is get the number of
rows returned in my query:

string strSQLAccountIn fo1 = "select * " +
"FROM orders " +
"where user_id = '" + Class1.loginUse rid.Trim() +
"' ";

SqlCommand cmdAL = new SqlCommand(strS QLAccountInfo1,
cnSQLAccountInf o1);
cnSQLAccountInf o1 = new
SqlConnection(C onnectionstring AccountInfo1);
cnSQLAccountInf o1.Open();

SqlCommand cmSQLAccountInf o1;
cmSQLAccountInf o1 = new SqlCommand(strS QLAccountInfo1,
cnSQLAccountInf o1);
drSQLAccountInf o1 = cmSQLAccountInf o1.ExecuteReade r();

int numbOfRowsAffec ted = 0;
numbOfRowsAffec ted =
drSQLAccountInf o1.RecordsAffec ted;

if (drSQLAccountIn fo1.Read())
{

Class1.UseridCo mpany =
drSQLAccountInf o1["order_date "].ToString();
}

Any help is appreciated.
Thanks,
Trint- Hide quoted text -

- Show quoted text -

So, in this case, for me to do an increment (i++) while reading is the
best way?
Thanks,
Trint

[Moty Michaely]

On May 18, 5:59 pm, "Nicholas Paldino [.NET/C# MVP]"
<[e-mail] d.caspershouse. comwrote:

Yes, I would say so. If you need to know the value before you iterate
through the results, then you will need to issue the query twice, once to
get the count, once to get the actual results.

--
- Nicholas Paldino [.NET/C# MVP]
- [e-mail] .caspershouse.c om

"trint" <trinity.sm...@ gmail.comwrote in message

news:11******** *************@y 80g2000hsf.goog legroups.com...

On May 18, 10:45 am, "Nicholas Paldino [.NET/C# MVP]"
<[e-mail] d.caspershouse. comwrote:

When you execute a reader, you will not be able to get the number of
rows until you have finished cycling through the rows. In order to know
the
number of rows beforehand, you will have to execute a call to the count
function in SQL server in order to get a record count.

--
- Nicholas Paldino [.NET/C# MVP]
- [e-mail] .caspershouse.c om

"trint" <trinity.sm...@ gmail.comwrote in message

>news:11******* *************** @u30g2000hsc.go oglegroups.com. ..

I have a select statement that all I want to do is get the number of
rows returned in my query:

string strSQLAccountIn fo1 = "select * " +
"FROM orders " +
"where user_id = '" + Class1.loginUse rid.Trim() +
"' ";

SqlCommand cmdAL = new SqlCommand(strS QLAccountInfo1,
cnSQLAccountInf o1);
cnSQLAccountInf o1 = new
SqlConnection(C onnectionstring AccountInfo1);
cnSQLAccountInf o1.Open();

SqlCommand cmSQLAccountInf o1;
cmSQLAccountInf o1 = new SqlCommand(strS QLAccountInfo1,
cnSQLAccountInf o1);
drSQLAccountInf o1 = cmSQLAccountInf o1.ExecuteReade r();

int numbOfRowsAffec ted = 0;
numbOfRowsAffec ted =
drSQLAccountInf o1.RecordsAffec ted;

if (drSQLAccountIn fo1.Read())
{

Class1.UseridCo mpany =
drSQLAccountInf o1["order_date "].ToString();
}

Any help is appreciated.
Thanks,
Trint- Hide quoted text -

- Show quoted text -

So, in this case, for me to do an increment (i++) while reading is the
best way?
Thanks,
Trint

Hi,

SQL Server 2005 has functionalities for a RowID column and stuff like
you look for, so that might be helpful :)

Anyhow, aggregate functions is the only way I know.

About RecordsAffected : since the command executes a reader, the reader
is like a (forward only) cursor. So there is no way knowing the number
of rows prior to iterating through all the rows.

Cheers,
Moty

[trint]

On May 18, 11:06 am, Moty Michaely <Moty...@gmail. comwrote:

On May 18, 5:59 pm, "Nicholas Paldino [.NET/C# MVP]"

<[e-mail] d.caspershouse. comwrote:

Yes, I would say so. If you need to know the value before you iterate
through the results, then you will need to issue the query twice, once to
get the count, once to get the actual results.

--
- Nicholas Paldino [.NET/C# MVP]
- [e-mail] .caspershouse.c om

"trint" <trinity.sm...@ gmail.comwrote in message

news:11******** *************@y 80g2000hsf.goog legroups.com...

On May 18, 10:45 am, "Nicholas Paldino [.NET/C# MVP]"
<[e-mail] d.caspershouse. comwrote:
> When you execute a reader, you will not be able to get the number of
>rows until you have finished cycling through the rows. In order to know
>the
>number of rows beforehand, you will have to execute a call to the count
>function in SQL server in order to get a record count.

>--
> - Nicholas Paldino [.NET/C# MVP]
> - [e-mail] .caspershouse.c om

>"trint" <trinity.sm...@ gmail.comwrote in message

>>news:11****** *************** *@u30g2000hsc.g ooglegroups.com ...

>I have a select statement that all I want to do is get the number of
rows returned in my query:

string strSQLAccountIn fo1 = "select * " +
"FROM orders " +
"where user_id = '" + Class1.loginUse rid.Trim() +
"' ";

SqlCommand cmdAL = new SqlCommand(strS QLAccountInfo1,
cnSQLAccountInf o1);
cnSQLAccountInf o1 = new
SqlConnection(C onnectionstring AccountInfo1);
cnSQLAccountInf o1.Open();

SqlCommand cmSQLAccountInf o1;
cmSQLAccountInf o1 = new SqlCommand(strS QLAccountInfo1,
cnSQLAccountInf o1);
drSQLAccountInf o1 = cmSQLAccountInf o1.ExecuteReade r();

int numbOfRowsAffec ted = 0;
numbOfRowsAffec ted =
drSQLAccountInf o1.RecordsAffec ted;

if (drSQLAccountIn fo1.Read())
{

Class1.UseridCo mpany =
drSQLAccountInf o1["order_date "].ToString();
}

Any help is appreciated.
Thanks,
Trint- Hide quoted text -

>- Show quoted text -

So, in this case, for me to do an increment (i++) while reading is the
best way?
Thanks,
Trint

Hi,

SQL Server 2005 has functionalities for a RowID column and stuff like
you look for, so that might be helpful :)

Anyhow, aggregate functions is the only way I know.

About RecordsAffected : since the command executes a reader, the reader
is like a (forward only) cursor. So there is no way knowing the number
of rows prior to iterating through all the rows.

Cheers,
Moty- Hide quoted text -

- Show quoted text -

The increment worked! Thanks everyone.
Trint

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

Trint:

I agree with Albert P. I hope this linik helps you,
See the remarks section.

http://msdn2.microsoft.com/en-us/lib...utescalar.aspx

--
Ivan A Loreto
Application Analyst II
Loma Linda University Medical Center
Transplantation Institute
"trint" wrote:

I have a select statement that all I want to do is get the number of
rows returned in my query:

string strSQLAccountIn fo1 = "select * " +
"FROM orders " +
"where user_id = '" + Class1.loginUse rid.Trim() +
"' ";

SqlCommand cmdAL = new SqlCommand(strS QLAccountInfo1,
cnSQLAccountInf o1);
cnSQLAccountInf o1 = new
SqlConnection(C onnectionstring AccountInfo1);
cnSQLAccountInf o1.Open();

SqlCommand cmSQLAccountInf o1;
cmSQLAccountInf o1 = new SqlCommand(strS QLAccountInfo1,
cnSQLAccountInf o1);
drSQLAccountInf o1 = cmSQLAccountInf o1.ExecuteReade r();

int numbOfRowsAffec ted = 0;
numbOfRowsAffec ted =
drSQLAccountInf o1.RecordsAffec ted;

if (drSQLAccountIn fo1.Read())
{

Class1.UseridCo mpany =
drSQLAccountInf o1["order_date "].ToString();
}

Any help is appreciated.
Thanks,
Trint