📄 住宿统计2.vb
字号:
Public Class 住宿统计2
Inherits System.Windows.Forms.Form
#Region " Windows 窗体设计器生成的代码 "
Public Sub New()
MyBase.New()
'该调用是 Windows 窗体设计器所必需的。
InitializeComponent()
'在 InitializeComponent() 调用之后添加任何初始化
End Sub
'窗体重写 dispose 以清理组件列表。
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 OleDbDA As System.Data.OleDb.OleDbDataAdapter
Friend WithEvents OleDbCom As System.Data.OleDb.OleDbCommand
Friend WithEvents Da1 As 学生公寓管理系统.Da
Friend WithEvents Label1 As System.Windows.Forms.Label
Friend WithEvents OleDbSelectCommand1 As System.Data.OleDb.OleDbCommand
Friend WithEvents OleDbInsertCommand1 As System.Data.OleDb.OleDbCommand
Friend WithEvents OleDbCon As System.Data.OleDb.OleDbConnection
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.OleDbDA = New System.Data.OleDb.OleDbDataAdapter
Me.OleDbCom = New System.Data.OleDb.OleDbCommand
Me.Da1 = New 学生公寓管理系统.Da
Me.Label1 = New System.Windows.Forms.Label
Me.OleDbSelectCommand1 = New System.Data.OleDb.OleDbCommand
Me.OleDbInsertCommand1 = New System.Data.OleDb.OleDbCommand
Me.OleDbCon = New System.Data.OleDb.OleDbConnection
CType(Me.Da1, System.ComponentModel.ISupportInitialize).BeginInit()
Me.SuspendLayout()
'
'OleDbDA
'
Me.OleDbDA.InsertCommand = Me.OleDbInsertCommand1
Me.OleDbDA.SelectCommand = Me.OleDbSelectCommand1
Me.OleDbDA.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "住宿统计", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("栋号", "栋号"), New System.Data.Common.DataColumnMapping("房号", "房号"), New System.Data.Common.DataColumnMapping("总床位", "总床位"), New System.Data.Common.DataColumnMapping("已住床位", "已住床位"), New System.Data.Common.DataColumnMapping("剩余床位", "剩余床位"), New System.Data.Common.DataColumnMapping("统 计 时 间", "统 计 时 间")})})
'
'Da1
'
Me.Da1.DataSetName = "Da"
Me.Da1.Locale = New System.Globalization.CultureInfo("zh-CN")
'
'Label1
'
Me.Label1.AutoSize = True
Me.Label1.Font = New System.Drawing.Font("宋体", 14.25!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(134, Byte))
Me.Label1.ForeColor = System.Drawing.SystemColors.ActiveCaption
Me.Label1.Location = New System.Drawing.Point(8, 3)
Me.Label1.Name = "Label1"
Me.Label1.Size = New System.Drawing.Size(271, 25)
Me.Label1.TabIndex = 1
Me.Label1.Text = "系统正在处理数据请稍等2……"
'
'OleDbSelectCommand1
'
Me.OleDbSelectCommand1.CommandText = "SELECT 栋号, 房号, 总床位, 已住床位, 剩余床位, [统 计 时 间] FROM 住宿统计 ORDER BY 栋号, 房号, 总床位"
Me.OleDbSelectCommand1.Connection = Me.OleDbCon
'
'OleDbInsertCommand1
'
Me.OleDbInsertCommand1.CommandText = "INSERT INTO 住宿统计(栋号, 房号, 总床位, 已住床位, 剩余床位) VALUES (?, ?, ?, ?, ?)"
Me.OleDbInsertCommand1.Connection = Me.OleDbCon
Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("栋号", System.Data.OleDb.OleDbType.VarWChar, 50, "栋号"))
Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("房号", System.Data.OleDb.OleDbType.VarWChar, 50, "房号"))
Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("总床位", System.Data.OleDb.OleDbType.VarWChar, 50, "总床位"))
Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("已住床位", System.Data.OleDb.OleDbType.VarWChar, 50, "已住床位"))
Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("剩余床位", System.Data.OleDb.OleDbType.VarWChar, 50, "剩余床位"))
'
'OleDbCon
'
Me.OleDbCon.ConnectionString = "Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database L" & _
"ocking Mode=1;Data Source=""学生公寓管理系统.mdb"";Jet OLEDB:Engine Type=5;Provider=""Micro" & _
"soft.Jet.OLEDB.4.0"";Jet OLEDB:System database=;Jet OLEDB:SFP=False;persist secur" & _
"ity info=False;Extended Properties=;Mode=Share Deny None;Jet OLEDB:Encrypt Datab" & _
"ase=False;Jet OLEDB:Create System Database=False;Jet OLEDB:Don't Copy Locale on " & _
"Compact=False;Jet OLEDB:Compact Without Replica Repair=False;User ID=Admin;Jet O" & _
"LEDB:Global Bulk Transactions=1"
'
'住宿统计2
'
Me.AutoScaleBaseSize = New System.Drawing.Size(6, 14)
Me.ClientSize = New System.Drawing.Size(272, 32)
Me.Controls.Add(Me.Label1)
Me.FormBorderStyle = System.Windows.Forms.FormBorderStyle.None
Me.Name = "住宿统计2"
Me.ShowInTaskbar = False
Me.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen
Me.Text = "住宿统计2"
CType(Me.Da1, System.ComponentModel.ISupportInitialize).EndInit()
Me.ResumeLayout(False)
End Sub
#End Region
Private Sub 住宿统计2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim time As String
Dim a, b, c, d, f, g As String
Dim Zsum, Ysum, Ssum As Integer
Zsum = 0
Ysum = 0
Ssum = 0
time = Now()
Try '1
OleDbCon.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=学生公寓管理系统.mdb"
OleDbCon.Open()
OleDbCom.Connection = OleDbCon
OleDbCom.CommandType = CommandType.TableDirect
OleDbCom.CommandText = "住宿统计"
OleDbDA.SelectCommand = OleDbCom
OleDbDA.Fill(Da1)
Da1.Tables(0).TableName = "住宿统计"
OleDbCom.CommandType = CommandType.Text
Dim Conn As New System.Data.OleDb.OleDbConnection
Dim Cmd As New System.Data.OleDb.OleDbCommand
Dim DataReader As System.Data.OleDb.OleDbDataReader
Try '2
Conn.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=学生公寓管理系统.mdb"
Conn.Open()
Cmd.CommandText = "SELECT 房源库.栋号, 房源库.房号, 房源库.总床位, 已住床位.已住床位 FROM 房源库, 已住床位 WHERE 房源库.栋号=已住床位.栋号 and 房源库.房号=已住床位.房号"
Cmd.Connection = Conn
DataReader = Cmd.ExecuteReader
'从已住床位表中读出数据
While DataReader.Read()
a = DataReader.Item(0)
b = DataReader.Item(1)
c = DataReader.Item(2)
d = DataReader.Item(3)
f = CInt(c) - CInt(d)
g = time & "^^"
Zsum += CInt(c)
Ysum += CInt(d)
Ssum += CInt(f)
'把栋号,房号,已住床位 写入到已住床位表中
OleDbCom.CommandText = "Insert Into 住宿统计 values(?,?,?,?,?,?)"
OleDbCom.Parameters.Clear()
OleDbCom.Parameters.Add("a", a)
OleDbCom.Parameters.Add("b", b)
OleDbCom.Parameters.Add("c", c)
OleDbCom.Parameters.Add("d", d)
OleDbCom.Parameters.Add("f", f)
OleDbCom.Parameters.Add("g", g)
OleDbDA.InsertCommand = OleDbCom
Dim DRow As DataRow
DRow = Da1.Tables("住宿统计").NewRow
DRow.Item(0) = a
DRow.Item(1) = b
DRow.Item(2) = c
DRow.Item(3) = d
DRow.Item(4) = f
Drow.Item(5) = g
Da1.Tables("住宿统计").Rows.Add(DRow)
OleDbDA.Update(Da1, "住宿统计")
End While
DataReader.Close()
Conn.Close()
'从已住床位,房源表中统计没人住的宿舍的信息
Conn.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=学生公寓管理系统.mdb"
Conn.Open()
Cmd.CommandText = "SELECT DISTINCT 房源库.栋号, 房源库.房号, 房源库.总床位 FROM 房源库, 已住床位 WHERE 房源库.栋号 not in(select 栋号 from 已住床位) or (房源库.栋号=已住床位.栋号 and 房源库.房号 not in(select 房号 from 已住床位)) ORDER BY 房源库.栋号, 房源库.房号;"
Cmd.Connection = Conn
DataReader = Cmd.ExecuteReader
While DataReader.Read
a = DataReader.Item(0)
b = DataReader.Item(1)
c = DataReader.Item(2)
d = 0
f = 0
g = time & "^^"
Zsum += CInt(c)
Ysum += CInt(d)
Ssum += CInt(f)
OleDbCom.CommandText = "Insert Into 住宿统计 values(?,?,?,?,?,?)"
OleDbCom.Parameters.Clear()
OleDbCom.Parameters.Add("a", a)
OleDbCom.Parameters.Add("b", b)
OleDbCom.Parameters.Add("c", c)
OleDbCom.Parameters.Add("d", d)
OleDbCom.Parameters.Add("f", f)
OleDbCom.Parameters.Add("g", g)
OleDbDA.InsertCommand = OleDbCom
Dim DRow As DataRow
DRow = Da1.Tables("住宿统计").NewRow
DRow.Item(0) = a
DRow.Item(1) = b
DRow.Item(2) = c
DRow.Item(3) = d
DRow.Item(4) = f
Da1.Tables("住宿统计").Rows.Add(DRow)
OleDbDA.Update(Da1, "住宿统计")
End While
DataReader.Close()
Conn.Close()
'把总数写入和分格符住宿统计表中
Dim k As Integer
g = g & "^"
For k = 0 To 2
If k = 0 Then
a = "学生公寓"
b = "住宿统计"
c = Zsum
d = Ysum
f = Ssum
g = time & "^"
ElseIf k = 1 Then
a = "_______________"
b = "________________"
c = "________________"
d = "________________"
f = "________________"
g = time
Else
a = " "
b = " "
c = " "
d = " "
f = " "
g = time
End If
OleDbCom.CommandText = "Insert Into 住宿统计 values(?,?,?,?,?,?)"
OleDbCom.Parameters.Clear()
OleDbCom.Parameters.Add("a", a)
OleDbCom.Parameters.Add("b", b)
OleDbCom.Parameters.Add("c", c)
OleDbCom.Parameters.Add("d", d)
OleDbCom.Parameters.Add("f", f)
OleDbCom.Parameters.Add("g", g)
OleDbDA.InsertCommand = OleDbCom
Dim DRow1 As DataRow
DRow1 = Da1.Tables("住宿统计").NewRow
DRow1.Item(0) = a
DRow1.Item(1) = b
DRow1.Item(2) = c
DRow1.Item(3) = d
DRow1.Item(4) = f
DRow1.Item(5) = g
Da1.Tables("住宿统计").Rows.Add(DRow1)
OleDbDA.Update(Da1, "住宿统计")
Next
Catch ex As Exception
MsgBox(ex.Message)
End Try '2
Catch ex As Exception
End Try '1
'析构连接对象,命令对象,数据对象
OleDbCon.Dispose()
OleDbCom.Dispose()
OleDbDA.Dispose()
Da1.Dispose()
Me.Close()
End Sub
Private Sub 住宿统计2_VisibleChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.VisibleChanged
Me.Hide()
End Sub
Private Sub 住宿统计2_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing
End Sub
End Class
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -