Solved

Line 14: Incorrect syntax near '='.

Posted on 2007-04-04
12
241 Views
Last Modified: 2008-02-01
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
Comment
Question by:SirReadAlot
  • 5
  • 3
  • 2
  • +2
12 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18850362
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
 
LVL 10

Accepted Solution

by:
ksaul earned 500 total points
ID: 18850364
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
 
LVL 11

Expert Comment

by:Otana
ID: 18850381
Can you show the lines above that particular statement?
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:SirReadAlot
ID: 18850399
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
 

Author Comment

by:SirReadAlot
ID: 18850411
angel its

@SQL varchar(8000)
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18850455
then, I don't see any problem in there...
0
 

Author Comment

by:SirReadAlot
ID: 18850464
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
 
LVL 14

Expert Comment

by:mherchl
ID: 18850491
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
 

Author Comment

by:SirReadAlot
ID: 18850500
will do
0
 
LVL 10

Assisted Solution

by:ksaul
ksaul earned 500 total points
ID: 18850508
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18850509
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
 

Author Comment

by:SirReadAlot
ID: 18850774
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql server concatenate fields 10 36
Query Syntax 17 36
T-SQL: New to using transactions 9 31
How to use three values with DATEDIFF 3 26
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question