CODEWITHSUNDEEP
asp.netsqldatabasevb.netcount
actKing
actKing

Reputation: 45

how to count the record in the database?

i have this database table called people

 People
peopleID peopleName relationship customerID
1         A          aunty         1
2         B          aunty         1
3         C          second uncle  1
4         D          aunty         2

how am i going to count the number of people where the customerID = 1 and if the relationship is the same, it is counted as 1

so from the database table above, i should get the result of 3 and put the result of 3 in Label1 in gridview?

i can get the count value for the only where the customerID =1 but i can't figure out how am i going to count if the relationship part

 Protected Sub GridView2_RowDataBound(sender As Object, e As 
System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView2.RowDataBound
        If e.Row.RowType = DataControlRowType.DataRow Then
            'Do your processing here...

            Dim txt As TextBox = DirectCast(e.Row.FindControl("Label1"), TextBox)
            Dim adapter As New SqlDataAdapter
            Dim ds As New DataSet
            'Dim sql As String

            Dim connectionString = ConfigurationManager.ConnectionStrings("ProjData").ConnectionString
            Dim myConn As New SqlConnection(connectionString)

            Dim cmd = "Select * From People Where customerID='" & Session("customerID") & "' "

            ' Dim myCmd As New SqlCommand(cmd, myConn)

            Try
                myConn.Open()
                Dim myCmd As New SqlCommand(cmd, myConn)
                adapter.SelectCommand = myCmd
                adapter.Fill(ds, "People")
                adapter.Dispose()
                myCmd.Dispose()
                txt.Text = ds.Tables(0).Rows.Count



            Catch ex As Exception
                MsgBox("Can not open connection ! ")
            End Try
        End If
    End Sub

Upvotes: 0

Views: 4476

Answers (3)

Hanno
Hanno

Reputation: 1027

You should wrap your SqlConnection in a using statement (not sure how you do that in VB). In your example you don't close the connection, the using statement does this for you automatically (the alternative is to close the connection in a finally block)

Upvotes: 1

Wouter de Kort
Wouter de Kort

Reputation: 39898

In your code, you fetch all the rows and then count them. You can also execute a query that will count the number of rows on the server. This will perform much better!

string sql = "SELECT COUNT(*) FROM People Where customerID='" & Session("customerID") & "' "
...
int rowCount = myCmd.ExecuteScalar();

If you want to count the number of rows with the same relationship, you have to use a group by.

You have to change your sql to:

string sql = 'SELECT COUNT(peopleId) FROM People Where customerID='" & Session("customerID") & "' "GROUP BY relationship, customerId"

Upvotes: 0

Andreas Rohde
Andreas Rohde

Reputation: 609

When i correctly understand your question, the following SQL Statement should give you the count as you need it.

Select count(peopleID) 
From People 
Where customerID = 1 
Group by relationship, customerID

If this answer didn't solve your problem please add further information to your question.

Upvotes: 0

Related Questions