form1.vb

VB Accese常用操作VB Accese常用操作VB Accese常用操作VB Accese常用操作

Imports System.Data.OleDb
Imports System.Xml




Public Class Form1
    
    Dim strCon As String = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source =db1.mdb"

    Dim strCom1 As String = " SELECT id, xm as 姓名, xb as 性别 ,nl as 年龄 ,zip as 邮政编号  FROM person  order by id "





    Public Sub bind_data()
        '绑定数据到gridview
        DataGridView1.ReadOnly = True
        Dim myDataSet As DataSet

        Dim myConn As OleDbConnection = New OleDbConnection()
        myConn.ConnectionString = strCon

        '创建一个 DataSet
        myDataSet = New DataSet()


        myConn.Open()
        '通过OleDbDataAdapter对象得到一个数据集
        Dim myCommand As OleDbDataAdapter = New OleDbDataAdapter(strCom1, myConn)
        '把Dataset绑定books数据表
        myCommand.Fill(myDataSet, "person")

        DataGridView1.DataSource = myDataSet.Tables(0)
        DataGridView1.Columns(0).Visible = False

        myConn.Close()
        '创建BindingManagerBase对象
        'myBind = Me.BindingContext(myDataSet, "person")

    End Sub


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

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        '退出
        Application.Exit()
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        '新增记录
        Dim Frm2 As New Form2()

        Frm2.TextBox1.Tag = "New"
        If (Frm2.ShowDialog() = DialogResult.OK) Then
            bind_data()
        End If
    End Sub

    Private Sub del_record()
        Dim i As Integer = DataGridView1.Rows.Count
        For i = 0 To DataGridView1.Rows.Count - 1
            If DataGridView1.Rows(i).Selected Then
                'MessageBox.Show(DataGridView1.Rows(i).Cells(1).Value)
                Del_Record_DataBase(DataGridView1.Rows(i).Cells(0).Value)
                i = DataGridView1.Rows.Count + 1
            End If
        Next i
    End Sub

    Private Sub Del_Record_DataBase(ByVal id As String)
        '根据学号删除记录
        Dim myConn As OleDbConnection = New OleDbConnection(strCon)
        myConn.Open()
        Dim strDele As String = "DELETE FROM person WHERE id= '" + id.Trim() + "'"
        Dim myCommand As OleDbCommand = New OleDbCommand(strDele, myConn)
        '从数据库中删除指定记录
        myCommand.ExecuteNonQuery()
        myConn.Close()
        '更新datagridview
        bind_data()

    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        '删除学生
        If MessageBox.Show("确定要删除吗？", "提示", MessageBoxButtons.YesNo) = DialogResult.Yes Then
            del_record()
        End If

    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        update_Rercord()
    End Sub


    Private Sub update_Rercord()
        '修改
        Dim Frm2 As New Form2()
        Dim i As Integer = DataGridView1.Rows.Count
        For i = 0 To DataGridView1.Rows.Count - 1
            If DataGridView1.Rows(i).Selected Then
                'MessageBox.Show(DataGridView1.Rows(i).Cells(1).Value)
                'Del_Record_DataBase(DataGridView1.Rows(i).Cells(0).Value)
                'i = DataGridView1.Rows.Count + 1
                '把编号传过去 为了修改
                Frm2.TextBox2.Tag = DataGridView1.Rows(i).Cells(0).Value
                i = DataGridView1.Rows.Count + 1
            End If
        Next i
        Frm2.TextBox1.Tag = "Modify"
        If (Frm2.ShowDialog() = DialogResult.OK) Then
            bind_data()
        End If
    End Sub

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
        bind_data()

    End Sub

    Private Sub DataGridView1_MouseDoubleClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles DataGridView1.MouseDoubleClick
        If DataGridView1.RowCount > 0 Then
            update_Rercord()
        End If
    End Sub

    Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnToExcel.Click
        '导出
        Dim strCom2 As String = " SELECT xm as 姓名, xb as 性别 ,nl as 年龄 ,zip as 邮政编号  FROM person  order by id "

        Dim myDataSet As DataSet

        Dim myConn As OleDbConnection = New OleDbConnection()
        myConn.ConnectionString = strCon

        '创建一个 DataSet
        myDataSet = New DataSet()


        myConn.Open()
        '通过OleDbDataAdapter对象得到一个数据集
        Dim myCommand As OleDbDataAdapter = New OleDbDataAdapter(strCom2, myConn)
        '把Dataset绑定books数据表
        myCommand.Fill(myDataSet, "person")
        myConn.Close()

        ToExcel(myDataSet)
        MessageBox.Show("导出完成")


    End Sub

    Private Sub ToExcel(ByVal ds As DataSet)
        Dim fs As System.IO.FileStream = New System.IO.FileStream("C:\\person.xml", System.IO.FileMode.Create)
        Dim xtw As System.Xml.XmlTextWriter = New System.Xml.XmlTextWriter(fs, System.Text.Encoding.Unicode)
        xtw.WriteProcessingInstruction("xml", "version='1.0'")

        ds.WriteXml(xtw)
        xtw.Close()
    End Sub

End Class