form1.vb

黑魔方vb.net数据库开发第4章第3节

Imports System.Data.SqlClient
Public Class Form1
    Inherits System.Windows.Forms.Form

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

    Public Sub New()
        MyBase.New()

        '该调用是 Windows 窗体设计器所必需的。
        InitializeComponent()

        '在 InitializeComponent() 调用之后添加任何初始化

    End Sub

    '窗体重写处置以清理组件列表。
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

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

    '注意：以下过程是 Windows 窗体设计器所必需的
    '可以使用 Windows 窗体设计器修改此过程。
    '不要使用代码编辑器修改它。
    Friend WithEvents Button1 As System.Windows.Forms.Button
    Friend WithEvents Button2 As System.Windows.Forms.Button
    Friend WithEvents Label1 As System.Windows.Forms.Label
    Friend WithEvents Label2 As System.Windows.Forms.Label
    Friend WithEvents Label3 As System.Windows.Forms.Label
    Friend WithEvents TextBox1 As System.Windows.Forms.TextBox
    Friend WithEvents ComboBox1 As System.Windows.Forms.ComboBox
    Friend WithEvents ComboBox2 As System.Windows.Forms.ComboBox
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.Button1 = New System.Windows.Forms.Button()
        Me.Button2 = New System.Windows.Forms.Button()
        Me.Label1 = New System.Windows.Forms.Label()
        Me.Label2 = New System.Windows.Forms.Label()
        Me.TextBox1 = New System.Windows.Forms.TextBox()
        Me.Label3 = New System.Windows.Forms.Label()
        Me.ComboBox1 = New System.Windows.Forms.ComboBox()
        Me.ComboBox2 = New System.Windows.Forms.ComboBox()
        Me.SuspendLayout()
        '
        'Button1
        '
        Me.Button1.Location = New System.Drawing.Point(88, 160)
        Me.Button1.Name = "Button1"
        Me.Button1.Size = New System.Drawing.Size(96, 32)
        Me.Button1.TabIndex = 0
        Me.Button1.Text = "增加记录"
        '
        'Button2
        '
        Me.Button2.Location = New System.Drawing.Point(256, 160)
        Me.Button2.Name = "Button2"
        Me.Button2.Size = New System.Drawing.Size(88, 32)
        Me.Button2.TabIndex = 1
        Me.Button2.Text = "删除记录"
        '
        'Label1
        '
        Me.Label1.Location = New System.Drawing.Point(96, 32)
        Me.Label1.Name = "Label1"
        Me.Label1.Size = New System.Drawing.Size(96, 24)
        Me.Label1.TabIndex = 3
        Me.Label1.Text = "客户编号"
        '
        'Label2
        '
        Me.Label2.Location = New System.Drawing.Point(96, 72)
        Me.Label2.Name = "Label2"
        Me.Label2.Size = New System.Drawing.Size(96, 24)
        Me.Label2.TabIndex = 4
        Me.Label2.Text = "雇员编号"
        '
        'TextBox1
        '
        Me.TextBox1.Location = New System.Drawing.Point(200, 104)
        Me.TextBox1.Name = "TextBox1"
        Me.TextBox1.Size = New System.Drawing.Size(128, 25)
        Me.TextBox1.TabIndex = 6
        Me.TextBox1.Text = ""
        '
        'Label3
        '
        Me.Label3.Location = New System.Drawing.Point(96, 104)
        Me.Label3.Name = "Label3"
        Me.Label3.Size = New System.Drawing.Size(80, 24)
        Me.Label3.TabIndex = 7
        Me.Label3.Text = "订单日期"
        '
        'ComboBox1
        '
        Me.ComboBox1.Location = New System.Drawing.Point(200, 24)
        Me.ComboBox1.Name = "ComboBox1"
        Me.ComboBox1.Size = New System.Drawing.Size(136, 23)
        Me.ComboBox1.TabIndex = 8
        '
        'ComboBox2
        '
        Me.ComboBox2.Location = New System.Drawing.Point(200, 64)
        Me.ComboBox2.Name = "ComboBox2"
        Me.ComboBox2.Size = New System.Drawing.Size(136, 23)
        Me.ComboBox2.TabIndex = 9
        '
        'Form1
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(8, 18)
        Me.ClientSize = New System.Drawing.Size(432, 232)
        Me.Controls.AddRange(New System.Windows.Forms.Control() {Me.ComboBox2, Me.ComboBox1, Me.Label3, Me.TextBox1, Me.Label2, Me.Label1, Me.Button2, Me.Button1})
        Me.Name = "Form1"
        Me.Text = "Form1"
        Me.ResumeLayout(False)

    End Sub

#End Region
    Dim con As New ADODB.Connection()
    Dim cmd As New ADODB.Command()
    Dim strsql As String
    Dim employeers, customerrs, orderrs As New ADODB.Recordset()
    '打开数据库
    Sub opendata()
        con.Open("Provider=SQLOLEDB.1;Integrated Security=SSPI; Persist Security Info=False;" _
         & "Database=Northwind")
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim khbh As String
        Dim gyh As Integer
        Dim rq As String
        khbh = ComboBox1.Text
        gyh = ComboBox2.Text
        rq = TextBox1.Text
        cmd.ActiveConnection = con
        '增加记录
        strsql = "Insert into orders(customerid,employeeid,orderdate)"
        strsql = strsql & " values('" & khbh & "'," & gyh & ",'" & rq & "')"
        cmd.CommandText = strsql
        cmd.Execute()
        MsgBox("记录增加完毕！")
    End Sub


    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        '找出新增加记录的记录号(由于orderid自动编号，故新加的编号为最大编号)
        orderrs.Open("select max(orderid) as maxid from orders", con)
        '删除记录
        strsql = "delete from orders where orderid=" & orderrs("maxid").Value
        cmd.CommandText = strsql
        cmd.Execute()
        MsgBox("记录删除成功！")
    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        opendata()
        '将客户编号加入到combobox1
        customerrs.Open("select customerid from customers", con)
        Do While Not customerrs.EOF
            ComboBox1.Items.Add(customerrs(0).Value)
            customerrs.MoveNext()
        Loop
        '将雇员编号加入到combobox2中
        employeers.Open("select employeeid from employees", con)
        Do While Not employeers.EOF
            ComboBox2.Items.Add(employeers(0).Value)
            employeers.MoveNext()
        Loop

    End Sub
End Class