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

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

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

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

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..

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)

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

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

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

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.

thanks , i already got the right answer :)

Then please mark this thread as solved.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.