2016/04/28

insert  into  select插入数据  使用方法

http://dotnet.9sssd.com/aspnet/art/806

VB  给下dropdownlist设置值

设置数据表选中的值： dropdowslist.selectedIndex=-1 dropdownlist.Items.findbyvalue(你的值).selected=true 或 dropdownlist.Items.findbytext(你的值).selected=true for example: drop1.Items.FindByValue("5").Selected=true; drop1.Items...


行列转换
--select *  from ( select row_number() OVER () as rowno,SS.* from ( 
declare @sql varchar(8000) 
 select @sql= isnull(@sql+',','')+' max(case ProdBigTypeName when '''+ ProdBigTypeName +''' then LeftAmount else 0 end) ['+ ProdBigTypeName +']' 
 from(select distinct ProdBigTypeName from 
 (select m.ProductLineCD ,g.ProdBigTypeName ,m.CustCD ,j.CustNameCn,SUM(m.LeftAmount ) LeftAmount 
 from(select d.ProductCD,d.CustCD ,d.ProductLineCD ,isnull(d.onhand,0) - isnull(c.allocationqty,0) LeftQty 
 , isnull(d.Amount,0) - isnull(c.allocationamount,0) LeftAmount  
 from(select b.ProductLineCD,a.OverstockNO,a.CustCD, b.ProductCD,b.Onhand,b.Price,b.Amount,b.ProductDate  
 from Overstock a,OverstockDtl b where a.OverstockNO =b.OverstockNO and (a.Status ='N' or a.Status ='P'))d 
 left join (select  OverstockNO,ProductCD,ProductDate ,SUM(isnull(allocationqty,0)) allocationqty ,SUM(isnull(allocationamount,0)) allocationamount  
  from Allocation  group by OverstockNO,ProductCD,ProductDate ) c on d.OverstockNO =c.OverstockNO and d.ProductCD =c.ProductCD and d.ProductDate = c.ProductDate   ) m 
 left join Product f on m.ProductCD = f.ProdID left join ProdBigType g on g.ProdBigTypeID = f.ProdBigTypeID 
  left join Customer j on j.CustID = m.CustCD group by m.ProductLineCD,m.CustCD,j.CustNameCn,g.ProdBigTypeName) as LLL ) asa       
set @sql ='select CustNameCn,'+@sql+' from (select m.ProductLineCD ,g.ProdBigTypeName ,m.CustCD ,j.CustNameCn,SUM(m.LeftAmount ) LeftAmount 
from(select d.ProductCD,d.CustCD ,d.ProductLineCD ,isnull(d.onhand,0) - isnull(c.allocationqty,0) LeftQty 
, isnull(d.Amount,0) - isnull(c.allocationamount,0) LeftAmount from(select b.ProductLineCD,a.OverstockNO,a.CustCD, b.ProductCD,b.Onhand,b.Price,b.Amount,b.ProductDate 
from Overstock a,OverstockDtl b where a.OverstockNO =b.OverstockNO and (a.Status = ''N'' or a.Status = ''P''))d 
left join (select  OverstockNO,ProductCD,ProductDate ,SUM(isnull(allocationqty,0)) allocationqty ,SUM(isnull(allocationamount,0)) allocationamount from Allocation  
group by OverstockNO,ProductCD,ProductDate ) c on d.OverstockNO =c.OverstockNO and d.ProductCD =c.ProductCD and d.ProductDate = c.ProductDate   ) m 
left join Product f on m.ProductCD = f.ProdID left join ProdBigType g on g.ProdBigTypeID = f.ProdBigTypeID left join Customer j on j.CustID = m.CustCD 
group by m.ProductLineCD,m.CustCD,j.CustNameCn,g.ProdBigTypeName) as LLL group by CustNameCn' 
exec(@sql) 
--) SS  ) FF where 1=1  and FF.rowno >=  1 and FF.rowno <  26 order by FF.rowno

vb

 　　

    Private Function ConvertDataTable(ByRef dt As DataTable) As DataTable

        DeleteNullCol(dt)

        'Dim dt As DataTable = New DataTable("Datas")

        'dt.Columns.Add("姓名", Type.GetType("System.String"))
        'dt.Columns.Add("科目", Type.GetType("System.String"))
        'dt.Columns.Add("分数", Type.GetType("System.Int32"))
        'dt.Rows.Add(New Object() {"张三", "语文", 89})
        'dt.Rows.Add(New Object() {"张三", "数学", 90})
        'dt.Rows.Add(New Object() {"张三", "英语", 79})
        'dt.Rows.Add(New Object() {"张三", "地理", 70})
        'dt.Rows.Add(New Object() {"张三", "生物", 95})
        'dt.Rows.Add(New Object() {"李四", "语文", 87})
        'dt.Rows.Add(New Object() {"李四", "英语", 86})
        'dt.Rows.Add(New Object() {"李四", "地理", 82})
        'dt.Rows.Add(New Object() {"王五", "语文", 81})
        'dt.Rows.Add(New Object() {"王五", "数学", 70})
        'dt.Rows.Add(New Object() {"王五", "英语", 88})
        'dt.Rows.Add(New Object() {"王五", "生物", 96})

        Dim result As DataTable = New DataTable()
        result.Columns.Add(dt.Columns(1).ColumnName)
        Dim dtColumns As DataTable = New DataTable()
        dtColumns = dt.DefaultView.ToTable("dtColumns", True, dt.Columns(0).ColumnName.ToString())
        Dim colName As String
        For i As Integer = 0 To dtColumns.Rows.Count - 1

            colName = dtColumns.Rows(i)(0).ToString()
            result.Columns.Add(colName)
            result.Columns(i + 1).DefaultValue = "0"
        Next
        Dim drNew As DataRow = result.NewRow()
        Dim exRow() As DataRow
        Dim dr As DataRow = dt.NewRow()
        Dim custAfter As String = dt.Rows(0)(1).ToString()
        Dim custName As String
        Dim typeName As String
        Dim dvalue As String
        For Each dr In dt.Rows
            custName = dr(1).ToString
            drNew(0) = custAfter
            If (custName = custAfter) Then
                typeName = dr(0).ToString()
                dvalue = dr(2).ToString()
                drNew(typeName) = dvalue
            Else
                result.Rows.Add(drNew)
                drNew = result.NewRow()
                custName = dr(1).ToString
                drNew(0) = custAfter
                typeName = dr(0).ToString()
                dvalue = dr(2).ToString()
                drNew(typeName) = dvalue
            End If
            custAfter = custName
        Next
        result.Rows.Add(drNew)
        Return result

    End Function

删除空白列

    Private Function DeleteNullCol(ByRef dt As DataTable) As DataTable
        Dim dtRow As DataRow
        dtRow = dt.NewRow()
        Dim flg As Boolean
        For colId As Integer = dt.Columns.Count - 1 To 0 Step (-1)
            flg = True
            For rowId As Integer = 0 To dt.Rows.Count - 1

                If (String.IsNullOrEmpty(dt.Rows(rowId)(colId).ToString()) = False) Then
                    flg = False
                    Exit For
                End If

            Next
            If (flg = True) Then
                dt.Columns.RemoveAt(colId)
            End If

        Next
        dt.Columns.Remove(PFCC0101Field.PRODUCTLINECD_FIELD)
        dt.Columns.Remove(PFCC0101Field.CUSTCD_FIELD)
        dt.Columns.Remove("ROWNO")
        Return dt
    End Function

转载于:https://www.cnblogs.com/loveLu/p/5442948.html