asked on

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

Guy Hengel [angelIII / a3]

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?

ASKER CERTIFIED SOLUTION

ksaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Otana

Can you show the lines above that particular statement?

SirReadAlot

ASKER

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

SirReadAlot

ASKER

angel its

@SQL varchar(8000)

Guy Hengel [angelIII / a3]

then, I don't see any problem in there...

SirReadAlot

ASKER

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

mherchl

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''

SirReadAlot

ASKER

will do

SOLUTION

ksaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Guy Hengel [angelIII / a3]

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

SirReadAlot

ASKER

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