Click here to Skip to main content
15,035,600 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
i have this database table called people
HTML
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

VB
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
Posted
Updated 30-Jan-12 20:45pm
v2
Comments
manognya kota 31-Jan-12 3:07am
   
If you are counting based on customerid and relation, you would get count of 2 for customerid 1.Is my understanding correct?

Try This And tell me is it what u want or any thing else

SQL
SELECT        customerID, relationship , COUNT(*) AS Expr1
FROM            Result
WHERE        (customerID= 1)
GROUP BY customerID, relationship 
   
Do you need a query??
Something like this,

SQL
(SELECT COUNT(distinct(relationship)) FROM People WHERE customerID=1)


I hope this help.

Thanks!!
   
Comments
vjdeeds 31-Jan-12 5:54am
   
Please mark it as a solution if it helped you.
The code would be
VB
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 COUNT(distinct(relationship)) FROM People WHERE " & Session("customerID") & " "
 
            ' Dim myCmd As New SqlCommand(cmd, myConn)

            Try
                myConn.Open()
                Dim myCmd As New SqlCommand(cmd, myConn)
                adapter.SelectCommand = myCmd
                txt.Text = adapter.SelectCommand.ExecuteScalar().ToString()
                adapter.Dispose()
                myCmd.Dispose()
               
 

 
            Catch ex As Exception
                MsgBox("Can not open connection ! ")
            End Try
        End If
    End Sub
   
v2
Comments
Anuja Pawar Indore 31-Jan-12 4:23am
   
Added pre tag

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900