frmuser.vb

基于VB.NET开发的简单的图书馆管理系统,可正常运行以测试,内带源码.

            strSelect = "update borrowinfo set btime='" + strbtime.AddMonths(1).ToString + "',rtime='" + strrtime.AddMonths(1).ToString + "',recount='" + counter.ToString + "' where bookid = " + tbxjbookid.Text.Trim() + ""
            cmd.CommandText = strSelect
            cmd.ExecuteNonQuery()
            MessageBox.Show("续借成功！")
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try


        conn.Close()

    End Sub



    Private Sub bnQuery_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bnQuery.Click
        Dim strField As String = cbFieldjs.Text.Trim()
        Dim strCon As String = cbConjs.Text.Trim()
        Dim strValue As String = tbValuejs.Text.Trim()

        Select Case strField
            Case "书名"
                strField = "bookname"
            Case "作者"
                strField = "author"
            Case "剩余数量"
                strField = "avacount"
            Case "图书类型"
                strField = "bookclass"
        End Select

        Select Case strCon
            Case "等于"
                strCon = "="
            Case "大于"
                strCon = ">"
            Case "小于"
                strCon = "<"
            Case "大于等于"
                strCon = ">="
            Case "小于等于"
                strCon = "<="
            Case "不等于"
                strCon = "<>"
            Case "模糊查"
                strCon = "like"
        End Select

        'Dim strSource As String = "workstation id=CHANGHAIFENG;packet size=4096;user id=sa;data source=localhost;persist security info=False;initial catalog=books"
        Dim strSelect As String
        If strField = "avacount" Then
            If strCon = "like" Then
                MessageBox.Show("剩余数量不支持模糊查询")
                Exit Sub
            End If
            strSelect = "select * from books where " + strField + " " + strCon + " " + strValue + ""
        End If

        If strCon = "like" Then
            strSelect = "select * from books where " + strField + " " + strCon + " '%" + strValue + "%'"
        Else
            If Not strCon = "avacount" Then
                strSelect = "select * from books where " + strField + " " + strCon + " '" + strValue + "'"
            End If
        End If

        Dim conn As SqlConnection = New SqlConnection(strSource)
        Dim da As SqlDataAdapter = New SqlDataAdapter(strSelect, conn)
        Dim ds As DataSet = New DataSet
        Try
            da.Fill(ds, "queryres")

            dgRst.DataSource = ds
            dgRst.DataMember = "queryres"
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try

    End Sub

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
        Dim strField As String = cbFieldcx.Text.Trim()
        Dim strCon As String = cbConcx.Text.Trim()
        Dim strValue As String = tbValuecx.Text.Trim()

        Select Case strField
            Case "书名"
                strField = "bookname"
            Case "作者"
                strField = "author"
            Case "剩余数量"
                strField = "avacount"
            Case "图书类型"
                strField = "bookclass"
        End Select

        Select Case strCon
            Case "等于"
                strCon = "="
            Case "大于"
                strCon = ">"
            Case "小于"
                strCon = "<"
            Case "大于等于"
                strCon = ">="
            Case "小于等于"
                strCon = "<="
            Case "不等于"
                strCon = "<>"
            Case "模糊查"
                strCon = "like"
        End Select

        'Dim strSource As String = "workstation id=CHANGHAIFENG;packet size=4096;user id=sa;data source=localhost;persist security info=False;initial catalog=books"
        Dim strSelect As String
        If strField = "avacount" Then
            If strCon = "like" Then
                MessageBox.Show("剩余数量不支持模糊查询")
                Exit Sub
            End If
            strSelect = "select * from books where " + strField + " " + strCon + " " + strValue + ""
        End If

        If strCon = "like" Then
            strSelect = "select * from books where " + strField + " " + strCon + " '%" + strValue + "%'"
        Else
            If Not strCon = "avacount" Then
                strSelect = "select * from books where " + strField + " " + strCon + " '" + strValue + "'"
            End If
        End If

        Dim conn As SqlConnection = New SqlConnection(strSource)
        Dim da As SqlDataAdapter = New SqlDataAdapter(strSelect, conn)
        Dim ds As DataSet = New DataSet
        Try
            da.Fill(ds, "queryres")

            dgrstquery.DataSource = ds
            dgrstquery.DataMember = "queryres"

        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try

    End Sub

    Private Sub bnB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bnB.Click
        Dim nava As Integer
        Dim strSelect As String = "select bookname,avacount from books where bookid = '" + tbBookid.Text.Trim() + "'"
        Dim conn As New SqlConnection(strSource)
        Dim strbookname As String
        Try
            conn.Open()
            Dim cmd As New SqlCommand(strSelect, conn)
            Dim reader As SqlDataReader = cmd.ExecuteReader()
            If Not reader.HasRows Then
                MessageBox.Show("书名错误！")
                conn.Close()
                Exit Sub
            End If

            reader.Read()
            strbookname = reader.Item("bookname").ToString().Trim()
            nava = Convert.ToInt32(reader.Item("avacount").ToString().Trim())
            nava -= 1
            reader.Close()
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try


        If nava < 0 Then
            MessageBox.Show("书已借完！")
            conn.Close()
            Exit Sub
        End If

        Dim dt As DateTime = New DateTime
        Dim strCommand As String = "insert into borrowinfo(cardid,bookid,bookname,btime,rtime) values('" + loginname + "','" + tbBookid.Text.Trim() + "','" + strbookname + "','" + dt.Now.ToString().Trim() + "','" + dt.Now.AddMonths(1).ToString.Trim() + "')"

        Dim cmdInsert As New SqlCommand(strCommand, conn)
        Try
            cmdInsert.ExecuteNonQuery()

            strCommand = "update books set avacount = " + nava.ToString() + " where bookid = '" + tbBookid.Text.Trim() + "'"
            Dim cmdUpdate As New SqlCommand(strCommand, conn)
            cmdUpdate.ExecuteNonQuery()
            MessageBox.Show("借书成功！")
        Catch ex As Exception
            MessageBox.Show(ex.ToString)

        End Try

        conn.Close()


    End Sub


    Private Sub bthslook_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bthslook.Click
        'Dim strSource As String = "workstation id=CHANGHAIFENG;packet size=4096;user id=sa;data source=localhost;persist security info=False;initial catalog=books"
        Dim strSelect As String = "select bookid as 图书编号,bookname as 书名,btime as 借书日期,rtime as 还书日期 from borrowinfo where cardid = '" + loginname + "'"
        Dim conn As SqlConnection = New SqlConnection(strSource)
        Dim da As SqlDataAdapter = New SqlDataAdapter(strSelect, conn)
        Dim ds As DataSet = New DataSet
        da.Fill(ds, "hs")

        dg2.DataSource = ds
        dg2.DataMember = "hs"
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        'Dim strSource As String = "workstation id=CHANGHAIFENG;packet size=4096;user id=sa;data source=localhost;persist security info=False;initial catalog=books"
        Dim strSelect As String = "select bookid as 图书编号,bookname as 书名,btime as 借书日期,rtime as 还书日期 from borrowinfo where cardid = '" + loginname + "'"
        Dim conn As SqlConnection = New SqlConnection(strSource)
        Dim da As SqlDataAdapter = New SqlDataAdapter(strSelect, conn)
        Dim ds As DataSet = New DataSet
        da.Fill(ds, "hs")

        dg1.DataSource = ds
        dg1.DataMember = "hs"
    End Sub
    Private Sub bnModify_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bnModify.Click


        Dim strPwd As String = tbPwd.Text.Trim()
        Dim strPwdConfirm As String = tbPwdConfirm.Text.Trim()
        Dim strUsername As String = tbusername.Text.Trim()
        Dim strEmail As String = tbEmail.Text.Trim()
        Dim strClass As String = tbClass.Text.Trim()
        Dim strStudNO As String = tbStudNo.Text.Trim()
        Dim strtelno As String = tbtelno.Text.Trim()
        Dim strsex As String = tbsex.Text.Trim()

        'Dim strSource As String = "workstation id='CHANGHAIFENG';packet size=4096;user id=sa;data source=localhost;persist security info=False;initial catalog=books"

        '检测是否有没有填写的项，要求全部填写
        If strPwd = "" Or strPwdConfirm = "" Or strUsername = "" Or strsex = "" Or strEmail = "" Or strClass = "" Or strStudNO = "" Or strtelno = "" Then
            '这里的检测应该一个个地进行，给出更详细提示信息
            MessageBox.Show("请填写！")
            Exit Sub
        End If

        If tbPwdConfirm.Text.Trim() <> tbPwd.Text.Trim() Then
            MessageBox.Show("两次口令不一致！")
            Exit Sub
        End If

        Dim strUpdate As String = "update bookcard set password='" + strPwd + " ',name='" + strUsername + "',sex='" + strsex + "',class='" + strClass + "',sno='" + strStudNO + "',email='" + strEmail + "',telno='" + strtelno + "'where cardid='" + loginname + "'"
        Dim conn As SqlConnection = New SqlConnection(strSource)
        Try
            conn.Open()
            Dim cmd As SqlCommand = New SqlCommand(strUpdate, conn)
            Dim ret As Integer = cmd.ExecuteNonQuery()
            If ret <> 1 Then
                MessageBox.Show("更新失败！")
            Else
                MessageBox.Show("更新成功！")
            End If
        Catch ex As Exception
            MessageBox.Show("更新失败！")
        End Try

        conn.Close()
    End Sub


    Private Sub clear()

        tbPwd.Clear()
        tbPwdConfirm.Clear()
        tbusername.Clear()
        tbsex.Clear()
        tbClass.Clear()
        tbStudNo.Clear()
        tbEmail.Clear()
        tbtelno.Clear()
    End Sub


    Private Sub bnCancle_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bnCancle.Click
        clear()
    End Sub
End Class