Click here to Skip to main content
15,867,771 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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

VB
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
Posted
Comments
Shahan Ayyub 17-Mar-13 12:36pm    
Which of the above query causes problem ? can you post the query you get in 'strSQL3' and 'strSQL4' variables ?
Shahan Ayyub 17-Mar-13 12:53pm    
In 2nd query, try enclosing value of "Dset3.Rows(0).Item("PaPeR_Type")" in "[ ]" something like the example in below link. Check out how they write [MON],[TUE],..... etc. http://blogs.msdn.com/b/spike/archive/2009/03/03/pivot-tables-in-sql-server-a-simple-sample.aspx

1 solution

Do not build queries in the code. Do it on server side. For example, you can create stored procedure, like:
SQL
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[^]
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900