Display count (sql) in vb.net

[noobies]

I had a problem on displaying count sql in vb. Anyone would help me? I had google about this, and I had to use executereader but I don't know how to use it

Dim dso1 As Label = CType(e.Item.FindControl("dso1"), Label)
        If Not IsNothing(dso1) Then
            Dim name As String

            name = dr.Item("alert_id")

            If (name = "DSO") Then

                strSQL = "SELECT COUNT(alert_value) AS alert1 "
                strSQL = strSQL & "FROM company_alert a "
                strSQL = strSQL & "LEFT JOIN ref_alert_parameter b ON a.alert_id = b.alert_id "
                strSQL = strSQL & "WHERE b.alert_id = 'DSO' And alert_value >= para1 "


                'dso1.Text = dr.Item("alert1")
                dso1.Text = e.Item.DataItem("alert1").ToString
                dso1.BackColor = Drawing.Color.Red

[Pgmer]

What count you want to show? Where is the execute reader in your code here?
This half code does not explain anything...

[noobies]

I want to show count for the alert value. I don't know how to use the execute reader, that's why I'm asking it here

[Pgmer]

Try
            Dim cn As SqlConnection
            Dim strCnn As String = "Your data base connection string"
            cn = New SqlConnection(strCnn)
            cn.Open()
            Dim comm As New SqlCommand("Your sp or Command text to get the data from DB", cn)
            '' If ur using Sp then Commandtype= CommandType.StoredProcedure if it is Text then comm.CommandType=CommandType.Text
            comm.CommandType = CommandType.StoredProcedure
            Dim ds As SqlDataReader
            ds = comm.ExecuteReader
            If ds.HasRows Then
                While ds.Read
                    ''
                    ' do what u want here using the data
                End While
            End If

            ''Close your connections and commands.
            cn.Close()
        Catch ex As Exception
            ''Handle error if any
        End Try

[noobies]

thank you pgmer, i'm using that in my code, my probs is just to display the count sql. i had try using

If (name = "DSO") Then
 
                strSQL = "SELECT COUNT(alert_value) AS alert1 "
                strSQL = strSQL & "FROM company_alert a "
                strSQL = strSQL & "LEFT JOIN ref_alert_parameter b ON a.alert_id = b.alert_id "
                strSQL = strSQL & "WHERE b.alert_id = 'DSO' And alert_value >= para1 "
 
 
                dso1.Text = e.Item.DataItem("alert1").ToString

alert1 to display the count, but it seems nothing come out..

[adam_k]

What is e?
In your code you are not showing a connection or a datareader.
Pgmer's code will work just fine if you replace connection string and the command.

In order to get the count into dso1 replace

ds = comm.ExecuteReader

with

dso1.text = comm.ExecuteScalar

(You will also have to remove the IF and the While)

[noobies]

I have been adding that, but it seems nothing was coming out..

Dim dsola As String

                dsola = "SELECT COUNT(alert_value) AS alert1 FROM company_alert a LEFT JOIN ref_alert_parameter b ON a.alert_id = b.alert_id WHERE b.alert_id = 'DSO' And alert_value >= para1 "
                cmd = New MySqlCommand(dsola, conn)
                dr = cmd.ExecuteReader()

                If dr.Read() Then
                    dso1.Text = dr.Item("alert1")
                End If

                dr.Close()

[Pgmer]

If you run ur Query before cmd.ExecuteReader() on SQL server are u getting any data?

[noobies]

yes i get, if i run it in sql it will count it

[adam_k]

replace this:

dr = cmd.ExecuteReader()

                If dr.Read() Then
                    dso1.Text = dr.Item("alert1")
                End If

with this:

dso1.text = cmd.ExecuteScalar

Also make sure that your connection string is correct.

[noobies]

thanks , i already got the right answer :)

[Pgmer]

Then please mark this thread as solved.