• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 267
  • Last Modified:

Line 14: Incorrect syntax near '='.

Hi experts,

i got the above error whilst running this code

SELECT   @SQL = 'SELECT  
      licPolicy.numUniqueDevices,
      licPolicy.matchBaseLicOnly,        
      product.prodID,
      product.prodCode,
      product.prodName,
      product.prodDesc,
      product.pfID,
      product.prodLicName,
      product.prodLicEncKey,
      product.supported,
      case  product.supported when 1 then ''Yes'' else ''No'' end as status,
       product.licVersion,
       product.ProductFamily = CASE  product.pfID
                    WHEN 10 THEN ''PC*MILER''  
                    WHEN 15 THEN ''FleetSuite''
                    WHEN 100 THEN ''CoPilot''  
                    ELSE ''CoPilot|Pocket''  
END
FROM
  licPolicy INNER JOIN
                      product ON licPolicy.prodID = product.prodID
ORDER BY ' + @SortColumn + '  ' + @Direction
exec(@SQL)
end

i can really see the mistake!! pls help

here is the calling code
=============================================
 Public Shared Function GetAllProduct(ByVal sortCriteria As String, ByVal sortDir As String) As ArrayList
        Dim arr As New ArrayList
        Dim DBConnection As SqlConnection = Connection()
        DBConnection.Open()
        Dim cmdProduct As SqlCommand = New SqlCommand("SPR_GETALL_PRODUCTS", DBConnection)
        cmdProduct.CommandType = CommandType.StoredProcedure
        cmdProduct.Parameters.Add("@SortColumn", sortCriteria)
        cmdProduct.Parameters.Add("@Direction", sortDir)
        Try
            Dim drProduct As SqlDataReader
            drProduct = cmdProduct.ExecuteReader()
            While drProduct.Read()
                Dim productProfile As New ProductProfile
                productProfile.prodID = DataUtil.IsNull(drProduct.Item("prodID"), "")
                productProfile.prodCode = DataUtil.IsNull(drProduct.Item("prodCode"), "")
                productProfile.prodName = DataUtil.IsNull(drProduct.Item("prodName"), "")
                productProfile.prodDesc = DataUtil.IsNull(drProduct.Item("prodDesc"), "")
                productProfile.pfID = DataUtil.IsNull(drProduct.Item("ProductFamily"), "")
                productProfile.prodLicName = DataUtil.IsNull(drProduct.Item("prodLicName"), "")
                productProfile.prodLicEncKey = DataUtil.IsNull(drProduct.Item("prodLicEncKey"), "")
                productProfile.supported = DataUtil.IsNull(drProduct.Item("status"), "")
                productProfile.licVersion = DataUtil.IsNull(drProduct.Item("licVersion"), "")
                'Gets details from licPolicy Table
                productProfile.numUniqueDevices = DataUtil.IsNull(drProduct.Item("numUniqueDevices"), "")
                productProfile.matchBaseLicOnly = DataUtil.IsNull(drProduct.Item("matchBaseLicOnly"), "")
                arr.Add(productProfile)
            End While
            Return arr
        Finally
            DBConnection.Close()
        End Try
    End Function


thanks
0
SirReadAlot
Asked:
SirReadAlot
  • 5
  • 3
  • 2
  • +2
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please try out this:

SET @SQL = 'SELECT  
      licPolicy.numUniqueDevices,
      licPolicy.matchBaseLicOnly,        
      product.prodID,
      product.prodCode,
      product.prodName,
      product.prodDesc,
      product.pfID,
      product.prodLicName,
      product.prodLicEncKey,
      product.supported,
      case  product.supported when 1 then ''Yes'' else ''No'' end as status,
       product.licVersion,
       product.ProductFamily = CASE  product.pfID
                    WHEN 10 THEN ''PC*MILER''  
                    WHEN 15 THEN ''FleetSuite''
                    WHEN 100 THEN ''CoPilot''  
                    ELSE ''CoPilot|Pocket''  
END
FROM
  licPolicy INNER JOIN
                      product ON licPolicy.prodID = product.prodID
ORDER BY '
exec(@SQL + @SortColumn + '  ' + @Direction)

now, how big is your variable @sql declared?

0
 
ksaulCommented:
When you alias the CASE Statement you can give it a field name like "ProductFamily" but not product.ProductFamily

ProductFamily = CASE  product.pfID
                    WHEN 10 THEN ''PC*MILER''  
                    WHEN 15 THEN ''FleetSuite''
                    WHEN 100 THEN ''CoPilot''  
                    ELSE ''CoPilot|Pocket''  
END
0
 
OtanaCommented:
Can you show the lines above that particular statement?
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
SirReadAlotAuthor Commented:
ok

ALTER PROCEDURE dbo.SPR_GETALL_PRODUCTS
@SortColumn varchar(255),
@Direction varchar(10)
AS
Declare @SQL varchar(8000)
BEGIN
SET NOCOUNT ON
SELECT   @SQL = 'SELECT  
      licPolicy.numUniqueDevices,
      licPolicy.matchBaseLicOnly,        
      product.prodID,
      product.prodCode,
      product.prodName,
      product.prodDesc,
      product.pfID,
      product.prodLicName,
      product.prodLicEncKey,
      product.supported,
      case  product.supported when 1 then ''Yes'' else ''No'' end as status,
       product.licVersion,
       product.ProductFamily = CASE  product.pfID
                    WHEN 10 THEN ''PC*MILER''  
                    WHEN 15 THEN ''FleetSuite''
                    WHEN 100 THEN ''CoPilot''  
                    ELSE ''CoPilot|Pocket''  
END
FROM
  licPolicy INNER JOIN
                      product ON licPolicy.prodID = product.prodID
ORDER BY ' + @SortColumn + '  ' + @Direction
exec(@SQL)
end






GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


0
 
SirReadAlotAuthor Commented:
angel its

@SQL varchar(8000)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
then, I don't see any problem in there...
0
 
SirReadAlotAuthor Commented:
i still have the same errors with

ALTER PROCEDURE dbo.SPR_GETALL_PRODUCTS
@SortColumn varchar(255),
@Direction varchar(10)
AS
Declare @SQL varchar(8000)
BEGIN
SET NOCOUNT ON
SET @SQL = 'SELECT  
      licPolicy.numUniqueDevices,
      licPolicy.matchBaseLicOnly,        
      product.prodID,
      product.prodCode,
      product.prodName,
      product.prodDesc,
      product.pfID,
      product.prodLicName,
      product.prodLicEncKey,
      product.supported,
      case  product.supported when 1 then ''Yes'' else ''No'' end as status,
       product.licVersion,
       product.ProductFamily = CASE  product.pfID
                    WHEN 10 THEN ''PC*MILER''  
                    WHEN 15 THEN ''FleetSuite''
                    WHEN 100 THEN ''CoPilot''  
                    ELSE ''CoPilot|Pocket''  
END
FROM
  licPolicy INNER JOIN
                      product ON licPolicy.prodID = product.prodID
ORDER BY '
exec(@SQL + @SortColumn + '  ' + @Direction)

--exec(@SQL)
end
0
 
mherchlCommented:
ksaul is right, try

       ProductFamily = CASE  product.pfID
                    WHEN 10 THEN ''PC*MILER''  
                    WHEN 15 THEN ''FleetSuite''
                    WHEN 100 THEN ''CoPilot''  
                    ELSE ''CoPilot|Pocket''  

instead of

       product.ProductFamily = CASE  product.pfID
                    WHEN 10 THEN ''PC*MILER''  
                    WHEN 15 THEN ''FleetSuite''
                    WHEN 100 THEN ''CoPilot''  
                    ELSE ''CoPilot|Pocket''  
0
 
SirReadAlotAuthor Commented:
will do
0
 
ksaulCommented:
The syntax error on line 14 is because of an invalid alias name:
product.ProductFamily = CASE  product.pfID
                    WHEN 10 THEN ''PC*MILER''  
                    WHEN 15 THEN ''FleetSuite''
                    WHEN 100 THEN ''CoPilot''  
                    ELSE ''CoPilot|Pocket''  

should be:

ProductFamily = CASE  product.pfID
                    WHEN 10 THEN ''PC*MILER''  
                    WHEN 15 THEN ''FleetSuite''
                    WHEN 100 THEN ''CoPilot''  
                    ELSE ''CoPilot|Pocket''  
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I now see the problem:
>       product.ProductFamily  = CASE  product.pfID
> ...
> END

what are you trying to achieve there?

either it has to be:
       product.ProductFamily  , CASE  product.pfID
...
END somecolname

or   maybe this:    
CASE  product.pfID
...
END        ProductFamily  

or, maybe this:
       product.ProductFamily +  CASE  product.pfID
...
END



0
 
SirReadAlotAuthor Commented:
thanks guys
i used this
ALTER PROCEDURE dbo.SPR_GETALL_PRODUCTS
@SortColumn varchar(255),
@Direction varchar(10)
AS
Declare @SQL varchar(8000)
BEGIN
SET NOCOUNT ON
SELECT   @SQL = 'SELECT  
      licPolicy.numUniqueDevices,
      licPolicy.matchBaseLicOnly,        
      product.prodID,
      product.prodCode,
      product.prodName,
      product.prodDesc,
      product.pfID,
      product.prodLicName,
      product.prodLicEncKey,
      product.supported,
      case  product.supported when 1 then ''Yes'' else ''No'' end as status,
       product.licVersion,
       ProductFamily = CASE  product.pfID
                    WHEN 10 THEN ''PC*MILER''  
                    WHEN 15 THEN ''FleetSuite''
                    WHEN 100 THEN ''CoPilot''  
                    ELSE ''CoPilot|Pocket''  
END
FROM
  licPolicy INNER JOIN
                      product ON licPolicy.prodID = product.prodID
ORDER BY ' + @SortColumn + '  ' + @Direction
exec(@SQL)
end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 5
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now