|
The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
Forum to discuss ADO specific questions for use with SalesLogix. View the code of conduct for posting guidelines.
|
|
|
|
Incorrect Syntax Error
Posted: 18 May 10 5:16 PM
|
Hi All,
I have the following query embedded in my .Net code:
select distinct
u.userid,
u.username,
us.defaultseccodeid,
Convert(Varchar(3), Left(IsNull(u.FirstName, ''), 1) + Left(IsNull(u.MiddleName, ''), 1) + Left(IsNull(u.LastName, ''), 1)) AS Initials
from seccode s
inner join secrights sr on sr.seccodeid = s.seccodeid
inner join userinfo u on u.userid = sr.accessid
inner join usersecurity us on u.userid = us.userid
where s.seccodetype = 'G'
and s.seccodeid = 'F6UJ9A00000H'
AND us.Type <> 'R' ORDER BY u.username
and am getting an error "Incorrect syntax near ')'" when I run it. It runs fine in SQL Server Management Studio.
I thought maybe the SLX provider was choking on the Left command if there was nothing in the field, so I changed it to the following:
select distinct
u.userid,
u.username,
us.defaultseccodeid,
Convert(Varchar(3), CASE IsNull(u.FirstName, '') WHEN '' THEN '' ELSE Left(IsNull(u.FirstName, ''), 1) END +
CASE IsNull(u.MiddleName, '') WHEN '' THEN '' ELSE Left(IsNull(u.MiddleName, ''), 1) END +
CASE IsNull(u.LastName, '') WHEN '' THEN '' ELSE Left(IsNull(u.LastName, ''), 1) END) AS Initials
from seccode s
inner join secrights sr on sr.seccodeid = s.seccodeid
inner join userinfo u on u.userid = sr.accessid
inner join usersecurity us on u.userid = us.userid
where s.seccodetype = 'G'
and s.seccodeid = 'F6UJ9A00000H'
AND us.Type <> 'R' ORDER BY u.username
This results in the error "Incorrect syntax near the keyword 'WHEN'". Again, it runs fine in SQL Server Management Studio.
This error has just come up since we upgraded from SLX 7.2 to 7.5.2. It was running fine before.
Any ideas?
Leon |
|
|
|
Re: Incorrect Syntax Error
Posted: 19 May 10 12:17 AM
|
Based on the fact that the error message is Incorrect Syntax, that means that the code is being parsed but fails on Execution.
So, since I ran SLX Profiler and capture what the Parser is doing for you:
select distinct
u.userid,
u.username,
us.defaultseccodeid,
CONVERT(Varchar(3),Left(IsNull(u.FirstName, ''), 1) + Left(IsNull(u.MiddleName, ''), 1) + Left) AS Initials
from seccode s
inner join secrights sr on sr.seccodeid = s.seccodeid
inner join userinfo u on u.userid = sr.accessid
inner join usersecurity us on u.userid = us.userid
where s.seccodetype = 'G'
and s.seccodeid = 'F6UJ9A00000H'
AND us.Type <> 'R' ORDER BY u.username
So, I decided to replace the Convert with a Cast, and guess what, that works.
I don't know why you would want to cast it as varchar(3), never the less it works better with CAST as shown below:
CAST(Left(IsNull(u.FirstName, ''), 1) + Left(IsNull(u.MiddleName, ''), 1) + Left(IsNull(u.LastName, ''), 1) AS VARCHAR(3)) AS Initials
|
|
|
|
Re: Incorrect Syntax Error
Posted: 19 May 10 9:17 AM
|
Thanks Raul. That worked.
I needed to cast to a varchar because without it, just concatenating the fields, .Net wasn't recognizing the data type of the returned value. |
|
|
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
 Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|