DGV Disply中的PIVOT表错误 [英] PIVOT Table Error in DGV Disply

问题描述

嗨..所有



我在PIVOT TALBE中遇到一些错误

对象或列名称缺失或为空。对于SELECT INTO语句，请验证每列是否具有名称。对于其他语句，请查找空别名。不允许使用定义为或[]的别名。将别名更改为有效名称。

我使用的代码

  Dim  strSQL3 作为  String  =   SELECT STUFF（（SELECT DISTINCT'，['+ Paper1 +']，['+ Paper2 +']，['+ Paper3 +']，['+ Paper4 +']，['+ Paper5 +']，['+ Paper6 +']，['+ Paper7 +']'FROM Exam_PaperDetails WHERE Class = '& cboClass.Text&  '和Exam_Type ='& cboExam.Text&  '对于XMl路径（''）），1,1，''）AS PaPeR_Type  
  Dim  DaAp3 作为 新 SqlDataAdapter（strSQL3，con）
  Dim  Dset3  As  新 DataTable 
 DaAp3.Fill（Dset3）
 
 
 
  Dim  strSQL4 作为 字符串 =  （SELECT * FROM（SELECT stdAdmission.Regno，stdAdmission.Sname，stdAdmission.class FROM stdAdmission WHERE Class ='& cboClass.Text&  '）SA PIVOT（最大（类）FOR类IN（ + Dset3.Rows（ 0 ）。Item（  PaPeR_Type）+  ））as PVT） 
  Dim  DaAp4 作为 新 SqlDataAdapter（strSQL4，con）
  Dim  Dset4  As  新 DataTable 
 DaAp4.Fill（ Dset4）
 DGV1.DataSource = Dset4 

请告诉我如何解决此错误

解决方案

不要在代码中构建查询。在服务器端执行此操作。例如，您可以创建存储过程，例如：

  CREATE  程序 CreateMyPivot 
  @ class   NVARCHAR （ 30 ），
  @ exam   NVARCHAR （ 30 ）
  AS  
  BEGIN  
  DECLARE   @cols   NVARCHAR （ 300 ）
  DECLARE   @ dt   NVARCHAR （ 2000 ）
  DECLARE   @ pt   NV ARCHAR （ 4000 ）
 
  SET   @cols  = '  [Paper1]，[Paper2]，[Paper3]，[论文4]，[论文5]，[论文6]，[论文7]' 
 
   -   或 
   -    SET @cols = SELECT STUFF（（SELECT DISTINCT'] ，['+ [PaperType]  
   -    FROM Exam_PaperDetails  
   -    ORDER BY']，['+ [PaperType]  
   -    WHERE Class = @ class and Exam_Type = @ exam  
  -    FOR XML PATH（''）），1,2，''）+']' 
 
  SET   @ dt  = '  SELECT Regno，Sname，类FROM stdAdmission WHERE Class =' +  @ class  
 
  SET   @ pt  = '  SELECT Regno，Sname，class，' +  @cols  + ' ' + 
 '  FROM（' +  @ dt  + ' ）AS DT' + 
 '  PIVOT（MAX（[AgregateField]）FOR IN类（' + < span class =code-sdkkeyword> @cols  + ' ））AS PT' 
  EXEC （ @ pt ）
 
  END  

如何从VB.NET执行存储过程？

如何：执行存储过程返回行 [ ^ ]

使用存储过程与命令 [ ^ ]

Hi.. All

I got Some Error in PIVOT TALBE

An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.

HEre I USED CODE

Dim strSQL3 As String = "SELECT STUFF((SELECT DISTINCT ',['+ Paper1 +'],['+ Paper2 +'],['+ Paper3 +'],['+ Paper4 +'],['+ Paper5 +'],['+ Paper6 +'],['+ Paper7 +']' FROM Exam_PaperDetails WHERE Class='" & cboClass.Text & "' and Exam_Type='" & cboExam.Text & "'  For XMl Path('')), 1, 1, '') AS PaPeR_Type"
Dim DaAp3 As New SqlDataAdapter(strSQL3, con)
 Dim Dset3 As New DataTable
 DaAp3.Fill(Dset3)



 Dim strSQL4 As String = "(SELECT * FROM (SELECT stdAdmission.Regno, stdAdmission.Sname, stdAdmission.class FROM stdAdmission WHERE Class='" & cboClass.Text & "') SA  PIVOT (Max(class) FOR class IN (" + Dset3.Rows(0).Item("PaPeR_Type") + "))as PVT)"
 Dim DaAp4 As New SqlDataAdapter(strSQL4, con)
 Dim Dset4 As New DataTable
 DaAp4.Fill(Dset4)
 DGV1.DataSource = Dset4

Please Tell me How can i slove this error

解决方案

Do not build queries in the code. Do it on server side. For example, you can create stored procedure, like:

CREATE PROCEDURE CreateMyPivot
     @class NVARCHAR(30),
     @exam  NVARCHAR(30)
AS
BEGIN
    DECLARE @cols NVARCHAR(300)
    DECLARE @dt NVARCHAR(2000)
    DECLARE @pt NVARCHAR(4000)

    SET @cols = '[Paper1],[Paper2],[Paper3],[Paper4],[Paper5],[Paper6],[Paper7]'

    --or
    --SET @cols = SELECT STUFF((SELECT DISTINCT '],[' + [PaperType]
    --        FROM Exam_PaperDetails
    --        ORDER BY '],[' + [PaperType]
    --        WHERE Class=@class and Exam_Type=@exam
    --    FOR XML PATH('')), 1, 2, '') + ']'

    SET @dt = 'SELECT Regno, Sname, class FROM stdAdmission WHERE Class=' + @class

    SET @pt = 'SELECT Regno, Sname, class, ' + @cols + ' ' +
              'FROM (' + @dt + ') AS DT ' +
              'PIVOT (MAX([AgregateField]) FOR class IN (' + @cols + ')) AS PT'
    EXEC(@pt)

END

How to execute stored procedure from VB.NET?
How to: Execute a Stored Procedure that Returns Rows[^]
Using Stored Procedures with a Command[^]

这篇关于DGV Disply中的PIVOT表错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！