控件:

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