form2.vb

首先利用Sql Sever数据库作为后台

        Me.SqlDataAdapter1.SelectCommand = Me.SqlSelectCommand1
        Me.SqlDataAdapter1.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "刷卡信息表", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("IC卡号", "IC卡号"), 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("日期", "日期")})})
        '
        'DataGrid1
        '
        Me.DataGrid1.DataMember = ""
        Me.DataGrid1.DataSource = Me.DataSet31.刷卡信息表
        Me.DataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText
        Me.DataGrid1.Location = New System.Drawing.Point(32, 88)
        Me.DataGrid1.Name = "DataGrid1"
        Me.DataGrid1.Size = New System.Drawing.Size(552, 144)
        Me.DataGrid1.TabIndex = 20
        '
        'DataSet31
        '
        Me.DataSet31.DataSetName = "DataSet3"
        Me.DataSet31.Locale = New System.Globalization.CultureInfo("zh-CN")
        '
        'SqlSelectCommand1
        '
        Me.SqlSelectCommand1.CommandText = "SELECT IC卡号, 班级, 姓名, 学号, 上课时间, 下课时间, 日期 FROM 刷卡信息表"
        Me.SqlSelectCommand1.Connection = Me.SqlConnection1
        '
        'SqlInsertCommand1
        '
        Me.SqlInsertCommand1.CommandText = "INSERT INTO 刷卡信息表(IC卡号, 班级, 姓名, 学号, 上课时间, 下课时间, 日期) VALUES (@IC卡号, @班级, @姓名, @学号," & _
        " @上课时间, @下课时间, @日期); SELECT IC卡号, 班级, 姓名, 学号, 上课时间, 下课时间, 日期 FROM 刷卡信息表"
        Me.SqlInsertCommand1.Connection = Me.SqlConnection1
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@IC卡号", System.Data.SqlDbType.VarChar, 50, "IC卡号"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@班级", System.Data.SqlDbType.VarChar, 50, "班级"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@姓名", System.Data.SqlDbType.VarChar, 50, "姓名"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@学号", System.Data.SqlDbType.VarChar, 50, "学号"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@上课时间", System.Data.SqlDbType.VarChar, 50, "上课时间"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@下课时间", System.Data.SqlDbType.VarChar, 50, "下课时间"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@日期", System.Data.SqlDbType.VarChar, 50, "日期"))
        '
        'SqlConnection1
        '
        Me.SqlConnection1.ConnectionString = "workstation id=levono20;packet size=4096;user id=sa;data source=levono20;persist " & _
        "security info=False;initial catalog=zjs"
        '
        'Form2
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(6, 14)
        Me.BackColor = System.Drawing.Color.DodgerBlue
        Me.ClientSize = New System.Drawing.Size(624, 325)
        Me.Controls.Add(Me.DataGrid1)
        Me.Controls.Add(Me.TextBox4)
        Me.Controls.Add(Me.TextBox2)
        Me.Controls.Add(Me.TextBox1)
        Me.Controls.Add(Me.Label3)
        Me.Controls.Add(Me.AxMSComm1)
        Me.Controls.Add(Me.Label2)
        Me.Controls.Add(Me.Label1)
        Me.Controls.Add(Me.Button1)
        Me.Controls.Add(Me.GroupBox1)
        Me.Controls.Add(Me.GroupBox2)
        Me.Controls.Add(Me.GroupBox3)
        Me.Controls.Add(Me.TextBox3)
        Me.Name = "Form2"
        Me.Text = "刷卡信息统计"
        CType(Me.AxMSComm1, System.ComponentModel.ISupportInitialize).EndInit()
        Me.GroupBox2.ResumeLayout(False)
        CType(Me.Timer1, System.ComponentModel.ISupportInitialize).EndInit()
        Me.GroupBox3.ResumeLayout(False)
        CType(Me.Timer2, System.ComponentModel.ISupportInitialize).EndInit()
        CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).EndInit()
        CType(Me.DataSet31, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub

#End Region

    Private Function hex2(ByRef c As String) As String
        hex2 = Hex(CInt(c))
        If Len(hex2) < 2 Then
            hex2 = "0" & hex2
        End If
    End Function

    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        AxMSComm1.PortOpen = True
        DataSet31.Clear()
        SqlDataAdapter1.Fill(DataSet31)
        TextBox1.Text = DateTime.Now.ToShortDateString()
        TextBox2.Text = DateTime.Now.ToLongTimeString()
        DataGrid1.Update()
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Timer2.Enabled = False
        Dim Form7 As New Form7
        Form7.Show()
        Me.Hide()
        AxMSComm1.PortOpen = False
    End Sub

    Private Sub AxMSComm1_OnComm(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AxMSComm1.OnComm
        Dim bytedata As Object
        Dim i As Integer
        Dim dataread() As Byte
        Dim readstr As String
        readstr = ""
        Select Case AxMSComm1.CommEvent
            Case MSCommLib.OnCommConstants.comEvReceive
                bytedata = AxMSComm1.Input
                ReDim dataread(UBound(bytedata))
                For i = 0 To UBound(bytedata)
                    dataread(i) = bytedata(i)
                    readstr = readstr & hex2(CStr(dataread(i)))
                Next i
                If readstr.Substring(0, 2) = "FF" Then
                    TextBox3.Text = readstr.Substring(6, 8)
                    TextBox5.Text = readstr.Substring(6, 8)
                End If
        End Select
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Dim jihao As Byte
        jihao = CByte(TextBox4.Text)
        AxMSComm1.OutBufferCount = 0
        AxMSComm1.InBufferCount = 0
        Dim bytecodeact(3) As Byte
        bytecodeact(1) = &HAA
        bytecodeact(2) = jihao
        bytecodeact(3) = &HBB
        AxMSComm1.Output = bytecodeact
    End Sub

    Private Sub Timer1_Elapsed(ByVal sender As System.Object, ByVal e As System.Timers.ElapsedEventArgs) Handles Timer1.Elapsed
        TextBox2.Text = DateTime.Now.ToLongTimeString()
        TextBox2.Refresh()
    End Sub

    Private Sub TextBox3_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox3.TextChanged
        SqlConnection1.Open()
        DataSet31.Clear()
        SqlCommand1.Connection = SqlConnection1
        SqlDataAdapter1.SelectCommand.CommandText = "select * from 刷卡信息表 where IC卡号='" & TextBox3.Text & "' and 下课时间 is null"
        SqlDataAdapter1.Fill(DataSet31)
        If DataSet31.刷卡信息表.Rows.Count = 0 Then
            SqlCommand1.CommandText = "insert into 刷卡信息表(IC卡号,班级,姓名,学号) select IC卡号,班级,姓名,学号 from 学生信息表 where IC卡号='" & TextBox3.Text & "'"
            SqlCommand1.ExecuteNonQuery()
            SqlCommand1.CommandText = "update 刷卡信息表 set 上课时间 = '" & TextBox2.Text & "',日期='" & TextBox1.Text & "' where IC卡号='" & TextBox3.Text & "' and 上课时间 is null"
            SqlCommand1.ExecuteNonQuery()
            SqlCommand1.CommandText = "insert into 学生考勤信息表(IC卡号,班级,姓名,学号,上课时间,日期) select IC卡号,班级,姓名,学号,上课时间,日期 from 刷卡信息表 where IC卡号='" & TextBox3.Text & "'"
            SqlCommand1.ExecuteNonQuery()
        Else
            SqlCommand1.CommandText = "update 刷卡信息表 set 下课时间 = '" & TextBox2.Text & "',日期='" & TextBox1.Text & "' where IC卡号='" & TextBox3.Text & "' and 下课时间 is null"
            SqlCommand1.ExecuteNonQuery()
            SqlCommand1.CommandText = "update 学生考勤信息表 set 下课时间 = '" & TextBox2.Text & "' where IC卡号='" & TextBox3.Text & "' and 下课时间 is null"
            SqlCommand1.ExecuteNonQuery()
        End If
        SqlConnection1.Close()
        DataSet31.Clear()
        SqlDataAdapter1.SelectCommand.CommandText = "select * from 刷卡信息表"
        SqlDataAdapter1.Fill(DataSet31)
        DataGrid1.Update()
        TextBox3.Text = ""
    End Sub

    Private Sub Timer2_Elapsed(ByVal sender As System.Object, ByVal e As System.Timers.ElapsedEventArgs) Handles Timer2.Elapsed
        Dim jihao As Byte
        If TextBox4.Text.Length <> 0 Then
            jihao = CByte(TextBox4.Text)
            AxMSComm1.OutBufferCount = 0
            AxMSComm1.InBufferCount = 0
            Dim bytecodeact(3) As Byte
            bytecodeact(1) = &HAA
            bytecodeact(2) = jihao
            bytecodeact(3) = &HBB
            AxMSComm1.Output = bytecodeact
        End If
    End Sub

    Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged

    End Sub

    Private Sub Label1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label1.Click

    End Sub

    Private Sub Label2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label2.Click

    End Sub

    Private Sub GroupBox1_Enter(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles GroupBox1.Enter

    End Sub

    Private Sub DataGrid1_Navigate(ByVal sender As System.Object, ByVal ne As System.Windows.Forms.NavigateEventArgs)

    End Sub

    Private Sub DataGrid1_Navigate_1(ByVal sender As System.Object, ByVal ne As System.Windows.Forms.NavigateEventArgs)

    End Sub

    Private Sub SqlConnection1_InfoMessage(ByVal sender As System.Object, ByVal e As System.Data.SqlClient.SqlInfoMessageEventArgs)

    End Sub

    Private Sub SqlDataAdapter1_RowUpdated(ByVal sender As System.Object, ByVal e As System.Data.SqlClient.SqlRowUpdatedEventArgs)

    End Sub

    Private Sub TextBox5_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)

    End Sub

    Private Sub DataGrid1_Navigate_2(ByVal sender As System.Object, ByVal ne As System.Windows.Forms.NavigateEventArgs)

    End Sub

    Private Sub GroupBox2_Enter(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles GroupBox2.Enter

    End Sub

    Private Sub GroupBox3_Enter(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles GroupBox3.Enter

    End Sub
End Class