how to count the fetched rows by sqldatasource

Question

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
    Dim LoginChecker As New SqlDataSource()
    LoginChecker.ConnectionString = ConfigurationManager.ConnectionStrings("A1ConnectionString1").ToString()
    LoginChecker.SelectCommandType = SqlDataSourceCommandType.Text
    LoginChecker.SelectCommand = "SELECT username FROM A1login WHERE username=@username AND password=@password"
    LoginChecker.SelectParameters.Add("username", username.Text)
    LoginChecker.SelectParameters.Add("password", password.Text)
    Dim rowsAffected As Integer = 0
    Try
        rowsAffected = LoginChecker.<what i have to write here>
    Catch ex As Exception
        'Server.Transfer("LoginSucessful.aspx")
    Finally
        LoginChecker = Nothing
    End Try
    username.Text = rowsAffected
    ' If rowsAffected = 1 Then
    'Server.Transfer("A1success.aspx")
    '   Else
    'Server.Transfer("A1failure.aspx")
    '  End If

End Sub

this is the code for login.aspx.vb
it checks the username and password in the database and redirecrs to respective page on the basis of rows returned. i'm having problem in finding a right function in the sqldatareader namespace so that it counts the number of rows affected. Can anybody tell me the function I should use there? Thanks in advance.

Viridescent

Answer 1

Use the Selected event of the SqlDataSource Control. The event is raised after the Select operation is complete.

protected void SqlDataSource1_Selected(object sender, SqlDataSourceStatusEventArgs e)
{
    lblRecordCount.Text = "Record Count: " + e.AffectedRows.ToString();
}

Cheesewood

Answer 2

protected void Button1_Click(object sender, EventArgs e)
{
    DataView d =(DataView) SqlDataSource1.Select(DataSourceSelectArguments.Empty);
    Response.Write(d.Count);
}

Bullpen

Answer 3

Select will return an IEnumerable. You you can ToList().Count on this.

Kremer

thanks . but, can u plz elaborate what you are saying . correct me if i'm wrong -------- objective is to count the number of IEnumerables so to do that how to code .. is it ToList(LoginChecker).Count – Viridescent

Answer 4

The SQLDataReader doesn't have a row count property the easiest way to get the count would be to do this...

int rowCount = 0;
if (dr.HasRows)  
{  
    while (dr.Read())  
        {  
            rowCount++;
            dgResults.DataSource = dr;  
            dgResults.DataBind();  
            dgResults.Visible = true;  
        }  

lblMsg.Text = rowCount.ToString();
dr.Close(); 

Clamatorial

no man there is an error , there is nothing like HasRows can u plz verify and tell me . shall i need to include any other specific namespace . – Viridescent

Answer 5

You should read this page

 Namespace:  System.Data 
 Assembly:  System.Data (in System.Data.dll)

Clamatorial

Answer 6

To fetch your rows, declare sqlcommand and sqlconnection:

Dim cmd as sqlcommand
Dim con as new sqlconnection("Ur connection string")
cmd.connection=con
cmd.commandtext="SELECT count(username) FROM A1login WHERE username=@username AND password=@password"
Dim rowsAffected As Integer = cmd.executescalar()

In rowsaffected you will get the total number of rows fetched

Vilma

Answer 7

SELECT Count(*) FROM A1login WHERE username=@username AND password=@password"

This Code return Count value

Spunky