VB.NET-查詢SQL內資料時如何顯示查詢資料筆之數量

#vb.net

REX0713 2021-12-20 14:35:38 ‧ 2179 瀏覽

分享至

    Protected Sub BT_查詢2_Click(sender As Object, e As EventArgs) Handles BT_查詢2.Click
        Dim 起始日期 As String = TB2_起始日期.Text
        Dim 結束日期 As String = TB2_結束日期.Text
        Dim Cnt As Integer = CInt(Trim(Cnt_TB2.Text))
        Dim Slt As String = ""
        If Cnt = 0 Then '未輸入任何資料時，搜尋全部
            Slt = ""
        Else '有輸入資料時
            Dim i As Integer = 0
            If 起始日期 <> "" Then
                i += 1
                If i = 1 Then
                    Slt += " WHERE"
                Else
                    Slt += " AND"
                End If
                Slt += " [Date] >= '" & 起始日期 & "'"
            End If
            If 結束日期 <> "" Then
                i += 1
                If i = 1 Then
                    Slt += " WHERE"
                Else
                    Slt += " AND"
                End If
                Slt += " [Date] <= '" & 結束日期 & "'"
            End If
        End If
        SQL = "Select * FROM [UserA]" + Slt + " Order By User"
        SQL_Select.Text = SQL
        '將Select結果顯示到GridView
        SqlDataSource1.SelectCommand = SQL
        GridView.DataBind()
        SqlDataSource1.Dispose()
    End Sub
    ```
    已上為"原碼"資訊

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

souda

iT邦高手 1 級 ‧ 2021-12-20 15:03:20

再寫一個COUNT就可以哦!

COUNT (Transact-SQL)

適用範圍：是SQL Server (所有支援的版本) 是Azure SQL Database 是Azure SQL 受控執行個體是Azure Synapse Analytics 是平行處理資料倉儲

此函數會傳回群組中找到的項目數。 COUNT 的運作方式類似 COUNT_BIG 函數。這些函數唯一的差別就是其傳回值的資料類型。 COUNT 一律會傳回 int 資料類型值。 COUNT_BIG 一律會傳回 bigint 資料類型值。

https://docs.microsoft.com/zh-tw/sql/t-sql/functions/count-transact-sql?view=sql-server-ver15

所以你可以這樣做：

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    Dim con As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=xxx.accdb")

    Try
        Dim sql As String
        Dim sql1 As String
        Dim cmd As New OleDb.OleDbCommand
        Dim cmd1 As New OleDb.OleDbCommand
        Dim dt As New DataTable
        Dim da As New OleDb.OleDbDataAdapter
        Dim objCmd As New OleDb.OleDbCommand
        Dim RecordNum
        con.Open()
        
        'db table select
        sql = "Select * from tblissue"
        cmd.Connection = con
        cmd.CommandText = sql
        da.SelectCommand = cmd

        'count
        sql = "Select count(*) from tblissue"
        cmd1.Connection = con
        cmd1.CommandText = sql
        RecordNum = cmd1.ExecuteScalar()
        Label1.Text = RecordNum

        'to grid
        da.Fill(dt)
        DataGridView1.DataSource = dt            

    Catch ex As Exception
        MsgBox(ex.Message)
    Finally
        con.Close()

    End Try
End Sub

回應 4
分享
檢舉

看更多先前的回應...收起先前的回應...

REX0713 iT邦新手 5 級 ‧ 2021-12-20 15:27:03 檢舉

感謝您幫助...
只是我是新手不是很懂
你提供的程式碼是放在哪
"End Sub"的上面嗎

souda iT邦高手 1 級 ‧ 2021-12-20 15:33:20 檢舉

你直接用gridview來增加.count 顯示到label即可摟.

REX0713 iT邦新手 5 級 ‧ 2021-12-21 14:20:09 檢舉

感謝前輩的幫忙

souda iT邦高手 1 級 ‧ 2021-12-27 22:05:22 檢舉

我實際完成的碼如上

登入發表回應

海綿寶寶

iT邦大神 1 級 ‧ 2021-12-20 16:46:13

依據這篇的說明
試試看
將原

...
        SQL = "Select * FROM [UserA]" + Slt + " Order By User"
        SQL_Select.Text = SQL
        '將Select結果顯示到GridView
        SqlDataSource1.SelectCommand = SQL
        GridView.DataBind()
        SqlDataSource1.Dispose()
...

加兩列變成

...
        SQL = "Select * FROM [UserA]" + Slt + " Order By User"
        SQL_Select.Text = SQL
        '將Select結果顯示到GridView
        SqlDataSource1.SelectCommand = SQL
        GridView.DataBind()
        
        DataView dv = (DataView)SqlDataSource1.Select(DataSourceSelectArguments.Empty);
        int recordcount = (int)dv.Table.Rows[0][0];
        
        SqlDataSource1.Dispose()
...