'//
'// Name:用VB.Net读写数据库
'// Author:Q&f
'// Email:[email protected]
'//

Imports System.Data.Odbc  ' [/ Imports System.Data.OleDb/ Imports System.Data.SqlClient(三者在使用方法上基本上相同)]
 
    '// 显示数据
    Private Sub ShowData()
        Dim Cnn As OdbcConnection
        Dim Cmd As OdbcCommand
        Dim Da As OdbcDataAdapter
        Dim Ds As DataSet
        Dim intIndex As Integer

        '// 连接到数据库
        Cnn = New OdbcConnection
        Cnn.ConnectionString = "PROVIDER=MSDASQL;DRIVER={Microsoft Access Driver (*.MDB)};" & _
                            "DBQ=" & Server.MapPath("database/database.mdb") & ";UID=;PWD=;"       
        Cnn.Open()

        Cmd = New OdbcCommand
        Cmd.Connection = Cnn
        Cmd.CommandType = CommandType.Text

        Da = New OdbcDataAdapter
        Ds = New DataSet
        Da.SelectCommand = Cmd

        '// 注意以下语句可以将不同SQL语句执行结果添加到同一个表中
        Da.SelectCommand.CommandText = "SELECT ID,Title,Issuer,IssueDate,DepName AS Range " & _
                                       "FROM IssueInforTable,DepartmentTable " & _
                                       "WHERE IssueInforTable.DepID = DepartmentTable.DepID"
        Da.Fill(Ds, "IssueInforTable")
        Da.SelectCommand.CommandText = "SELECT ID,Title,Content,Issuer,IssueDate,Range " & _
                                       "FROM IssueInforTable WHERE Range ='ALL'"
        Da.Fill(Ds, "IssueInforTable")

        If Ds.Tables("IssueInforTable").Rows.Count > 0 Then
            DataGrid1.EditItemIndex = -1
            DataGrid1.CurrentPageIndex = 0
            DataGrid1.DataSource = Ds
            DataGrid1.DataBind()
            txtPage.Text = CStr(DataGrid1.CurrentPageIndex + 1)
            lblInfor.Text = "记录总数:" & Ds.Tables("IssueInforTable").Rows.Count & _
                            "条.当前,第" & DataGrid1.CurrentPageIndex + 1 & "页,总计" & DataGrid1.PageCount & "页."
        Else
            CreateTempTable()
            txtPage.Text = CStr(DataGrid1.CurrentPageIndex + 1)
            lblInfor.Text = "没有找到任何相关记录"
        End If
        '// 保存数据集
        Session("DataSet") = Ds
        '// 保存记录总数
        Session("RecordCount") = Ds.Tables("IssueInforTable").Rows.Count

        Ds.Dispose()
        Da.Dispose()
        Cmd.Dispose()
        Cnn.Dispose()

    End Sub

  

    '// 显示数据
    Private Sub ShowData1()
        Dim Cnn As OdbcConnection
        Dim Cmd As OdbcCommand
        Dim Da As OdbcDataAdapter
        Dim Ds As DataSet
        Dim strSQL As String

        '// 连接到数据库
        Cnn = New OdbcConnection
        Cnn.ConnectionString = "PROVIDER=MSDASQL;DRIVER={Microsoft Access Driver (*.MDB)};" & _
                               "DBQ=" & Server.MapPath("database/database.mdb") & ";UID=;PWD=;"       
        Cnn.Open()

        strSQL = "SELECT ID,Title,Issuer,IssueDate FROM IssueInforTable ORDER BY IssueDate DESC"
        Cmd = New OdbcCommand
        Cmd.Connection = Cnn
        Cmd.CommandType = CommandType.Text
        Cmd.CommandText = strSQL

        Da = New OdbcDataAdapter
        Da.SelectCommand = Cmd
        Ds = New DataSet

        Da.Fill(Ds, "IssueInforTable")

        If Ds.Tables("IssueInforTable").Rows.Count > 0 Then
            DataGrid1.EditItemIndex = -1
            DataGrid1.CurrentPageIndex = 0
            DataGrid1.DataSource = Ds
            DataGrid1.DataBind()
            txtPage.Text = CStr(DataGrid1.CurrentPageIndex + 1)
            lblInfor.Text = "记录总数:" & Ds.Tables("IssueInforTable").Rows.Count & _
                            "条.当前,第" & DataGrid1.CurrentPageIndex + 1 & "页,总计" & DataGrid1.PageCount & "页."
        Else
            DataGrid1.EditItemIndex = -1
            DataGrid1.CurrentPageIndex = 0
            DataGrid1.DataSource = Ds
            DataGrid1.DataBind()
            txtPage.Text = CStr(DataGrid1.CurrentPageIndex + 1)
            lblInfor.Text = "没有找到任何相关记录"
        End If

        '// 保存数据集
        Session("DataSet") = Ds
        '// 保存记录总数
        Session("RecordCount") = Ds.Tables("IssueInforTable").Rows.Count

        Ds.Dispose()
        Da.Dispose()
        Cmd.Dispose()
        Cnn.Dispose()

    End Sub

   

    '// 创建一个临时表，用于添加内容
    Private Sub CreateTempTable()
        Dim Dt As DataTable
        Dim Dr As DataRow

        '// 创建数据表
        Dt = New DataTable
        Dt.Columns.Add(New DataColumn("ID", GetType(String)))
        Dt.Columns.Add(New DataColumn("Title", GetType(String)))
        Dt.Columns.Add(New DataColumn("Issuer", GetType(String)))
        Dt.Columns.Add(New DataColumn("IssueDate", GetType(String)))
        Dt.Columns.Add(New DataColumn("Range", GetType(String)))

        '// 生成一新行
        Dr = Dt.NewRow()
        Dr(0) = "-"
        Dr(1) = "-"
        Dr(2) = "-"
        Dr(3) = "-"
        Dr(4) = "-"
        '// 向数据表添加行
        Dt.Rows.Add(Dr)

        '// 绑定数据源
        DataGrid1.EditItemIndex = -1
        DataGrid1.CurrentPageIndex = 0
        DataGrid1.DataSource = New DataView(Dt)
        DataGrid1.DataBind()

        Dr = Nothing
        Dt = Nothing

    End Sub

    '// 分页显示,页面改变
    Private Sub DataGrid1_PageIndexChanged(ByVal source As Object, & _
        ByVal e As System.Web.UI.WebControls.DataGridPageChangedEventArgs) Handles DataGrid1.PageIndexChanged
        On Error Resume Next

        DataGrid1.CurrentPageIndex = e.NewPageIndex
        DataGrid1.DataSource = CType(Session("DataSet"), DataSet)
        DataGrid1.DataBind()
        txtPage.Text = CStr(DataGrid1.CurrentPageIndex + 1)
        lblInfor.Text = "记录总数:" & Session("RecordCount") & "条.当前,第" &  _
        DataGrid1.CurrentPageIndex + 1 & "页,总计" & DataGrid1.PageCount & "页."

    End Sub

    '// 跳转到指定页
    Private Sub btnGo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGo.Click
        On Error Resume Next

        If IsNumeric(txtPage.Text.Trim) = True Then
            Dim intPage As Integer
            intPage = (CInt(txtPage.Text.Trim))
            If intPage > 0 And intPage <= DataGrid1.PageCount Then
                DataGrid1.CurrentPageIndex = intPage - 1
                DataGrid1.DataSource = CType(Session("DataSet"), DataSet)
                DataGrid1.DataBind()
            End If
        End If
        txtPage.Text = CStr(DataGrid1.CurrentPageIndex + 1)
        lblInfor.Text = "记录总数:" & Session("RecordCount") & "条.当前,第" &  _
        DataGrid1.CurrentPageIndex + 1 & "页,总计" & DataGrid1.PageCount & "页."

    End Sub

    '// 第一页
    Private Sub btnFirstPage_Click(ByVal sender As System.Object, & _
        ByVal e As System.EventArgs) Handles btnFirstPage.Click
        On Error Resume Next

        DataGrid1.CurrentPageIndex = 0
        DataGrid1.DataSource = CType(Session("DataSet"), DataSet)
        DataGrid1.DataBind()
        txtPage.Text = CStr(DataGrid1.CurrentPageIndex + 1)
        lblInfor.Text = "记录总数:" & Session("RecordCount") & "条.当前,第" &  _
        DataGrid1.CurrentPageIndex + 1 & "页,总计" & DataGrid1.PageCount & "页."

    End Sub

    '// 上一页
    Private Sub btnPrePage_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrePage.Click
        On Error Resume Next
        Dim intPage As Integer

        intPage = DataGrid1.CurrentPageIndex
        intPage = intPage - 1
        If intPage <= 0 Then
            DataGrid1.CurrentPageIndex = 0
            DataGrid1.DataSource = CType(Session("DataSet"), DataSet)
            DataGrid1.DataBind()
        Else
            DataGrid1.CurrentPageIndex = intPage
            DataGrid1.DataSource = CType(Session("DataSet"), DataSet)
            DataGrid1.DataBind()
        End If
        txtPage.Text = CStr(DataGrid1.CurrentPageIndex + 1)
        lblInfor.Text = "记录总数:" & Session("RecordCount") & "条.当前,第" & _
                     DataGrid1.CurrentPageIndex + 1 & "页,总计" & DataGrid1.PageCount & "页."

    End Sub

    '// 下一页
    Private Sub btnNextPage_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNextPage.Click
        On Error Resume Next
        Dim intPage As Integer

        intPage = DataGrid1.CurrentPageIndex
        intPage = intPage + 1
        If intPage >= DataGrid1.PageCount Then
            DataGrid1.CurrentPageIndex = DataGrid1.PageCount - 1
            DataGrid1.DataSource = CType(Session("DataSet"), DataSet)
            DataGrid1.DataBind()
        Else
            DataGrid1.CurrentPageIndex = intPage
            DataGrid1.DataSource = CType(Session("DataSet"), DataSet)
            DataGrid1.DataBind()
        End If
        txtPage.Text = CStr(DataGrid1.CurrentPageIndex + 1)
        lblInfor.Text = "记录总数:" & Session("RecordCount") & "条.当前,第" &  _
        DataGrid1.CurrentPageIndex + 1 & "页,总计" & DataGrid1.PageCount & "页."

    End Sub

    '// 最后一页
    Private Sub btnLastPage_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLastPage.Click
        On Error Resume Next

        DataGrid1.CurrentPageIndex = DataGrid1.PageCount - 1
        DataGrid1.DataSource = CType(Session("DataSet"), DataSet)
        DataGrid1.DataBind()
        txtPage.Text = CStr(DataGrid1.CurrentPageIndex + 1)
        lblInfor.Text = "记录总数:" & Session("RecordCount") & "条.当前,第" &  _
        DataGrid1.CurrentPageIndex + 1 & "页,总计" & DataGrid1.PageCount & "页."

    End Sub

    '// 验证用户是否存在
    Public Function UserLoginVerifying(ByVal strUserName As String, ByVal strPassword As String) As Boolean
        On Error GoTo ErrorHandler
        Dim Cnn As OdbcConnection
        Dim Cmd As OdbcCommand
        Dim Dr As OdbcDataReader
        Dim Flag As Boolean

        Cnn = New OdbcConnection
        Cnn.ConnectionString = "PROVIDER=MSDASQL;DRIVER={Microsoft Access Driver (*.MDB)};" & _
                               "DBQ=" & Server.MapPath("database/database.mdb") & ";UID=;PWD=;"       
        Cnn.Open()

        Cmd = New OdbcCommand
        Cmd.Connection = Cnn
        Cmd.CommandType = CommandType.Text
        Cmd.CommandText = "SELECT UserID,UserName,UserPwd,DepartmentTable.DepID AS DepID,DepName FROM " & _
                           "UserInforTable,DepartmentTable " & _
                           "WHERE UserInforTable.DepID=DepartmentTable.DepID"
        Dr = Cmd.ExecuteReader()
        If Dr.HasRows = True Then
            Flag = False
            Do While Dr.Read
                If (StrComp(Trim(Dr.GetString(1)), Trim(strUserName), CompareMethod.Binary) = 0) And _
                   (StrComp(Trim(Dr.GetString(2)), Trim(strPassword), CompareMethod.Binary) = 0) Then
                    Flag = True
                    Exit Do
                End If
            Loop
        Else   
            Flag = False
        End If
        
        Dr.Close()
        Cmd.Dispose()
        Cnn.Dispose()

        If Flag = True Then
            Return True
        Else
            Return False
        End If

     End Function

 

评论 (0) All

登陆 还没注册？