主题：执行窗口命令后不能保存？

参考代码

 

Dim Cols1() As String = {"来源列一","来源列二","来源列三"}
Dim Cols2() As String = {"接收列一","接收列二","接收列三"}
For Each dr1 As DataRow In DataTables("表A").Select("")   
    Dim dr2 As DataRow = DataTables("表B").find("姓名 = '" & dr1("姓名") & "'")
    If dr2 Is Nothing Then
        dr2 = DataTables("表B").AddNew()
    End If
    For i As Integer = 0 To Cols1.Length -1
        dr2(Cols2(i)) = dr1(Cols1(i))
    Next
Next

谢谢大红袍老师！

 

我现在把成绩表转换成了竖表“成绩总表竖表”，希望统计出各学科的全校名次和等级。

 
此主题相关图片如下：截图20160512101732.png

 

我知道下面的代码有些问题，那正确的应该怎么写，要用SQl吗

Tables("成绩总表竖表").StopRedraw
Dim Count As Integer
Dim kms As List(of String) = DataTables("成绩总表竖表").GetValues("科目")
For Each km As String In kms
    '以下设置学科成绩按全校排名和等级
    Dim drss As List(of DataRow)=DataTables("成绩总表竖表").Select("[科目]= " & km, km & " DESC ")
    count=0
    For n As Integer = 0 To drss.Count - 1 '遍历所有行
        Count = Count + 1
        If n > 0 AndAlso drss(n)(km) = drss(n-1)(km) Then '如果总分和上一行相同
            drss(n)("全校名次") = drss(n-1)("全校名次") '则排名等于上一行
        Else
            drss(n)("全校名次") =count '设置排名
        End If
        If drss(n)("全校名次")<=drss.Count *0.15 Then
            drss(n)("等级")="A"
        ElseIf drss(n)("全校名次")<=drss.Count *0.45 Then
            drss(n)("等级")="B"
        ElseIf drss(n)("全校名次")<=drss.Count *0.75 Then
            drss(n)("等级")="C"
        ElseIf drss(n)("全校名次")<=drss.Count *0.95 Then
            drss(n)("等级")="D"
        Else
            drss(n)("等级")="E"
        End If
    Next
Next
Tables("成绩总表竖表").ResumeRedraw

 

 

此主题相关图片如下：截图20160512094254.png

 Dim drss As List(of DataRow)=DataTables("成绩总表竖表").Select("[科目]= " & km, km & " DESC ")

 

改成

 

  Dim drss As List(of DataRow)=DataTables("成绩总表竖表").Select("[科目]= '" & km & "'", km & " DESC ")

 

-------------如果还报错，做个实例发上来看看

还是同样提示“找不到列 高二_地理”

 

 

下载信息  [文件大小：   下载次数： ]

点击浏览该文件:成绩总表竖表.rar

Tables("成绩总表竖表").ResumeRedraw
Tables("成绩总表竖表").StopRedraw
Dim Count As Integer
Dim kms As List(of String) = DataTables("成绩总表竖表").GetValues("科目")
For Each km As String In kms
    '以下设置学科成绩按全校排名和等级
    Dim drss As List(of DataRow)=DataTables("成绩总表竖表").Select("[科目]='" & km & "'", "分数 DESC ")
    count=0
    For n As Integer = 0 To drss.Count - 1 '遍历所有行
        Count = Count + 1
        If n > 0 AndAlso drss(n)("分数") = drss(n-1)("分数") Then '如果总分和上一行相同
            drss(n)("全校名次") = drss(n-1)("全校名次") '则排名等于上一行
        Else
            drss(n)("全校名次") =count '设置排名
        End If
        If drss(n)("全校名次")<=drss.Count *0.15 Then
            drss(n)("等级")="A"
        ElseIf drss(n)("全校名次")<=drss.Count *0.45 Then
            drss(n)("等级")="B"
        ElseIf drss(n)("全校名次")<=drss.Count *0.75 Then
            drss(n)("等级")="C"
        ElseIf drss(n)("全校名次")<=drss.Count *0.95 Then
            drss(n)("等级")="D"
        Else
            drss(n)("等级")="E"
        End If
    Next
Next
Tables("成绩总表竖表").ResumeRedraw

嗯，谢谢，这个全校排名没问题了，是我把“分数”从高到低排序变成了“班级”了。

 

另外，如果统计各学科在各班级中的名次，我加了一段代码，主要是同时选择[班级]=bjs(i) And [科目]=km 这个用select语句写不清楚

下面两种都不对？

Select("[班级] ='" & bjs(i) & "'"  And "[科目]='" & km & "'", "分数 DESC")

Select("[班级] =" & bjs(i)  And "[科目]='" & km & "'", "分数 DESC")

Tables("成绩总表竖表").StopRedraw
Dim Count As Integer
Dim bjs As List(of String)=DataTables("成绩总表竖表").getvalues("班级","[班级] is not null")
Dim kms As List(of String) = DataTables("成绩总表竖表").GetValues("科目")
For Each km As String In kms
    '以下设置学科成绩按全校排名和等级
    Dim drss As List(of DataRow)=DataTables("成绩总表竖表").Select("[科目]='" & km & "'", "分数 DESC ")
    count=0
    For n As Integer = 0 To drss.Count - 1 '遍历所有行
        Count = Count + 1
        If n > 0 AndAlso drss(n)("分数") = drss(n-1)("分数") Then '如果总分和上一行相同
            drss(n)("全校名次") = drss(n-1)("全校名次") '则排名等于上一行
        Else
            drss(n)("全校名次") =count '设置排名
        End If
        If drss(n)("全校名次")<=drss.Count *0.15 Then
            drss(n)("等级")="A"
        ElseIf drss(n)("全校名次")<=drss.Count *0.45 Then
            drss(n)("等级")="B"
        ElseIf drss(n)("全校名次")<=drss.Count *0.75 Then
            drss(n)("等级")="C"
        ElseIf drss(n)("全校名次")<=drss.Count *0.95 Then
            drss(n)("等级")="D"
        Else
            drss(n)("等级")="E"
        End If
    Next

'下面计算各科班级名次
    For i As Integer = 0 To bjs.Count -1
        Dim drs As List(Of DataRow) = DataTables("成绩总表竖表").Select("[班级] =" & bjs(i)  And "[科目]='" & km & "'", "分数 DESC")
        Count = 0
        For n As Integer = 0 To drs.Count - 1
            Count = Count + 1
            If n > 0 AndAlso drs(n)("分数") = drs(n-1)("分数") Then
                drs(n)("班级名次") = drs(n-1)("班级名次")
            Else
                drs(n)("班级名次") = Count
            End If
        Next
    Next

Next
Tables("成绩总表竖表").ResumeRedraw

此主题相关图片如下：截图20160512160140.png

改成

 

        Dim drs As List(Of DataRow) = DataTables("成绩总表竖表").Select("[班级] ='" & bjs(i) & "' And [科目]='" & km & "'", "分数 DESC")