Retrieve Count From SQL

Question

I'm trying to get the count from a select SQL sentence but no luck, how can I retrieve the value?

Here is where I call the function :

Dim sql3 As String = "select COUNT(*) as countvalue from fat_prods where id_fat=" + valor.ToString + ""
Dim dadosretornados3 As System.Data.DataTableReader = buscadadosacess(sql3)
oConn.Close()

here is the function:

Function buscadadosacess(sql As String)
  oConn.ConnectionString = strConn
  oConn.Open()
  If oConn.State = ConnectionState.Open Then
    ACommand = New OleDbCommand(sql, oConn)
    'define um dataAdapter
    AAdapter = New OleDbDataAdapter()
    AAdapter.SelectCommand = ACommand
    'define e preenche um DataTable com os dados
    ATabela = New DataTable()
    AAdapter.Fill(ATabela)
    ' associar campos a base de dados
    xy = ATabela.CreateDataReader
    ' Ler da tabela
    'linha = ACommand.ExecuteReader
  End If

  'Tipo de dados incorrecto na expressão de critérios.'
  Return xy
End Function

my question is how do i retrieve the countvalue? if it was somekinda of column in database i will be like msgbox(dadosretornados("id_fat"))

so my real question is what i need to put inside of dadosretornados(HERE) to get the value of the count?

Answer 1

Try the following:

Private Function RowCount() As Integer
    ' Declare the object to return
    Dim count As Integer = -1

    ' Declare the connection object
    Dim con As OleDbConnection

    ' Wrap code in Try/Catch
    Try
        ' Set the connection object to a new instance
        ' TODO: Change "My Connection String Here" with a valid connection string
        con = New OleDbConnection("My Connection String Here")

        ' Create a new instance of the command object
        Using cmd As OleDbCommand = New OleDbCommand("SELECT Count([id_fat]) FROM [fat_prods] WHERE [id_fat]=@id_fat", con)
            ' Paramterize the query
            cmd.Parameters.AddWithValue("@id_fat", valor)

            ' Open the connection
            con.Open()

            ' Use ExecuteScalar to return a single value
            count = Convert.ToInt32(cmd.ExecuteScalar())

            ' Close the connection
            con.Close()
        End Using
    Catch ex As Exception
        ' Display the error
        Console.WriteLine(ex.Message)
    Finally
        ' Check if the connection object was initialized
        If con IsNot Nothing Then
            If con.State = ConnectionState.Open Then
                ' Close the connection if it was left open(exception thrown)
                con.Close()
            End If

            ' Dispose of the connection object
            con.Dispose()
        End If
    End Try

    ' Return the row count
    Return count
End Function

What this function does is either return the row count or -1 if it failed. It does so by calling the ExecuteScalar on a command that returns a single (count) value.

It also cleans up any lingering objects by either using the Using statement or explicitly calling the Dispose method.