Solved

Line 14: Incorrect syntax near '='.

Posted on 2007-04-04
12
233 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
 

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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

708 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now