我的一个ASP.net简单查询实现

最新推荐文章于 2024-03-20 21:25:02 发布

suerliu

最新推荐文章于 2024-03-20 21:25:02 发布

阅读量1.4k

点赞数

CC 4.0 BY-SA版权

分类专栏：软件开发文章标签： asp.net string integer dataset each database

本文链接：https://blog.youkuaiyun.com/suerliu/article/details/1505437

软件开发专栏收录该内容

1 篇文章

订阅专栏

本文介绍了一种使用Visual Basic.NET从Access数据库检索数据的方法，并展示了如何通过SQL查询特定团队的工作记录，同时还包括了添加汇总行到GridView及格式化数据显示的技巧。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Imports System.Data
Imports System.Data.OleDb

.....
'Initialize the query string
Dim strName, strYear, strSQL As String
Dim rTmp As DataRow

strYear = lstYear.SelectedValue

If RadioTeam.Checked = True Then
strName = lstTeam.SelectedValue
strSQL = "SELECT * FROM QueryTeamWV WHERE TeamName = '" & strName & "' And WorkYear=" & strYear
End If

....
'Create connection to one access database, and get data

Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & Server.MapPath("App_Data/WV.mdb")

Dim objConn As New OleDbConnection(sConnectionString)
objConn.Open()

Dim objCmdSelect As New OleDbCommand(strSQL, objConn)

Dim objAdapter1 As New OleDbDataAdapter()
objAdapter1.SelectCommand = objCmdSelect

Dim objDataset1 As New DataSet()

objAdapter1.Fill(objDataset1, "wvData")

'how to add one additional row to temp table

rTmp = objDataset1.Tables(0).NewRow
rTmp("TeamName") = "Total"
rTmp("Jan") = SumJan
rTmp("Feb") = SumFeb
rTmp("Mar") = SumMar
rTmp("Apr") = SumApr
rTmp("May") = SumMay
rTmp("Jun") = SumJun
rTmp("Jul") = SumJul
。。。。
objDataset1.Tables(0).Rows.Add(rTmp)

'Display result
gvTeam.DataSource = objDataset1.Tables(0).DefaultView
gvTeam.DataBind()

'Follow procedure are called by RowDataBound event to format the data.

'display diff with red when negative
Private Sub NumNegative(ByVal gvTmp As GridView)

Dim gvr As GridViewRow

        For Each gvr In gvTmp.Rows
            If Not String.IsNullOrEmpty(gvr.Cells(gvr.Cells.Count - 1).Text) Then
                If CInt(gvr.Cells(gvr.Cells.Count - 1).Text) < 0 Then
                    gvr.Cells(gvr.Cells.Count - 1).ForeColor = Drawing.Color.Red
                End If
            End If
        Next

End Sub

    Private Sub GrouGV(ByVal gvTmp As GridView, ByVal e As System.EventArgs)
        If radioDept.Checked = True Then
            GroupRows(gvTmp, e, "Team", 1)
            GroupRows(gvTmp, e, "Dept", 0)
        Else
            GroupRows(gvTmp, e, "Name", 0)
        End If
    End Sub

    '将指定列分组合并
    Private Sub GroupRows(ByVal gvTmp As GridView, ByVal e As System.EventArgs, ByVal strType As String, ByVal intColumn As Integer)
        Dim text As String
        text = ""
        Dim count As Integer
        count = 0
        Dim ht As Hashtable
        ht = New Hashtable

' loop through all rows to get row counts
Dim gvr As GridViewRow

For Each gvr In gvTmp.Rows

If (gvr.RowType = DataControlRowType.DataRow) Then

                If (gvr.Cells(intColumn).Text = text) Then
                    count = count + 1
                Else
                    If (count > 0) Then
                        ht.Add(strType + text, count)
                    End If
                    text = gvr.Cells(intColumn).Text
                    count = 1
                End If
            End If
        Next

If (count > 1) Then ht.Add(strType + text, count)

        'loop through all rows again to set rowspan
        text = ""
        For Each gvr In gvTmp.Rows

            If (gvr.RowType = DataControlRowType.DataRow) Then
                If gvr.Cells(intColumn).Text = text Then
                    gvr.Cells.Remove(gvr.Cells(intColumn))
                Else
                    text = gvr.Cells(intColumn).Text
                    gvr.Cells(intColumn).RowSpan = Convert.ToInt32(ht(strType + text))
                End If
            End If
        Next
    End Sub

'Two small tips:
1. 如何让系统自动为Radio Button生成后台的事件代码需要将其属性：AutoPostBack=True

2. 定义grid view中数据格式，例如显示一位小数的数字：{0:N1}，需要将列属性中的HtmlEncode=False，才能生效