|
 |
aspx thread: RecordCount in ASP.NET
Message #1 by "Jose Haymaker" <ehaymaker@a...> on Fri, 19 Oct 2001 16:50:56
|
|
How do I get a recordcount in asp.net? (Note: I am *not* paging through
recordsets.)
But for:
myCommand = New SQLCommand("Select name, lastname from Employees where
lastname='Cook'", myConnection )
How do I get a recordcount?
Message #2 by "Sascha Ederer" <sascha@e...> on Sat, 20 Oct 2001 13:15:22
|
|
I would try to fill a dataset first and then use the count method of the
DataSet. This should work as follows (I used an OleDB Connection to the
database):
Dim userset as new dataset()
Dim userAdapter As OleDbDataAdapter
Dim SQLstring, ConnStr As String
Dim dbConn As OleDbConnection
userAdapter= New OleDbDataAdapter(SQLString, dbConn)
dbConn = "Path to your Database"
SQLString = "Select name, lastname from Employees where
lastname='Cook'"
dbConn = New OleDbConnection(ConnStr)
dbConn.Open()
UserAdapter = New OleDbDataAdapter(SQLString, dbConn)
AuswahlAdapter.Fill(AuswahlSet, "Employees")
'counting the rows of the Table Employees in the DataSet
UserSet.Tables("Employees").Rows.Count
dbConn.Close()
I hope this helps.
Best regards,
Sascha
> How do I get a recordcount in asp.net? (Note: I am *not* paging through
> recordsets.)
>
> But for:
>
> myCommand = New SQLCommand("Select name, lastname from Employees where
> lastname='Cook'", myConnection )
>
> How do I get a recordcount?
Message #3 by "dave 123aspx.com" <support@1...> on Sat, 20 Oct 2001 10:26:51 -0500
|
|
I would probably do something a little less resource expensive and do
something like
SELECT count(PrimaryKey) from Employees where lastname='Cook'
where PrimaryKey is the PrimaryKey of the table.
Then just do an ExecuteScalar() to return that 1 value. If you *have* to
return a result set, then I would preferable return a DataReader, as they
are less expensive than a DataSet.
The execute scalar function might look something like (C#)
SqlConnection sqlConn = new SqlConnection(ConnectString);
SqlCommand sqlCmd = new SqlCommand("SELECT count(PrimaryKey) from Employees
where lastname='Cook'",sqlConn);
sqlCmd.Connection.Open();
int RecordCount = Convert.ToInt32( sqlCmd.ExecuteScalar().ToString() );
Cheers!
dave
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
http://www.123aspx.com
The Largest ASP.NET Web Directory!
Find the latest ASP.NET resources --
Subscribe to our newsletter!
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
----- Original Message -----
From: "Sascha Ederer" <sascha@e...>
To: "ASP+" <aspx@p...>
Sent: Saturday, October 20, 2001 1:15 PM
Subject: [aspx] Re: RecordCount in ASP.NET
> I would try to fill a dataset first and then use the count method of the
> DataSet. This should work as follows (I used an OleDB Connection to the
> database):
>
> Dim userset as new dataset()
> Dim userAdapter As OleDbDataAdapter
> Dim SQLstring, ConnStr As String
> Dim dbConn As OleDbConnection
>
> userAdapter= New OleDbDataAdapter(SQLString, dbConn)
> dbConn = "Path to your Database"
> SQLString = "Select name, lastname from Employees where
> lastname='Cook'"
>
> dbConn = New OleDbConnection(ConnStr)
> dbConn.Open()
> UserAdapter = New OleDbDataAdapter(SQLString, dbConn)
> AuswahlAdapter.Fill(AuswahlSet, "Employees")
>
> 'counting the rows of the Table Employees in the DataSet
> UserSet.Tables("Employees").Rows.Count
> dbConn.Close()
>
> I hope this helps.
>
> Best regards,
>
> Sascha
>
> > How do I get a recordcount in asp.net? (Note: I am *not* paging through
> > recordsets.)
> >
> > But for:
> >
> > myCommand = New SQLCommand("Select name, lastname from Employees where
> > lastname='Cook'", myConnection )
> >
> > How do I get a recordcount?
>
Message #4 by "GfWeis" <gfw@y...> on Sat, 20 Oct 2001 18:06:24 -0500
|
|
For a C# RecordCount fucntion using ExecuteScalar, check out...
http://dn.yyyz.net/DnYzArticleShow.aspx?WA=2
Gfw
-----Original Message-----
From: dave 123aspx.com [mailto:support@1...]
Sent: Saturday, October 20, 2001 10:27 AM
To: ASP+
Subject: [aspx] Re: RecordCount in ASP.NET
I would probably do something a little less resource expensive and do
something like
SELECT count(PrimaryKey) from Employees where lastname='Cook'
where PrimaryKey is the PrimaryKey of the table.
Then just do an ExecuteScalar() to return that 1 value. If you *have* to
return a result set, then I would preferable return a DataReader, as they
are less expensive than a DataSet.
The execute scalar function might look something like (C#)
SqlConnection sqlConn = new SqlConnection(ConnectString);
SqlCommand sqlCmd = new SqlCommand("SELECT count(PrimaryKey) from Employees
where lastname='Cook'",sqlConn);
sqlCmd.Connection.Open();
int RecordCount = Convert.ToInt32( sqlCmd.ExecuteScalar().ToString() );
Cheers!
dave
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
http://www.123aspx.com
The Largest ASP.NET Web Directory!
Find the latest ASP.NET resources --
Subscribe to our newsletter!
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
----- Original Message -----
From: "Sascha Ederer" <sascha@e...>
To: "ASP+" <aspx@p...>
Sent: Saturday, October 20, 2001 1:15 PM
Subject: [aspx] Re: RecordCount in ASP.NET
> I would try to fill a dataset first and then use the count method of the
> DataSet. This should work as follows (I used an OleDB Connection to the
> database):
>
> Dim userset as new dataset()
> Dim userAdapter As OleDbDataAdapter
> Dim SQLstring, ConnStr As String
> Dim dbConn As OleDbConnection
>
> userAdapter= New OleDbDataAdapter(SQLString, dbConn)
> dbConn = "Path to your Database"
> SQLString = "Select name, lastname from Employees where
> lastname='Cook'"
>
> dbConn = New OleDbConnection(ConnStr)
> dbConn.Open()
> UserAdapter = New OleDbDataAdapter(SQLString, dbConn)
> AuswahlAdapter.Fill(AuswahlSet, "Employees")
>
> 'counting the rows of the Table Employees in the DataSet
> UserSet.Tables("Employees").Rows.Count
> dbConn.Close()
>
> I hope this helps.
>
> Best regards,
>
> Sascha
>
> > How do I get a recordcount in asp.net? (Note: I am *not* paging through
> > recordsets.)
> >
> > But for:
> >
> > myCommand = New SQLCommand("Select name, lastname from Employees where
> > lastname='Cook'", myConnection )
> >
> > How do I get a recordcount?
>
Message #5 by Todd Carrico <ToddC@m...> on Tue, 23 Oct 2001 05:49:14 -0500
|
|
A little tip here...
Select Count(*) From MyTable
is more efficient than
Select Count(someColumn) From MyTable
Select Count(1) From MyTable is even better.
Problem, Select Count(*), and Count(1) both will count Nulls. If you are
trying to count only populated values, i.e. not Nulls, then you should use
the particular column. In the examples here, they are counting on the
Primary Key, which should not allow nulls, so Count(1) would work.
ExecuteScalar is the preferred method to just retrieve a count. In SQL
Server, a stored proc could populate an output variable with the @@Rowcount
variable, after returning the records. Problem with this approach is that
you can't get access to the output variable until the connection is closed,
can't close the connection for a DataReader without loosing the data.
So if you must have a record count, it sounds like you need to use a DataSet
or an Array to hold the records and close the connection to get at the
output variable, or just use the count functionality in the DataRows
collection, or Array.
hth
tc
-----Original Message-----
From: GfWeis [mailto:gfw@y...]
Sent: Saturday, October 20, 2001 6:06 PM
To: ASP+
Subject: [aspx] Re: RecordCount in ASP.NET
For a C# RecordCount fucntion using ExecuteScalar, check out...
http://dn.yyyz.net/DnYzArticleShow.aspx?WA=2
Gfw
-----Original Message-----
From: dave 123aspx.com [mailto:support@1...]
Sent: Saturday, October 20, 2001 10:27 AM
To: ASP+
Subject: [aspx] Re: RecordCount in ASP.NET
I would probably do something a little less resource expensive and do
something like
SELECT count(PrimaryKey) from Employees where lastname='Cook'
where PrimaryKey is the PrimaryKey of the table.
Then just do an ExecuteScalar() to return that 1 value. If you *have* to
return a result set, then I would preferable return a DataReader, as they
are less expensive than a DataSet.
The execute scalar function might look something like (C#)
SqlConnection sqlConn = new SqlConnection(ConnectString);
SqlCommand sqlCmd = new SqlCommand("SELECT count(PrimaryKey) from Employees
where lastname='Cook'",sqlConn);
sqlCmd.Connection.Open();
int RecordCount = Convert.ToInt32( sqlCmd.ExecuteScalar().ToString() );
Cheers!
dave
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
http://www.123aspx.com
The Largest ASP.NET Web Directory!
Find the latest ASP.NET resources --
Subscribe to our newsletter!
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
----- Original Message -----
From: "Sascha Ederer" <sascha@e...>
To: "ASP+" <aspx@p...>
Sent: Saturday, October 20, 2001 1:15 PM
Subject: [aspx] Re: RecordCount in ASP.NET
> I would try to fill a dataset first and then use the count method of the
> DataSet. This should work as follows (I used an OleDB Connection to the
> database):
>
> Dim userset as new dataset()
> Dim userAdapter As OleDbDataAdapter
> Dim SQLstring, ConnStr As String
> Dim dbConn As OleDbConnection
>
> userAdapter= New OleDbDataAdapter(SQLString, dbConn)
> dbConn = "Path to your Database"
> SQLString = "Select name, lastname from Employees where
> lastname='Cook'"
>
> dbConn = New OleDbConnection(ConnStr)
> dbConn.Open()
> UserAdapter = New OleDbDataAdapter(SQLString, dbConn)
> AuswahlAdapter.Fill(AuswahlSet, "Employees")
>
> 'counting the rows of the Table Employees in the DataSet
> UserSet.Tables("Employees").Rows.Count
> dbConn.Close()
>
> I hope this helps.
>
> Best regards,
>
> Sascha
>
> > How do I get a recordcount in asp.net? (Note: I am *not* paging through
> > recordsets.)
> >
> > But for:
> >
> > myCommand = New SQLCommand("Select name, lastname from Employees where
> > lastname='Cook'", myConnection )
> >
> > How do I get a recordcount?
Message #6 by "GfWeis" <gfw@y...> on Tue, 23 Oct 2001 06:06:34 -0500
|
|
I use the ExecuteScalar function pretty regularly and have included it in my
C# Class generator as an option function within the class. Check out...
http://dn.yyyz.net/DnYzArticleShow.aspx?WA=2
Gfw
-----Original Message-----
From: Todd Carrico [mailto:ToddC@m...]
Sent: Tuesday, October 23, 2001 5:49 AM
To: ASP+
Subject: [aspx] Re: RecordCount in ASP.NET
A little tip here...
Select Count(*) From MyTable
is more efficient than
Select Count(someColumn) From MyTable
Select Count(1) From MyTable is even better.
Problem, Select Count(*), and Count(1) both will count Nulls. If you are
trying to count only populated values, i.e. not Nulls, then you should use
the particular column. In the examples here, they are counting on the
Primary Key, which should not allow nulls, so Count(1) would work.
ExecuteScalar is the preferred method to just retrieve a count. In SQL
Server, a stored proc could populate an output variable with the @@Rowcount
variable, after returning the records. Problem with this approach is that
you can't get access to the output variable until the connection is closed,
can't close the connection for a DataReader without loosing the data.
So if you must have a record count, it sounds like you need to use a DataSet
or an Array to hold the records and close the connection to get at the
output variable, or just use the count functionality in the DataRows
collection, or Array.
hth
tc
-----Original Message-----
From: GfWeis [mailto:gfw@y...]
Sent: Saturday, October 20, 2001 6:06 PM
To: ASP+
Subject: [aspx] Re: RecordCount in ASP.NET
For a C# RecordCount fucntion using ExecuteScalar, check out...
http://dn.yyyz.net/DnYzArticleShow.aspx?WA=2
Gfw
-----Original Message-----
From: dave 123aspx.com [mailto:support@1...]
Sent: Saturday, October 20, 2001 10:27 AM
To: ASP+
Subject: [aspx] Re: RecordCount in ASP.NET
I would probably do something a little less resource expensive and do
something like
SELECT count(PrimaryKey) from Employees where lastname='Cook'
where PrimaryKey is the PrimaryKey of the table.
Then just do an ExecuteScalar() to return that 1 value. If you *have* to
return a result set, then I would preferable return a DataReader, as they
are less expensive than a DataSet.
The execute scalar function might look something like (C#)
SqlConnection sqlConn = new SqlConnection(ConnectString);
SqlCommand sqlCmd = new SqlCommand("SELECT count(PrimaryKey) from Employees
where lastname='Cook'",sqlConn);
sqlCmd.Connection.Open();
int RecordCount = Convert.ToInt32( sqlCmd.ExecuteScalar().ToString() );
Cheers!
dave
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
http://www.123aspx.com
The Largest ASP.NET Web Directory!
Find the latest ASP.NET resources --
Subscribe to our newsletter!
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
----- Original Message -----
From: "Sascha Ederer" <sascha@e...>
To: "ASP+" <aspx@p...>
Sent: Saturday, October 20, 2001 1:15 PM
Subject: [aspx] Re: RecordCount in ASP.NET
> I would try to fill a dataset first and then use the count method of the
> DataSet. This should work as follows (I used an OleDB Connection to the
> database):
>
> Dim userset as new dataset()
> Dim userAdapter As OleDbDataAdapter
> Dim SQLstring, ConnStr As String
> Dim dbConn As OleDbConnection
>
> userAdapter= New OleDbDataAdapter(SQLString, dbConn)
> dbConn = "Path to your Database"
> SQLString = "Select name, lastname from Employees where
> lastname='Cook'"
>
> dbConn = New OleDbConnection(ConnStr)
> dbConn.Open()
> UserAdapter = New OleDbDataAdapter(SQLString, dbConn)
> AuswahlAdapter.Fill(AuswahlSet, "Employees")
>
> 'counting the rows of the Table Employees in the DataSet
> UserSet.Tables("Employees").Rows.Count
> dbConn.Close()
>
> I hope this helps.
>
> Best regards,
>
> Sascha
>
> > How do I get a recordcount in asp.net? (Note: I am *not* paging through
> > recordsets.)
> >
> > But for:
> >
> > myCommand = New SQLCommand("Select name, lastname from Employees where
> > lastname='Cook'", myConnection )
> >
> > How do I get a recordcount?
|
|
|
View Cart
