排序

控件:

TEXTBOX    :1个       Name:txtJobNo

BUTTON:     2个       Name:btnFilter,btnShowAll

LISTVIEW :  1个       Name:lstvwJobNo  Columns:JobNo,ContainerID,CartonID,PO,Style,Color,Size,ShipMent,Factory             Sorting:none

源代码:

Imports System.Data.SqlClient

Dim con As New SqlConnection
Dim varSQL As String
Dim r As Integer
Dim conString As String ="data source=127.0.0.1;initial catalog=mydb; uid=sa;password=123"

'定义默认排序变量
Dim mySort As String = "ASC"

'定义默认字段变量
Dim myField As String = "JOB.JOBNO"

'过滤指定条件的记录
    Private Sub btnFilter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFilter.Click
        Try
            Dim fJobNo As String = ""
            fJobNo = txtJobNo.Text.Trim()
            r = List_dgJobNo(fJobNo)

        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        Finally
            If con.State = ConnectionState.Open Then
                con.Close()
            End If
        End Try
    End Sub

'显示所有记录
Private Sub btnShowAll_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnShowAll.Click
        List_dgJobNo("")
End Sub

'函数功能：将读取出来的数据写入lstvwJovNo的表格中
'函数名称：List_dgJobNo,调用格式：List_dgJobNo("10022")，List_dgJobNo("")
'参    数：fJobNo，要查询的工作号JOBNO，为空时则查询所有的
'编 写 者：SD
'日    期：2005-9-23
'转载请保留此信息
Private Function List_dgJobNo(ByVal fJobNo As String) As Integer
        Try
            Cursor.Current = Cursors.WaitCursor

            lstvwJobNo.Items.Clear()

            con = New SqlConnection(conString)

            Dim cmd As SqlCommand
            Dim rd As SqlDataReader

            If fJobNo.Length > 0 Then
                varSQL = "SELECT JOB.JOBNO,CONTAINERNO,CARTONFROM,PO,STYLE,COLOR,CARTONSIZE,SHIPMENT,FACTORY FROM JOB,JOB_LINE WHERE JOB.JOBNO=JOB_LINE.JOBNO AND JOB.JOBNO LIKE '%" & fJobNo & "%' "
            Else
                varSQL = "SELECT JOB.JOBNO,CONTAINERNO,CARTONFROM,PO,STYLE,COLOR,CARTONSIZE,SHIPMENT,FACTORY FROM JOB,JOB_LINE WHERE JOB.JOBNO=JOB_LINE.JOBNO "
            End If

            varSQL = varSQL & "ORDER BY " & myField & " " & mySort & ";"

            cmd = New SqlCommand(varSQL, con)
            rd = cmd.ExecuteReader

            Dim lsv As ListViewItem

            While rd.Read
                lsv = New ListViewItem(rd("JOBNO").ToString)
                'lsv.Checked = True

                lsv.SubItems.Add(rd("ContainerNo").ToString)
                lsv.SubItems.Add(rd("CartonFrom").ToString)
                lsv.SubItems.Add(rd("PO").ToString)
                lsv.SubItems.Add(rd("Style").ToString)
                lsv.SubItems.Add(rd("Color").ToString)
                lsv.SubItems.Add(rd("CartonSize").ToString)
                lsv.SubItems.Add(rd("ShipMent").ToString)
                lsv.SubItems.Add(rd("Factory").ToString)

                lstvwJobNo.Items.Add(lsv)
            End While
            Cursor.Current = Cursors.Default
            Return List_dgJobNo = 1
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
            Return List_dgJobNo = -1
        Finally
            If con.State = ConnectionState.Open Then
                con.Close()
            End If
        End Try
End Function

'获得排序的字段,并显示排序标记
Private Sub lstvwJobNo_ColumnClick(ByVal sender As Object, ByVal e As System.Windows.Forms.ColumnClickEventArgs) Handles lstvwJobNo.ColumnClick
        Select Case e.Column.ToString
            Case 0
                myField = "JOB.JOBNO"
            Case 1
                myField = "CONTAINERNO"
            Case 2
                myField = "CARTONFROM"
            Case 3
                myField = "PO"
            Case 4
                myField = "STYLE"
            Case 5
                myField = "COLOR"
            Case 6
                myField = "CARTONSIZE"
            Case 7
                myField = "SHIPMENT"
            Case 8
                myField = "FACTORY"
        End Select

        Dim i As Integer

        For i = 0 To 8
            lstvwJobNo.Columns(i).Text = lstvwJobNo.Columns(i).Text.Replace(" ▼", "").Replace(" ▲", "")
        Next

        If mySort = "ASC" Then
            lstvwJobNo.Columns(e.Column.ToString).Text = lstvwJobNo.Columns(e.Column.ToString).Text & " ▼"
            mySort = "DESC"
        Else
            mySort = "ASC"
            lstvwJobNo.Columns(e.Column.ToString).Text = lstvwJobNo.Columns(e.Column.ToString).Text & " ▲"
        End If

        Dim fJobNo As String = ""
        fJobNo = txtJobNo.Text.Trim()
        r = List_dgJobNo(fJobNo)
        lstvwJobNo.Refresh()

End Sub