CODEWITHSUNDEEP
asp.netsqlvb.netsqldatasource
user481831
user481831

Reputation: 1023

how to count the fetched rows by sqldatasource

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.

Upvotes: 8

Views: 13255

Answers (7)

Chris Catignani
Chris Catignani

Reputation: 5306

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();
}

Upvotes: 3

Andrew Collins
Andrew Collins

Reputation: 2681

You should read this page 

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

Upvotes: 0

user1580004
user1580004

Reputation: 49

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

Upvotes: 0

senthilkumar2185
senthilkumar2185

Reputation: 2566

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

This Code return Count value

Upvotes: 0

Samiey Mehdi
Samiey Mehdi

Reputation: 9414

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

Upvotes: 1

Andrew Collins
Andrew Collins

Reputation: 2681

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();

Upvotes: 0

Breand&#225;n
Breand&#225;n

Reputation: 1883

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

Upvotes: 0

Related Questions