frmbuybook.vb

visual Basic 数据库开发实例导航

                End If
            Else
                strFilter = "where 图书编号 like '"
                strFilter += txbTwo.Text.Trim + "%'"
                If txbThree.Text.Trim.Length <> 0 Then
                    strFilter += " and 图书名称 like '"
                    strFilter += txbThree.Text.Trim + "%'"
                End If
            End If
        Else
            strFilter = "where 征订编号 like '"
            strFilter += txbOne.Text.Trim + "%'"
            If txbTwo.Text.Trim.Length <> 0 Then
                strFilter += " and 图书编号 like '"
                strFilter += txbTwo.Text.Trim + "%'"
                If txbThree.Text.Trim.Length <> 0 Then
                    strFilter += " and 图书名称 like '"
                    strFilter += txbThree.Text.Trim + "%'"
                End If
            Else
                If txbThree.Text.Trim.Length <> 0 Then
                    strFilter += " and 图书名称 like '"
                    strFilter += txbThree.Text.Trim + "%'"
                End If
            End If
        End If
        '清空DataSet11
        DataSet11.Clear()
        SqlConnection1.ConnectionString = DataBase.sConn
        Dim strSQL As String
        If strFilter.Trim.Length = 0 Then
            strFilter = " where 是否验收 = 0 "
        Else
            strFilter = " and 是否验收 = 0  "
        End If
        SqlDataAdapter1.SelectCommand.CommandText _
        = "select * from 图书征订 " + strFilter
        '根据查询条件重新填充DataSet11
        SqlDataAdapter1.Fill(DataSet11)
        dtData = DataSet11.Tables("图书征订")
        If dtData.Columns.IndexOf("图书名称") = -1 Then
            dtData.Columns.Add("图书名称")
        End If
        '为图书名称设置数据
        SetBookName()
        bmData = Me.BindingContext(DataSet11, "图书征订")
        If bmData.Count <> 0 Then
            bmData.Position = 0
        End If
    End Sub
    Public Overrides Sub SetDataGrid()
        LoadData()
        '定义一个DataGrid表样式
        Dim ts As New DataGridTableStyle
        Dim aColumnTextColumn As DataGridTextBoxColumn
        Dim numCols As Integer = dtData.Columns.Count
        Dim i As Integer
        For i = 0 To numCols - 4
            If i = 2 Then
                '添加图书名称列
                aColumnTextColumn = New DataGridTextBoxColumn
                aColumnTextColumn.MappingName = _
                "图书名称"
                aColumnTextColumn.HeaderText = _
                "图书名称"
                aColumnTextColumn.NullText = ""
                aColumnTextColumn.ReadOnly = True
                aColumnTextColumn.Width = 110
                ts.GridColumnStyles.Add(aColumnTextColumn)
            End If
            aColumnTextColumn = New DataGridTextBoxColumn
            aColumnTextColumn.MappingName = _
            dtData.Columns(i).ColumnName
            aColumnTextColumn.HeaderText = _
            dtData.Columns(i).ColumnName
            aColumnTextColumn.NullText = ""
            aColumnTextColumn.ReadOnly = True
            ts.GridColumnStyles.Add(aColumnTextColumn)
        Next
        ts.AlternatingBackColor = Color.LightGray
        ts.AllowSorting = False
        ts.MappingName = dtData.TableName
        dgdList.TableStyles.Clear()
        dgdList.TableStyles.Add(ts)
        Dim dv As DataView = dtData.DefaultView
        dv.AllowNew = False
        dv.AllowDelete = False
        dv.AllowEdit = False
        '设置数据源
        dgdList.DataSource = dtData.DefaultView
        bmData = Me.BindingContext(DataSet11, "图书征订")
        lblCount.Text = "记录数:" + dtData.Rows.Count.ToString()
    End Sub
    Public Overrides Sub SetTextBoxState(ByVal bState As Boolean)
        Me.GroupBox1.Enabled = Not bState
    End Sub
    Public Overrides Sub Clear()
        '计算 征订编号
        Dim db As DataBase = New DataBase
        Dim strID, strT, strSQL As String
        strSQL = "select max(征订编号) as MaxID from 图书征订"
        Dim dv As DataView = db.RunSelectSQL(strSQL)
        If dv.Count <> 0 Then
            strT = "0000000"
            strID = (CInt(dv(0)(0)) + 1).ToString
            Me.txbNumber.Text = _
        strT.Substring(0, strT.Length - strID.Length) + strID
        Else
            Me.txbNumber.Text = "0000001"
        End If
        '更新控件信息
        Me.txbBookNumber.Text = ""
        Me.txbBookName.Text = ""
        Me.txbContact.Text = ""
        Me.txbCount.Text = "0"
        Me.txbPrice.Text = "0"
        Me.txbSubscriber.Text = ""
        Me.txbWorker.Text = ""
        Me.tmDate.Value = Date.Now
    End Sub
    Public Overrides Sub SaveForAdd()
        Dim dr As DataRow = DataSet11.Tables("图书征订").NewRow()
        Try
            dr("征订编号") = txbNumber.Text.Trim
            dr("图书编号") = txbBookNumber.Text.Trim
            dr("订购者") = txbSubscriber.Text.Trim
            dr("操作员") = txbWorker.Text.Trim
            dr("订购数量") = txbCount.Text.Trim
            dr("订购单价") = txbPrice.Text.Trim
            dr("订购日期") = tmDate.Text.Trim
            dr("订购者联系方式") = txbContact.Text.Trim
            dr("是否验收") = "False"
        Catch ex As Exception
            MessageBox.Show("数据格式不正确！")
            Exit Sub
        End Try
        Try
            DataSet11.Tables("图书征订").Rows.Add(dr)
            If Me.DataSet11.HasChanges = True Then
                SqlDataAdapter1.Update(DataSet11)
            End If
        Catch ex As Exception
            MessageBox.Show("数据添加失败!")
        End Try
        LoadData()
        dgdList.DataSource = dtData.DefaultView
    End Sub
    Public Overrides Sub DeleteData()
        Try
            '删除当前行的数据
            DataSet11.Tables("图书征订"). _
            Rows(bmData.Position).Delete()
            If Me.DataSet11.HasChanges = True Then
                '更新DataSet11
                SqlDataAdapter1.Update(DataSet11)
            End If
        Catch ex As Exception
            MessageBox.Show("数据删除失败!")
        End Try
    End Sub
    Public Overrides Sub SaveForUpdate()
        Try
            Dim row As Integer
            row = dgdList.CurrentCell.RowNumber
            dgdList.CurrentCell = _
            New DataGridCell((row + 1) Mod bmData.Count, 0)
            '判断是否有更新的数据
            If Me.DataSet11.HasChanges = True Then
                SqlDataAdapter1.Update(DataSet11)
            End If
            SetBookName()
        Catch ex As Exception
            MessageBox.Show("数据修改失败!")
        End Try
    End Sub
    Private Sub txbBookNumber_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles txbBookNumber.TextChanged
        If txbBookNumber.Text.Trim.Length = 0 Then
            Return
        End If
        Dim db As DataBase = New DataBase
        Dim strSQL As String
        strSQL = "select 书名 from 图书信息 where 编号="
        strSQL += txbBookNumber.Text.Trim
        Dim dv As DataView = db.RunSelectSQL(strSQL)
        If dv.Count = 0 Then
            Return
        Else
            txbBookName.Text = dv(0)(0).ToString.Trim
        End If
        db.Dispose()
    End Sub
    Private Sub SetBookName()
        Dim db As DataBase = New DataBase
        Dim strSQL As String
        Dim dv As DataView
        For Each dr As DataRow In dtData.Rows
            '根据图书编号获得书名并设置dgdList相应列的数据
            strSQL = "select 书名 from 图书信息 where 编号="
            strSQL += dr("图书编号").ToString.Trim
            dv = db.RunSelectSQL(strSQL)
            If dv.Count <> 0 Then
                dr("图书名称") = dv(0)(0).ToString.Trim
            End If
        Next
        db.Dispose()
    End Sub
    Private Sub btnOpen_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles btnOpen.Click
        Dim dlgSelect As frmSelectBook = New frmSelectBook
        dlgSelect.ShowDialog()
        txbBookNumber.Text = dlgSelect.GetInfo().Trim
    End Sub
    Private Sub btnSubmit_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles btnSubmit.Click
        '执行sf_图书征订存储过程
        Dim db As DataBase = New DataBase
        db.RunDelOrInsSQL("exec sf_图书征订")
        db.Dispose()
        '重新加载数据并显示
        LoadData()
        dgdList.DataSource = dtData.DefaultView
    End Sub
End Class