vb.net-连接数据库实例

Imports System.Data.OleDb
Imports System.Data

Public Class Class1

    Inherits System.Windows.Forms.Form
    Public ADOcmd As OleDbDataAdapter
    Public ds As DataSet = New DataSet() '建立DataSet对象
    Public mytable As Data.DataTable '建立表单对象
    Public myrow As Data.DataRow '建立数据行对象
    Public rownumber As Integer '用来记录当前行数
    Public SearchSQL As String 'sql字符串
    Public cmd As OleDbCommandBuilder 'sql命令建立者

    '======================================================
#Region "   Windows   窗体设计器生成的代码   "

 

    'Form 重写 Dispose，以清理组件列表。
    <System.Diagnostics.DebuggerNonUserCode()> _
    Protected Overrides Sub Dispose(ByVal disposing As Boolean)
        Try
            If disposing AndAlso components IsNot Nothing Then
                components.Dispose()
            End If
        Finally
            MyBase.Dispose(disposing)
        End Try
    End Sub

    'Windows 窗体设计器所必需的
    Private components As System.ComponentModel.IContainer

    '注意: 以下过程是 Windows 窗体设计器所必需的
    '可以使用 Windows 窗体设计器修改它。
    '不要使用代码编辑器修改它。
    <System.Diagnostics.DebuggerStepThrough()> _
    Private Sub InitializeComponent()
        Me.TextBox1 = New System.Windows.Forms.TextBox
        Me.TxtSID = New System.Windows.Forms.Label
        Me.Label2 = New System.Windows.Forms.Label
        Me.TxtName = New System.Windows.Forms.TextBox
        Me.ComboSex = New System.Windows.Forms.ComboBox
        Me.Label1 = New System.Windows.Forms.Label
        Me.TxtBornDate = New System.Windows.Forms.TextBox
        Me.Label3 = New System.Windows.Forms.Label
        Me.Label4 = New System.Windows.Forms.Label
        Me.TxtClassno = New System.Windows.Forms.TextBox
        Me.Label5 = New System.Windows.Forms.Label
        Me.TxtRuDate = New System.Windows.Forms.TextBox
        Me.Label6 = New System.Windows.Forms.Label
        Me.TxtTel = New System.Windows.Forms.TextBox
        Me.TxtAddress = New System.Windows.Forms.TextBox
        Me.Label7 = New System.Windows.Forms.Label
        Me.TxtComment = New System.Windows.Forms.TextBox
        Me.Label8 = New System.Windows.Forms.Label
        Me.BtFirst = New System.Windows.Forms.Button
        Me.BtPrev = New System.Windows.Forms.Button
        Me.BtNext = New System.Windows.Forms.Button
        Me.BtLastt = New System.Windows.Forms.Button
        Me.BtDelete = New System.Windows.Forms.Button
        Me.BtModify = New System.Windows.Forms.Button
        Me.BtUpdate = New System.Windows.Forms.Button
        Me.BtCancel = New System.Windows.Forms.Button
        Me.SuspendLayout()
        '
        'TextBox1
        '
        Me.TextBox1.Location = New System.Drawing.Point(80, 26)
        Me.TextBox1.Name = "TextBox1"
        Me.TextBox1.Size = New System.Drawing.Size(100, 21)
        Me.TextBox1.TabIndex = 0
        '
        'TxtSID
        '
        Me.TxtSID.AutoSize = True
        Me.TxtSID.Location = New System.Drawing.Point(27, 29)
        Me.TxtSID.Name = "TxtSID"
        Me.TxtSID.Size = New System.Drawing.Size(41, 12)
        Me.TxtSID.TabIndex = 1
        Me.TxtSID.Text = "TxtSID"
        '
        'Label2
        '
        Me.Label2.AutoSize = True
        Me.Label2.Location = New System.Drawing.Point(27, 71)
        Me.Label2.Name = "Label2"
        Me.Label2.Size = New System.Drawing.Size(47, 12)
        Me.Label2.TabIndex = 3
        Me.Label2.Text = "TxtName"
        '
        'TxtName
        '
        Me.TxtName.Location = New System.Drawing.Point(80, 68)
        Me.TxtName.Name = "TxtName"
        Me.TxtName.Size = New System.Drawing.Size(100, 21)
        Me.TxtName.TabIndex = 2
        '
        'ComboSex
        '
        Me.ComboSex.FormattingEnabled = True
        Me.ComboSex.Location = New System.Drawing.Point(80, 114)
        Me.ComboSex.Name = "ComboSex"
        Me.ComboSex.Size = New System.Drawing.Size(121, 20)
        Me.ComboSex.TabIndex = 4
        '
        'Label1
        '
        Me.Label1.AutoSize = True
        Me.Label1.Location = New System.Drawing.Point(21, 117)
        Me.Label1.Name = "Label1"
        Me.Label1.Size = New System.Drawing.Size(53, 12)
        Me.Label1.TabIndex = 5
        Me.Label1.Text = "ComboSex"
        '
        'TxtBornDate
        '
        Me.TxtBornDate.Location = New System.Drawing.Point(80, 160)
        Me.TxtBornDate.Name = "TxtBornDate"
        Me.TxtBornDate.Size = New System.Drawing.Size(100, 21)
        Me.TxtBornDate.TabIndex = 6
        '
        'Label3
        '
        Me.Label3.AutoSize = True
        Me.Label3.Location = New System.Drawing.Point(3, 160)
        Me.Label3.Name = "Label3"
        Me.Label3.Size = New System.Drawing.Size(71, 12)
        Me.Label3.TabIndex = 7
        Me.Label3.Text = "TxtBornDate"
        '
        'Label4
        '
        Me.Label4.AutoSize = True
        Me.Label4.Location = New System.Drawing.Point(3, 208)
        Me.Label4.Name = "Label4"
        Me.Label4.Size = New System.Drawing.Size(65, 12)
        Me.Label4.TabIndex = 9
        Me.Label4.Text = "TxtClassno"
        '
        'TxtClassno
        '
        Me.TxtClassno.Location = New System.Drawing.Point(80, 205)
        Me.TxtClassno.Name = "TxtClassno"
        Me.TxtClassno.Size = New System.Drawing.Size(100, 21)
        Me.TxtClassno.TabIndex = 8
        '
        'Label5
        '
        Me.Label5.AutoSize = True
        Me.Label5.Location = New System.Drawing.Point(9, 246)
        Me.Label5.Name = "Label5"
        Me.Label5.Size = New System.Drawing.Size(59, 12)
        Me.Label5.TabIndex = 11
        Me.Label5.Text = "TxtRuDate"
        '
        'TxtRuDate
        '
        Me.TxtRuDate.Location = New System.Drawing.Point(80, 243)
        Me.TxtRuDate.Name = "TxtRuDate"
        Me.TxtRuDate.Size = New System.Drawing.Size(100, 21)
        Me.TxtRuDate.TabIndex = 10
        '
        'Label6
        '
        Me.Label6.AutoSize = True
        Me.Label6.Location = New System.Drawing.Point(21, 289)
        Me.Label6.Name = "Label6"
        Me.Label6.Size = New System.Drawing.Size(41, 12)
        Me.Label6.TabIndex = 12
        Me.Label6.Text = "TxtTel"
        '
        'TxtTel
        '
        Me.TxtTel.Location = New System.Drawing.Point(80, 286)
        Me.TxtTel.Name = "TxtTel"
        Me.TxtTel.Size = New System.Drawing.Size(100, 21)
        Me.TxtTel.TabIndex = 13
        '
        'TxtAddress
        '
        Me.TxtAddress.Location = New System.Drawing.Point(80, 327)
        Me.TxtAddress.Name = "TxtAddress"
        Me.TxtAddress.Size = New System.Drawing.Size(100, 21)
        Me.TxtAddress.TabIndex = 15
        '
        'Label7
        '
        Me.Label7.AutoSize = True
        Me.Label7.Location = New System.Drawing.Point(9, 327)
        Me.Label7.Name = "Label7"
        Me.Label7.Size = New System.Drawing.Size(65, 12)
        Me.Label7.TabIndex = 14
        Me.Label7.Text = "TxtAddress"
        '
        'TxtComment
        '
        Me.TxtComment.Location = New System.Drawing.Point(80, 371)
        Me.TxtComment.Name = "TxtComment"
        Me.TxtComment.Size = New System.Drawing.Size(100, 21)
        Me.TxtComment.TabIndex = 17
        '
        'Label8
        '
        Me.Label8.AutoSize = True
        Me.Label8.Location = New System.Drawing.Point(9, 374)
        Me.Label8.Name = "Label8"
        Me.Label8.Size = New System.Drawing.Size(65, 12)
        Me.Label8.TabIndex = 16
        Me.Label8.Text = "TxtComment"
        '
        'BtFirst
        '
        Me.BtFirst.Location = New System.Drawing.Point(221, 20)
        Me.BtFirst.Name = "BtFirst"
        Me.BtFirst.Size = New System.Drawing.Size(82, 30)
        Me.BtFirst.TabIndex = 18
        Me.BtFirst.Text = "BtFirst"
        Me.BtFirst.UseVisualStyleBackColor = True
        '
        'BtPrev
        '
        Me.BtPrev.Location = New System.Drawing.Point(221, 65)
        Me.BtPrev.Name = "BtPrev"
        Me.BtPrev.Size = New System.Drawing.Size(82, 30)
        Me.BtPrev.TabIndex = 19
        Me.BtPrev.Text = "BtPrev"
        Me.BtPrev.UseVisualStyleBackColor = True
        '
        'BtNext
        '
        Me.BtNext.Location = New System.Drawing.Point(221, 114)
        Me.BtNext.Name = "BtNext"
        Me.BtNext.Size = New System.Drawing.Size(82, 30)
        Me.BtNext.TabIndex = 20
        Me.BtNext.Text = "BtNext"
        Me.BtNext.UseVisualStyleBackColor = True
        '
        'BtLastt
        '
        Me.BtLastt.Location = New System.Drawing.Point(221, 160)
        Me.BtLastt.Name = "BtLastt"
        Me.BtLastt.Size = New System.Drawing.Size(82, 30)
        Me.BtLastt.TabIndex = 21
        Me.BtLastt.Text = "BtLastt"
        Me.BtLastt.UseVisualStyleBackColor = True
        '
        'BtDelete
        '
        Me.BtDelete.Location = New System.Drawing.Point(221, 208)
        Me.BtDelete.Name = "BtDelete"
        Me.BtDelete.Size = New System.Drawing.Size(82, 30)
        Me.BtDelete.TabIndex = 22
        Me.BtDelete.Text = "BtDelete"
        Me.BtDelete.UseVisualStyleBackColor = True
        '
        'BtModify
        '
        Me.BtModify.Location = New System.Drawing.Point(221, 258)
        Me.BtModify.Name = "BtModify"
        Me.BtModify.Size = New System.Drawing.Size(82, 30)
        Me.BtModify.TabIndex = 23
        Me.BtModify.Text = "BtModify"
        Me.BtModify.UseVisualStyleBackColor = True
        '
        'BtUpdate
        '
        Me.BtUpdate.Location = New System.Drawing.Point(221, 304)
        Me.BtUpdate.Name = "BtUpdate"
        Me.BtUpdate.Size = New System.Drawing.Size(82, 30)
        Me.BtUpdate.TabIndex = 24
        Me.BtUpdate.Text = "BtUpdate"
        Me.BtUpdate.UseVisualStyleBackColor = True
        '
        'BtCancel
        '
        Me.BtCancel.Location = New System.Drawing.Point(221, 352)
        Me.BtCancel.Name = "BtCancel"
        Me.BtCancel.Size = New System.Drawing.Size(82, 30)
        Me.BtCancel.TabIndex = 25
        Me.BtCancel.Text = "BtCancel"
        Me.BtCancel.UseVisualStyleBackColor = True
        '
        'BtLast
        '
        Me.AutoScaleDimensions = New System.Drawing.SizeF(6.0!, 12.0!)
        Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
        Me.ClientSize = New System.Drawing.Size(324, 418)
        Me.Controls.Add(Me.BtCancel)
        Me.Controls.Add(Me.BtUpdate)
        Me.Controls.Add(Me.BtModify)
        Me.Controls.Add(Me.BtDelete)
        Me.Controls.Add(Me.BtLastt)
        Me.Controls.Add(Me.BtNext)
        Me.Controls.Add(Me.BtPrev)
        Me.Controls.Add(Me.BtFirst)
        Me.Controls.Add(Me.TxtComment)
        Me.Controls.Add(Me.Label8)
        Me.Controls.Add(Me.TxtAddress)
        Me.Controls.Add(Me.Label7)
        Me.Controls.Add(Me.TxtTel)
        Me.Controls.Add(Me.Label6)
        Me.Controls.Add(Me.Label5)
        Me.Controls.Add(Me.TxtRuDate)
        Me.Controls.Add(Me.Label4)
        Me.Controls.Add(Me.TxtClassno)
        Me.Controls.Add(Me.Label3)
        Me.Controls.Add(Me.TxtBornDate)
        Me.Controls.Add(Me.Label1)
        Me.Controls.Add(Me.ComboSex)
        Me.Controls.Add(Me.Label2)
        Me.Controls.Add(Me.TxtName)
        Me.Controls.Add(Me.TxtSID)
        Me.Controls.Add(Me.TextBox1)
        Me.Name = "BtLast"
        Me.Text = "BtLast"
        Me.ResumeLayout(False)
        Me.PerformLayout()

    End Sub

    Friend WithEvents TextBox1 As System.Windows.Forms.TextBox
    Friend WithEvents TxtSID As System.Windows.Forms.Label
    Friend WithEvents Label2 As System.Windows.Forms.Label
    Friend WithEvents TxtName As System.Windows.Forms.TextBox
    Friend WithEvents ComboSex As System.Windows.Forms.ComboBox
    Friend WithEvents Label1 As System.Windows.Forms.Label
    Friend WithEvents TxtBornDate As System.Windows.Forms.TextBox
    Friend WithEvents Label3 As System.Windows.Forms.Label
    Friend WithEvents Label4 As System.Windows.Forms.Label
    Friend WithEvents TxtClassno As System.Windows.Forms.TextBox
    Friend WithEvents Label5 As System.Windows.Forms.Label
    Friend WithEvents TxtRuDate As System.Windows.Forms.TextBox
    Friend WithEvents Label6 As System.Windows.Forms.Label
    Friend WithEvents TxtTel As System.Windows.Forms.TextBox
    Friend WithEvents TxtAddress As System.Windows.Forms.TextBox
    Friend WithEvents Label7 As System.Windows.Forms.Label
    Friend WithEvents TxtComment As System.Windows.Forms.TextBox
    Friend WithEvents Label8 As System.Windows.Forms.Label
    Friend WithEvents BtFirst As System.Windows.Forms.Button
    Friend WithEvents BtPrev As System.Windows.Forms.Button
    Friend WithEvents BtNext As System.Windows.Forms.Button
    Friend WithEvents BtLastt As System.Windows.Forms.Button
    Friend WithEvents BtDelete As System.Windows.Forms.Button
    Friend WithEvents BtModify As System.Windows.Forms.Button
    Friend WithEvents BtUpdate As System.Windows.Forms.Button
    Friend WithEvents BtCancel As System.Windows.Forms.Button

#End Region
    '======================================================

    Private Sub Class1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
        '窗体的载入
        TxtSID.Enabled = False
        TxtName.Enabled = False
        ComboSex.Enabled = False
        TxtBornDate.Enabled = False
        TxtClassno.Enabled = False
        TxtRuDate.Enabled = False
        TxtTel.Enabled = False
        TxtAddress.Enabled = False
        TxtComment.Enabled = False       '设置信息为只读
        Dim tablename As String = "Student"
        SearchSQL = "select   *   from   Student"
        ExecuteSQL(SearchSQL, tablename) '打开数据库,取得结果集的第一行
        ShowDate() '在窗体的textbox中显示数据

      
    End Sub

    Private Sub ShowDate()
        '在窗体的textbox中显示数据
        myrow = mytable.Rows.Item(rownumber) '取得第n行数据
        TxtSID.Text = myrow.Item(0).ToString
        TxtName.Text = myrow.Item(1).ToString
        ComboSex.Text = myrow.Item(2).ToString
        TxtBornDate.Text = myrow.Item(3).ToString
        TxtClassno.Text = myrow.Item(4).ToString
        TxtTel.Text = myrow.Item(5).ToString
        TxtRuDate.Text = myrow.Item(6).ToString
        TxtAddress.Text = myrow.Item(7).ToString
        TxtComment.Text = myrow.Item(8).ToString

    End Sub

    Public Function ExecuteSQL(ByVal SQL As String, ByVal table As String)
        Try
            '建立ADODataSetCommand对象
            '数据库查询函数
            ADOcmd = New OleDbDataAdapter(SQL, "Provider=SQLOLEDB;SERVER=XP-201206180939;uid=sa;pwd=5201314;database=Student_Info")

            '建立ADODataSetCommand对象
            ADOcmd.Fill(ds, table) '取得表单
            mytable = ds.Tables.Item(0) '取得名为table的表
            rownumber = 0 '设置成第一行
            myrow = mytable.Rows.Item(rownumber) '取得第一行数据
            Return True
        Catch
            MsgBox(Err.Description)
            Return False
        End Try
    End Function

    Private Sub BtFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtFirst.Click
        '指向第一条数据
        rownumber = 0
        ShowDate()

    End Sub

    Private Sub BtPrev_click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtPrev.Click
        '指向上一条数据
        BtNext.Enabled = True
        rownumber = rownumber - 1
        If rownumber < 0 Then
            rownumber = 0 '如果达到记录的首部，行号设为0
            BtPrev.Enabled = False
        End If
        ShowDate()
    End Sub

    Private Sub BtNext_click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtNext.Click
        '指向下一条数据
        BtPrev.Enabled = True
        rownumber = rownumber + 1
        If rownumber > mytable.Rows.Count - 1 Then
            rownumber = mytable.Rows.Count - 1 '如果最后一条数据，行号设为最后一行的行号
            BtNext.Enabled = False
        End If
        ShowDate()
    End Sub

    Private Sub BtLastt_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtLastt.Click
        '指向最后一条数据
        rownumber = mytable.Rows.Count - 1
        ShowDate()

    End Sub

    Private Sub BtDelete_click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtDelete.Click
        mytable.Rows.Item(rownumber).Delete() '删除记录
        If MsgBox("确定要删除记录吗？", MsgBoxStyle.OkCancel + vbExclamation, "警告") = MsgBoxResult.Ok Then
            cmd = New OleDbCommandBuilder(ADOcmd)
            '使用自动生成的SQL语句
            ADOcmd.Update(ds, "Student")
            BtNext.PerformClick()

        End If
    End Sub

    Private Sub BtModify_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtModify.Click

        TxtSID.Enabled = False
        TxtName.Enabled = True
        ComboSex.Enabled = True
        TxtBornDate.Enabled = True
        TxtClassno.Enabled = True
        TxtRuDate.Enabled = True
        TxtTel.Enabled = True
        TxtAddress.Enabled = True
        TxtComment.Enabled = True

    End Sub

    Private Sub BtUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtUpdate.Click
        'If Not Testtxt(TxtName.Text) Then
        '    MsgBox("请输入姓名！ ", vbOKOnly + vbExclamation, "警告 ")
        '    TxtName.Focus()
        '    Exit Sub
        'End If
        'If Not Testtxt(ComboSex.Text) Then
        '    MsgBox("请选择性别！ ", vbOKOnly + vbExclamation, "警告 ")
        '    ComboSex.Focus()
        '    Exit Sub
        'End If
        'If Not Testtxt(TxtClassno.Text) Then
        '    MsgBox("请选择班号！ ", vbOKOnly + vbExclamation, "警告 ")
        '    TxtClassno.Focus()
        '    Exit Sub
        'End If

        'If Not Testtxt(TxtTel.Text) Then
        '    MsgBox("请输入联系电话！ ", vbOKOnly + vbExclamation, "警告 ")
        '    TxtTel.Focus()
        '    Exit Sub
        'End If
        'If Not Testtxt(TxtAddress.Text) Then
        '    MsgBox("请输入家庭住址！ ", vbOKOnly + vbExclamation, "警告 ")
        '    TxtAddress.Focus()
        '    Exit Sub
        'End If
        'If Not IsNumeric(Trim(TxtSID.Text)) Then
        '    MsgBox("请输入数字学号！ ", vbOKOnly + vbExclamation, "警告 ")
        '    Exit Sub
        '    TxtSID.Focus()
        'End If
        If Not IsDate(TxtBornDate.Text) Then
            MsgBox("出生时间应输入日期格式（yyyy-mm-dd）！ ", vbOKOnly + vbExclamation, "警告 ")
            Exit Sub
            TxtBornDate.Focus()
        End If
        If Not IsDate(TxtRuDate.Text) Then
            MsgBox("入校时间应输入日期格式（yyyy-mm-dd）！ ", vbOKOnly + vbExclamation, "警告 ")
            TxtRuDate.Focus()
            Exit Sub
        End If

        myrow.Item(0) = Trim(TxtSID.Text)
        myrow.Item(1) = Trim(TxtName.Text)
        myrow.Item(2) = Trim(ComboSex.Text)
        myrow.Item(3) = Trim(TxtBornDate.Text)
        myrow.Item(4) = Trim(TxtClassno.Text)
        myrow.Item(5) = Trim(TxtTel.Text)
        myrow.Item(6) = Trim(TxtRuDate.Text)
        myrow.Item(7) = Trim(TxtAddress.Text)
        myrow.Item(8) = Trim(TxtComment.Text)
        mytable.GetChanges()
        cmd = New OleDbCommandBuilder(ADOcmd)
        ADOcmd.Update(ds, "Student")
        MsgBox("修改学籍信息成功！ ", vbOKOnly + vbExclamation, "警告 ")
        TxtName.Enabled = False
        ComboSex.Enabled = False
        TxtBornDate.Enabled = False
        TxtClassno.Enabled = False
        TxtRuDate.Enabled = False
        TxtTel.Enabled = False
        TxtAddress.Enabled = False
        TxtComment.Enabled = False   '重新设置信息为只读

      

 

    End Sub

End Class