Solved

Line 14: Incorrect syntax near '='.

Posted on 2007-04-04
12
248 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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

Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

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 for XML PATH giving wrong results. 6 59
Server 2012 r2 and SQL 2014 6 34
rolling count by date, hour query 7 30
What is needed to become a DBA? 7 54
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

734 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