Solved

Line 14: Incorrect syntax near '='.

Posted on 2007-04-04
12
252 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +2
12 Comments
 
LVL 143

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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

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 143

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 143

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

Database Solutions Engineer FAQs

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller single-server environments.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

630 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