从SQL检索计数

2024-05-17 • 问答

我正试图从选择的SQL语句中获取计数，但是没有运气，我如何获取该值？

在这里我调用函数：

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

这是函数：

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

我的问题是如何获取计数值？如果它是数据库中列的某种形式，我将像msgbox(dadosretornados("id_fat"))

所以我真正的问题是我需要在dadosretornados（ HERE ）里面放入什么来获得计数值？

dadosretornados（“ countvalue”）

dadosretornados（0）

尝试以下操作：

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

此函数的作用是返回行计数，如果失败则返回-1。通过在返回单个（计数）值的命令上调用ExecuteScalar来实现。

它也可以使用Using语句或显式调用Dispose方法来清理所有残留的对象。

从SQL检索计数

gwt3344 回答：从SQL检索计数

大家都在问