Displaying number of rows with the same value into label

Question

Please help me in figuring this one out. So I am trying to display the number of records with the same value into a label in my form.

SELECT Status_ , COUNT(Status_) FROM ISSUANCE WHERE Status_ = 'Draft' GROUP BY Status_

How can I display it? Thank you.

    Dim disconn As New SqlConnection("Server = EURIZZE-PC;Database = INTERTRANS;Integrated Security = SSPI;")
    Dim DataSet2 As New DataSet


    Dim sqlcon As String = "SELECT ISSUANCE.Status_ , COUNT(Status_) FROM ISSUANCE WHERE Status_ = 'Draft' GROUP BY Status_"
    Dim sqlda As New SqlDataAdapter(sql, thisConnection)

    da.Fill(DataSet1, "ISSUANCE")
    NameLabel1.DataBindings.Add("text", DataSet1, "") 'So I am having trouble on how to display it. Because the rowcount is not a permanent column.

Is the problem with your vb? If so can you include that code? If you've encountered any error messages can you add those too. — David Rushton, Jan 18 '16 at 09:41
little remark: it seems that `WHERE Status_ = 'Draft'` is redundant. But there is no errors in query — Ruslan K., Jan 18 '16 at 09:45
@Ian my query works fine when I tried it in sql server. The thing is that I don't have any idea on how to display the rowcount :( — noob-programmer, Jan 18 '16 at 09:54
Hi @wingedpanther I have tried the link that you posted. But then I got an error that says "variable cmd hides a variable in an enclosing block" What does that mean? Thank you. — noob-programmer, Jan 18 '16 at 10:17
@wingedpanther the error is still there. The display should be like this: "There are *no. of rows* item/s in the database." — noob-programmer, Jan 18 '16 at 10:21
Copy paste your code http://hastebin.com/ and comment the link,let me check it — Vivek S., Jan 18 '16 at 10:23
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/100961/discussion-between-noob-programmer-and-wingedpanther). — noob-programmer, Jan 18 '16 at 10:46

score 1 · Answer 1 · answered Jan 18 '16 at 09:42

1

Probably change your query with a case expression like

SELECT Status_ , 
SUM(CASE WHEN Status_ = 'Draft' THEN 1 ELSE 0 END) AS NewSum
FROM ISSUANCE 
GROUP BY Status_;

answered Jan 18 '16 at 09:42

Rahul

76,197
13
71
125

score 1 · Answer 2 · answered Jan 18 '16 at 09:44

1

Are you working with win-forms? You didn't provide much information here...

Label newlabel = new Label();
newlabel.Text = ""; // <- put your query-result in here

answered Jan 18 '16 at 09:44

S. Jerk

29
8

score 0 · Answer 3 · answered Jan 20 '16 at 04:20

Try following method

  Dim disconn As New SqlConnection("Server = EURIZZE-PC;Database = INTERTRANS;Integrated Security = SSPI;")
            Dim retval As String
            Dim arRetval As String()
            Using cmd1 As New SqlCommand("", disconn)
                With cmd1
                    .CommandText = "SELECT Status_ +'|'+cast( COUNT(Status_)as varchar) FROM ISSUANCE WHERE Status_ = 'Draft' GROUP BY Status_"
                    retval = .ExecuteScalar
                End With
            End Using
            If retval <> String.Empty Then
                arRetval = retval.Split("|")
                count.Text = arRetval(0) & " " & arRetval(1)
            End If

Displaying number of rows with the same value into label

3 Answers3