book_select.vb

图书管理系统

        Me.Label2.Location = New System.Drawing.Point(16, 64)
        Me.Label2.Name = "Label2"
        Me.Label2.TabIndex = 1
        Me.Label2.Text = "书籍名称"
        '
        'Label1
        '
        Me.Label1.Location = New System.Drawing.Point(312, 24)
        Me.Label1.Name = "Label1"
        Me.Label1.TabIndex = 0
        Me.Label1.Text = "书籍编号"
        '
        'Label6
        '
        Me.Label6.Location = New System.Drawing.Point(520, 112)
        Me.Label6.Name = "Label6"
        Me.Label6.Size = New System.Drawing.Size(24, 16)
        Me.Label6.TabIndex = 1
        Me.Label6.Text = "至"
        '
        'ListView1
        '
        Me.ListView1.Columns.AddRange(New System.Windows.Forms.ColumnHeader() {Me.ColumnHeader0, Me.ColumnHeader1, Me.ColumnHeader2, Me.ColumnHeader3, Me.ColumnHeader4, Me.ColumnHeader5})
        Me.ListView1.Location = New System.Drawing.Point(16, 184)
        Me.ListView1.Name = "ListView1"
        Me.ListView1.Size = New System.Drawing.Size(752, 144)
        Me.ListView1.TabIndex = 1
        Me.ListView1.View = System.Windows.Forms.View.Details
        '
        'ColumnHeader0
        '
        Me.ColumnHeader0.Text = "书籍编号"
        Me.ColumnHeader0.Width = 67
        '
        'ColumnHeader1
        '
        Me.ColumnHeader1.Text = "书籍名称"
        Me.ColumnHeader1.TextAlign = System.Windows.Forms.HorizontalAlignment.Center
        Me.ColumnHeader1.Width = 206
        '
        'ColumnHeader2
        '
        Me.ColumnHeader2.Text = "书籍作者"
        Me.ColumnHeader2.TextAlign = System.Windows.Forms.HorizontalAlignment.Center
        Me.ColumnHeader2.Width = 115
        '
        'ColumnHeader3
        '
        Me.ColumnHeader3.Text = "出版社"
        Me.ColumnHeader3.TextAlign = System.Windows.Forms.HorizontalAlignment.Center
        Me.ColumnHeader3.Width = 155
        '
        'ColumnHeader4
        '
        Me.ColumnHeader4.Text = "出版日期"
        Me.ColumnHeader4.TextAlign = System.Windows.Forms.HorizontalAlignment.Center
        Me.ColumnHeader4.Width = 134
        '
        'ColumnHeader5
        '
        Me.ColumnHeader5.Text = "书籍定价"
        Me.ColumnHeader5.Width = 228
        '
        'Button2
        '
        Me.Button2.Location = New System.Drawing.Point(544, 344)
        Me.Button2.Name = "Button2"
        Me.Button2.Size = New System.Drawing.Size(80, 23)
        Me.Button2.TabIndex = 2
        Me.Button2.Text = "退出"
        '
        'Button3
        '
        Me.Button3.Location = New System.Drawing.Point(232, 344)
        Me.Button3.Name = "Button3"
        Me.Button3.Size = New System.Drawing.Size(80, 23)
        Me.Button3.TabIndex = 3
        Me.Button3.Text = "清空列表框"
        '
        'Book_Select
        '
        Me.AcceptButton = Me.Button1
        Me.AutoScaleBaseSize = New System.Drawing.Size(6, 14)
        Me.BackColor = System.Drawing.SystemColors.ActiveBorder
        Me.ClientSize = New System.Drawing.Size(800, 389)
        Me.Controls.Add(Me.Button3)
        Me.Controls.Add(Me.Button2)
        Me.Controls.Add(Me.ListView1)
        Me.Controls.Add(Me.GroupBox1)
        Me.FormBorderStyle = System.Windows.Forms.FormBorderStyle.FixedDialog
        Me.MaximizeBox = False
        Me.Name = "Book_Select"
        Me.Text = "书籍信息查询"
        Me.GroupBox1.ResumeLayout(False)
        Me.Panel1.ResumeLayout(False)
        Me.ResumeLayout(False)

    End Sub

#End Region
    Dim Book_Situation As String
    Private Sub FillDataToListView1()
        ListView1.Items.Clear() '先清空里面的数据在说，呵呵
        If mycon.State = ConnectionState.Closed Then
            mycon.Open()
        End If
        Dim myda As New SqlDataAdapter(sqlstr, mycon)
        Dim myset1 As New DataSet
        myda.Fill(myset1, "Book")
        Dim i As Integer
        Dim item As ListViewItem
        Dim SubItem2 As ListViewItem.ListViewSubItem
        Dim SubItem3 As ListViewItem.ListViewSubItem
        Dim SubItem4 As ListViewItem.ListViewSubItem
        Dim SubItem5 As ListViewItem.ListViewSubItem
        Dim SubItem6 As ListViewItem.ListViewSubItem
        Try
            For i = 0 To myset1.Tables("Book").Rows.Count - 1
                With myset1.Tables("Book").Rows(i)
                    item = New ListViewItem
                    SubItem2 = New ListViewItem.ListViewSubItem
                    SubItem3 = New ListViewItem.ListViewSubItem
                    SubItem4 = New ListViewItem.ListViewSubItem
                    SubItem5 = New ListViewItem.ListViewSubItem
                    SubItem6 = New ListViewItem.ListViewSubItem
                    item.Text = .Item("ID") & ""
                    SubItem2.Text = .Item("Name") & ""
                    SubItem3.Text = .Item("Anchor") & ""
                    SubItem4.Text = .Item("Publisher") & ""
                    SubItem5.Text = .Item("PublishDate") & ""
                    SubItem6.Text = .Item("Price") & ""
                    item.SubItems.Add(SubItem2)
                    item.SubItems.Add(SubItem3)
                    item.SubItems.Add(SubItem4)
                    item.SubItems.Add(SubItem5)
                    item.SubItems.Add(SubItem6)
                    ListView1.Items.Add(item)
                End With
            Next
            '      myset_Row = myset.Tables("Book").Rows.Count
            If myset1.Tables("Book").Rows.Count = 0 Then
                ' Button2.Enabled = True
                'MessageBox.Show("书籍已经全部借完，学校太穷了！望大家体谅", "注意", MessageBoxButtons.OK, MessageBoxIcon.Warning)
                MessageBox.Show("没有满足条件的记录!", "提醒", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            Else
                '   MessageBox.Show("没有满足条件的记录!", "提醒", MessageBoxButtons.OK, MessageBoxIcon.Warning)
                '  Button2.Enabled = False
                '  MessageBox.Show("该学生还未借书或已经全部归还！", "注意", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            End If
            mycon.Close()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub
    Dim sqlstr As String  '放到最后，呵呵Flag='1'" '基本sql语句
    '  Dim Price1 As New SqlParameter("@Price1", SqlDbType.Int)
    ' Dim Price2 As New SqlParameter("@Price2", SqlDbType.Int)
    Private Sub Book_Select_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    End Sub
    Function CheckData() As Boolean
        If TextBox1.Text <> "" Then
            If IsNumeric(TextBox1.Text) = False Then
                MessageBox.Show("书籍编号输入项必须为整数!", "提醒", MessageBoxButtons.OK, MessageBoxIcon.Warning)
                TextBox1.Focus()
                Return False
            End If
        End If
        If TextBox5.Text <> "" And TextBox6.Text <> "" Then
            If IsNumeric(TextBox5.Text) = False Then
                MessageBox.Show("书籍价格输入项必须为整数!", "提醒", MessageBoxButtons.OK, MessageBoxIcon.Warning)
                TextBox5.Focus()
                Return False
            End If
            If IsNumeric(TextBox6.Text) = False Then
                MessageBox.Show("书籍价格输入项必须为整数!", "提醒", MessageBoxButtons.OK, MessageBoxIcon.Warning)
                TextBox6.Focus()
                Return False
            End If
        End If
        If TextBox5.Text <> "" And TextBox6.Text = "" Or TextBox5.Text = "" And TextBox6.Text <> "" Then
            MessageBox.Show("书籍价格输入选项必须同为空或同不为空!", "提醒", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            TextBox5.Focus()
            Return False
        End If
        Return True
    End Function

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        '开始查询按钮的代码
        '检验数据的合法性
        If RadioButton1.Checked = True Then
            Book_Situation = "Book_Lend"
        End If
        If RadioButton2.Checked = True Then
            Book_Situation = "Book_guihuan"
        End If
        If RadioButton3.Checked = True Then
            Book_Situation = "Book_All"
        End If
        If CheckData() = False Then
            Exit Sub
        End If
        Dim logic As String
        sqlstr = "Select ID,Name,Anchor,Publisher,PublishDate,Price From " & Book_Situation & " where"
        Try
            If CheckBox2.Checked = True Then
                logic = "or"
            Else
                logic = "and"
            End If
            If TextBox1.Text <> "" Then
                If CheckBox1.Checked = True Then
                    sqlstr = sqlstr + " " + "ID like  '%" + TextBox1.Text + "%'" + " " + logic
                Else
                    sqlstr = sqlstr + " " + "ID='" & TextBox1.Text & "'" + " " + logic '这里遇到问题了，因为她是整数，呵呵
                End If
            End If
            If TextBox2.Text <> "" Then
                If CheckBox1.Checked = True Then
                    sqlstr = sqlstr + " " + "Name like  '%" + TextBox2.Text + "%'" + " " + logic
                Else
                    sqlstr = sqlstr + " " + "Name='" + TextBox2.Text + "'" + " " + logic
                End If
            End If
            If TextBox3.Text <> "" Then
                If CheckBox1.Checked = True Then
                    sqlstr = sqlstr + " " + "Anchor like  '%" + TextBox3.Text + "%'" + " " + logic
                Else
                    sqlstr = sqlstr + " " + "Anchor='" + TextBox3.Text + "'" + " " + logic
                End If
            End If
            If TextBox4.Text <> "" Then
                If CheckBox1.Checked = True Then
                    sqlstr = sqlstr + " " + "Publisher like  '%" + TextBox4.Text + "'" + " " + logic
                Else
                    sqlstr = sqlstr + " " + "Publisher='" + TextBox4.Text + "'" + " " + logic
                End If
            End If
            If TextBox5.Text <> "" And TextBox6.Text <> "" Then
                sqlstr = sqlstr + " " + "(Price Between '" & TextBox5.Text & "' and '" & TextBox6.Text & "')" + logic '这句话好搞笑哦，找了半天没有找到问题的答案，好让我失望，呵呵
            End If
            sqlstr = sqlstr.Substring(0, sqlstr.Length - logic.Length) '取子字符传，相当与减去末端的逻辑操作符
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
        FillDataToListView1()
        If ListView1.Items Is Nothing Then
            MessageBox.Show("没有满足条件的记录!", "提醒", MessageBoxButtons.OK, MessageBoxIcon.Warning)
        End If
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Me.Close()
    End Sub
End Class