Visual Basic 2010 数据库开发之超市管理系统08售货查询草稿

Imports System.Data.SqlClient
Imports 超市管理系统.csglxtDataSetTableAdapters
Public Class FrmShouHuoCX

    Private Sub FrmShouHuoCX_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim shda As New shTableTableAdapter
        Dim dt As New csglxtDataSet.shTableDataTable
        shda.Fill(dt)
        DataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
        DataGridView1.DataSource = dt

        DateTimePicker1.Value = DateTime.FromOADate(Today.ToOADate - 30)
        If cmbXiangMu.Items.Count = 0 Then
            cmbXiangMu.Items.Add("货品种类")
            cmbXiangMu.Items.Add("货品编号")
            cmbXiangMu.Items.Add("货品名称")
        End If
        cmbXiangMu.SelectedIndex = 0

        '调试用的
        txtTiaoJian.Text = "食品"

        Dim sum1 As Integer
        Dim sum2 As Decimal
        Dim conn As SqlConnection = GetConnection()
        conn.Open()
        Dim strsql As String = "Select sum(数量),sum(金额) from shTable"
        Dim comm As New SqlCommand(strsql, conn)
        Dim dr As SqlDataReader
        dr = comm.ExecuteReader
        While dr.Read
            sum1 = dr.GetInt32(0)
            sum2 = dr.GetDecimal(1)
        End While
        conn.Close()
        Label6.Text = sum1
        Label8.Text = sum2
    End Sub

    Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
        Dim sum1 As Integer
        Dim sum2 As Decimal
        Dim date1 As String = Format(DateTimePicker1.Value, "yyyy-MM-dd")
        Dim date2 As String = Format(DateTimePicker2.Value, "yyyy-MM-dd")

        Dim conn As SqlConnection = GetConnection()
        conn.Open()
        Dim strSql As String = _
            "Select sum(数量),sum(金额) From shTable " _
          & "Where 售货日期 between " _
          & "'" & date1 & " 0:00:00 ' and '" & date2 & " 0:00:00 '" _
          & " and " & cmbXiangMu.Text & "='" & txtTiaoJian.Text & "'"
        MsgBox(strSql)
        Dim comm As New SqlCommand(strSql, conn)
        Dim dr As SqlDataReader
        dr = comm.ExecuteReader
        While dr.Read
            Try
                sum1 = dr.GetInt32(0)
                sum2 = dr.GetDecimal(1)
            Catch ex As Exception
                'Exit Sub
            End Try

        End While
        conn.Close()
        Label6.Text = sum1
        Label8.Text = sum2

        '显示查询结果
        DataGridView1.DataSource = Nothing
        Dim da As New SqlDataAdapter("Select sum(数量),sum(金额) From shTable " _
          & "Where 售货日期 between " _
          & "'" & date1 & " 0:00:00 ' and '" & date2 & " 0:00:00 '" _
          & " and " & cmbXiangMu.Text & "='" & txtTiaoJian.Text & "'", conn)
        Dim dt As New DataTable
        da.Fill(dt)

        DataGridView1.DataSource = dt
        DataGridView1.Columns(0).HeaderText = "售货数量"
        DataGridView1.Columns(1).HeaderText = "金额"
    End Sub

    Private Sub btnRefresh_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRefresh.Click
        FrmShouHuoCX_Load(sender, e)
    End Sub

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