中文期刊订购.vb

图书管理系统 万千可以用于小规模的图书管理拉 用access作为数据库

        Me.Button5.Location = New System.Drawing.Point(368, 192)
        Me.Button5.Name = "Button5"
        Me.Button5.Size = New System.Drawing.Size(88, 23)
        Me.Button5.TabIndex = 2
        Me.Button5.Text = "统计订购总价"
        '
        'Button4
        '
        Me.Button4.Location = New System.Drawing.Point(264, 192)
        Me.Button4.Name = "Button4"
        Me.Button4.TabIndex = 1
        Me.Button4.Text = "修改"
        '
        'DataGrid1
        '
        Me.DataGrid1.DataMember = ""
        Me.DataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText
        Me.DataGrid1.Location = New System.Drawing.Point(8, 16)
        Me.DataGrid1.Name = "DataGrid1"
        Me.DataGrid1.Size = New System.Drawing.Size(440, 176)
        Me.DataGrid1.TabIndex = 0
        '
        'MainMenu1
        '
        Me.MainMenu1.MenuItems.AddRange(New System.Windows.Forms.MenuItem() {Me.MenuItem1, Me.MenuItem3, Me.MenuItem6})
        '
        'MenuItem1
        '
        Me.MenuItem1.Index = 0
        Me.MenuItem1.MenuItems.AddRange(New System.Windows.Forms.MenuItem() {Me.MenuItem2})
        Me.MenuItem1.Text = "文件(&F)"
        '
        'MenuItem2
        '
        Me.MenuItem2.Index = 0
        Me.MenuItem2.Text = "退出(&X)"
        '
        'MenuItem3
        '
        Me.MenuItem3.Index = 1
        Me.MenuItem3.MenuItems.AddRange(New System.Windows.Forms.MenuItem() {Me.MenuItem4, Me.MenuItem5})
        Me.MenuItem3.Text = "数据处理(&A)"
        '
        'MenuItem4
        '
        Me.MenuItem4.Index = 0
        Me.MenuItem4.Text = "导出Execl"
        '
        'MenuItem5
        '
        Me.MenuItem5.Index = 1
        Me.MenuItem5.Text = "打印"
        '
        'MenuItem6
        '
        Me.MenuItem6.Index = 2
        Me.MenuItem6.Shortcut = System.Windows.Forms.Shortcut.F1
        Me.MenuItem6.Text = "帮助(&H)"
        '
        'PrintDocument1
        '
        '
        'Label7
        '
        Me.Label7.Location = New System.Drawing.Point(8, 192)
        Me.Label7.Name = "Label7"
        Me.Label7.Size = New System.Drawing.Size(192, 16)
        Me.Label7.TabIndex = 3
        Me.Label7.Text = "Yes表示订购；No表示还没有订购"
        Me.Label7.TextAlign = System.Drawing.ContentAlignment.MiddleRight
        '
        '中文期刊订购
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(6, 14)
        Me.ClientSize = New System.Drawing.Size(472, 321)
        Me.Controls.Add(Me.GroupBox2)
        Me.Controls.Add(Me.GroupBox1)
        Me.MaximizeBox = False
        Me.Menu = Me.MainMenu1
        Me.Name = "中文期刊订购"
        Me.Text = "中文期刊订购"
        Me.GroupBox1.ResumeLayout(False)
        Me.GroupBox2.ResumeLayout(False)
        CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub

#End Region
    Dim sqlstr As String
    Dim dingg As String
    Dim mycomm As New OleDbCommand("select  报刊代号,刊名,份数,定期,单价,单价*份数 as 总价,定购否 from 中外文期刊订购表 where 分类 like ""%中文期刊%"" ", myconn)
    Dim mydataad As OleDbDataAdapter = New OleDbDataAdapter(mycomm)
    Dim mydataset As DataSet = New DataSet
    Sub chushi()
        If myconn.State <> ConnectionState.Open Then
            myconn.Open()
        End If
        mydataset.Clear()
        mydataad.Fill(mydataset)
        DataGrid1.DataSource = mydataset.Tables(0)
        myconn.Close()
    End Sub
    Private Sub MenuItem2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem2.Click
        Me.Close()
    End Sub
    Private Sub 中文期刊订购_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        chushi()
    End Sub
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try
            If myconn.State <> ConnectionState.Open Then
                myconn.Open()
            End If
            sqlstr = "insert into 中外文期刊订购表(报刊代号,刊名,份数,定期,单价,定购否,分类) values ('" & TextBox1.Text & "','" & TextBox2.Text & "','" & TextBox3.Text & "','" & TextBox4.Text & "','" & TextBox5.Text & "','" & ComboBox1.Text & "',""中文期刊"")"
            Dim mycomm As New OleDbCommand
            mycomm.Connection = myconn
            mycomm.CommandText() = sqlstr
            mycomm.ExecuteNonQuery()
            myconn.Close()
            chushi()
        Catch ex As Exception
            MsgBox("库里已经存在同样报刊代号的数据,请检查一下是否有误,谢谢！")
        End Try
    End Sub

    Private Sub DataGrid1_CurrentCellChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DataGrid1.CurrentCellChanged
        Try
            dingg = DataGrid1.Item(DataGrid1.CurrentCell.RowNumber, 0)
        Catch ex As Exception

        End Try
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Try
            If myconn.State <> ConnectionState.Open Then
                myconn.Open()
            End If
            Dim mycomm As New OleDbCommand("select  * from 中外文期刊订购表 where 报刊代号= '" & dingg & "' ", myconn)
            Dim mydataad As OleDbDataAdapter = New OleDbDataAdapter(mycomm)
            Dim mydataset As DataSet = New DataSet
            mydataset.Clear()
            mydataad.Fill(mydataset)
            TextBox1.Text = mydataset.Tables(0).Rows(0)("报刊代号").ToString
            TextBox2.Text = mydataset.Tables(0).Rows(0)("刊名").ToString
            TextBox3.Text = mydataset.Tables(0).Rows(0)("份数").ToString
            TextBox4.Text = mydataset.Tables(0).Rows(0)("定期").ToString
            TextBox5.Text = mydataset.Tables(0).Rows(0)("单价").ToString
            ComboBox1.Text = mydataset.Tables(0).Rows(0)("定购否").ToString
            myconn.Close()
            Button3.Enabled = True
        Catch ex As Exception

        End Try
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Try
            If myconn.State <> ConnectionState.Open Then
                myconn.Open()
            End If
            sqlstr = "update 中外文期刊订购表 set 报刊代号='" & TextBox1.Text & "',刊名='" & TextBox2.Text & "',份数='" & TextBox3.Text & "',定期='" & TextBox4.Text & "',单价='" & TextBox5.Text & "',定购否='" & ComboBox1.Text & "' where 报刊代号= '" & dingg & "' "
            Dim mycomm As New OleDbCommand
            mycomm.Connection = myconn
            mycomm.CommandText() = sqlstr
            mycomm.ExecuteScalar()
            myconn.Close()
            chushi()
            Button3.Enabled = False
        Catch ex As Exception

        End Try
    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Try
            Dim drc As New OleDbCommandBuilder(mydataad)
            mydataad.Update(mydataset)
            chushi()
        Catch ex As Exception

        End Try
    End Sub

    Private Sub MenuItem4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem4.Click
        Try
            Dim excelApp1 As New Microsoft.Office.Interop.Excel.Application
            Dim excelBook1 As Microsoft.Office.Interop.Excel.Workbook = excelApp1.Workbooks.Add
            Dim excelWorksheet1 As Microsoft.Office.Interop.Excel.Worksheet = CType(excelBook1.Worksheets(1), Microsoft.Office.Interop.Excel.Worksheet)
            excelApp1.Visible = True
            With excelWorksheet1 '',,,,,
                .Columns().ColumnWidth = 15
                .Range("A1").Value = "报刊代号"
                .Range("A1").Font.Bold = True
                .Range("B1").Value = "刊名"
                .Range("B1").Font.Bold = True
                .Range("C1").Value = "份数"
                .Range("C1").Font.Bold = True
                .Range("D1").Value = "定期"
                .Range("D1").Font.Bold = True
                .Range("E1").Value = "单价"
                .Range("E1").Font.Bold = True
                .Range("F1").Value = "总价"
                .Range("F1").Font.Bold = True
                .Range("G1").Value = "定购否"
                .Range("G1").Font.Bold = True
                Dim i As Integer = 2
                Dim dr As DataRow
                For Each dr In mydataset.Tables(0).Rows
                    .Range("A" & i.ToString).Value = dr("报刊代号")
                    .Range("B" & i.ToString).Value = dr("刊名")
                    .Range("C" & i.ToString).Value = dr("份数")
                    .Range("D" & i.ToString).Value = dr("定期")
                    .Range("E" & i.ToString).Value = dr("单价")
                    .Range("F" & i.ToString).Value = dr("总价")
                    .Range("G" & i.ToString).Value = dr("定购否")
                    i += 1
                Next
            End With
        Catch ex As Exception

        End Try
    End Sub

    Private Sub MenuItem5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem5.Click
        PrintDocument1.Print()
    End Sub

    Private Sub PrintDocument1_PrintPage(ByVal sender As Object, ByVal e As System.Drawing.Printing.PrintPageEventArgs) Handles PrintDocument1.PrintPage
        Dim myPaintArgs As New PaintEventArgs(e.Graphics, New Rectangle(New Point(0, 0), Me.Size))
        Me.InvokePaint(DataGrid1, myPaintArgs)
    End Sub

    Private Sub MenuItem6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem6.Click
        Process.Start(Application.StartupPath & ("\11.txt"))
    End Sub

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
        Try
            Dim mycom As New OleDbCommand("select  sum(单价*份数) as 总价 from 中外文期刊订购表 where 分类 like ""%中文期刊%"" and 定购否 like ""%Yes%"" ", myconn)
            Dim mydataa As OleDbDataAdapter = New OleDbDataAdapter(mycom)
            Dim mydatase As DataSet = New DataSet
            If myconn.State <> ConnectionState.Open Then
                myconn.Open()
            End If
            mydatase.Clear()
            mydataa.Fill(mydatase)
            MsgBox("你所有确定订购的总金额" & mydatase.Tables(0).Rows(0)("总价").ToString & "元")
            myconn.Close()
        Catch ex As Exception

        End Try
    End Sub
End Class