Hi,

How do i count the recordcount returned by a SQL in aspx?
The code that i used is:

query2 = "select * from bank_master order by bank_id"
cmd2.CommandType = CommandType.Text
cmd2.Connection = MyConnection
cmd2.CommandText = query2
rs2 = cmd2.ExecuteReader
If rs2.HasRows Then
Dim count = rs2.RecordsAffected
Response.Write(count.ToString)
%>
<table>
<%
    While rs2.Read()
    %>
    <tr>
    <td>
    <%=rs2(1)%>
    </td>
    </tr>
    <%
    End While
%>
</table>
<%
Else
Response.Write("No banks yet registered in the system")
End If


-------------------------
It returns -1

Any help?



---
Best regards,

Debashis

__________________
---
Best regards,

Debashis
http://debashis80.spaces.live.com/

A datareader is a "connected" data object. This means it is constantly connected to the database as long as it's open in the code. This makes it faster, but has the drawback that it only gets the data that is required as it is requested. As a result, you can't ask it how many records there are for a given query because it doesn't know. It only knows that it has reached the end of the requested set when it asks the database for the next record (DataReader.Read()) and that method call returns false.

In order to get a complete set of data and be able to determine the number of rows, you need to work with a "disconnected" data object, such as the DataTable. A DataTable has a rows collection which has a count property.

-Peter

Hi Peter,

Thanks for the response.

Is it possible toi provide me with a snippet code, so that I can move forward with that?



---
Best regards,

Debashis

Hi

A simple way of filling a dataset and determining the recordcount of default datatable is below:

SqlConnection sConn = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
            string sQuery = "spTest";

            sConn.Open();
            SqlCommand sqlCmd = new SqlCommand(sQuery,sConn);
            sqlCmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter(sqlCmd);

            DataSet ds = new DataSet();
            da.Fill(ds);

            Response.Write(ds.Tables[0].Rows.Count);

Regards
Mike

Fortune favours the brave, so don't regret on missed oppurtunities.

Thanks Mike,

It does help ! :)

---
Best regards,

Debashis

better than that, just do a select count(*) on the numbers of rows so you don't need to populate a dataset...

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========

Hi Ganzalo,

I have been using count(*) previously only..
However now I got 2 options.. :)

---
Best regards,

Debashis