March 31, 2003 at 2:30 pm
SQL2K, ColdFusion 5.0 on Win2k IIS 5.0
When I run this from Query Analyzer, it works great. When I run it from my cfstoredproc tag, it freaks out if I enter anything for @ContactLastName and throws the following error:
------------ERROR----------------
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ')'.
The error occurred while processing an element with a general identifier of (CFSTOREDPROC), occupying document position (196:2) to (196:80).
------------ERROR----------------
PROCEDURE dbo.GetAdvancedSearchCompaniesTest
@CompanyName as varchar(100),
@City AS varchar(100),
@TotalEmployeesFrom as varchar(100),
@NAICSCode AS varchar(100),
@TotalEmployeesTo as varchar(100),
@ContactLastName as varchar(100),
@PrivateComp as char(1),
@PublicComp as char(1),
@RegionalComp as char(1),
@NonProfitComp as char(1)
AS
DECLARE @strCommand as nvarchar(4000)
DECLARE @strPrivateCommand as nvarchar(4000)
DECLARE @strPublicCommand as nvarchar(4000)
DECLARE @strRegionalCommand as nvarchar(4000)
DECLARE @strNonProfitCommand as nvarchar(4000)
SET @strPrivateCommand= N'SELECT PRIVATE.lngCompID, strCompany, strAddress1, strCity, strState, lngZip, dbo.fncFormatPhone(strPhone) as strPhone, ''Private'' as CompType, PRIV_SIC.lngNAICSCode FROM PRIVATE INNER JOIN PRIV_SIC ON PRIVATE.lngCompID = PRIV_SIC.lngCompID WHERE '
SET @strPublicCommand= N'SELECT [PUBLIC].lngCompID, strCompany, strAddress1, strCity, strState, lngZip, dbo.fncFormatPhone(strPhone) as strPhone, ''Public'' as CompType, PUB_SIC.lngNAICSCode FROM [PUBLIC] INNER JOIN PUB_SIC ON [PUBLIC].lngCompID = PUB_SIC.lngCompID WHERE '
SET @strRegionalCommand= N'SELECT REGIONAL.lngCompID, strCompany, strAddress1, strCity, strState, lngZip, dbo.fncFormatPhone(strPhone) as strPhone, ''Regional'' as CompType, REG_SIC.lngNAICSCode FROM REGIONAL INNER JOIN REG_SIC ON REGIONAL.lngCompID = REG_SIC.lngCompID WHERE '
SET @strNonProfitCommand= N'SELECT NON_PROFIT.lngCompID, strCompany, strAddress1, strCity, strState, lngZip, dbo.fncFormatPhone(strPhone) as strPhone, ''Non_Profit'' as CompType, NON_SIC.lngNAICSCode FROM NON_PROFIT INNER JOIN NON_SIC ON NON_PROFIT.lngCompID = NON_SIC.lngCompID WHERE '
if len(@CompanyName)>0
BEGIN
SET @strPrivateCommand=@strPrivateCommand + 'strCompany LIKE ''%' + @CompanyName + '%'' AND '
SET @strPublicCommand=@strPublicCommand + 'strCompany LIKE ''%' + @CompanyName + '%'' AND '
SET @strRegionalCommand=@strRegionalCommand + 'strCompany LIKE ''%' + @CompanyName + '%'' AND '
SET @strNonProfitCommand=@strNonProfitCommand + 'strCompany LIKE ''%' + @CompanyName + '%'' AND '
END
if len(@City)>0
BEGIN
SET @strPrivateCommand=@strPrivateCommand + 'strCity LIKE ''%' + @City + '%'' AND '
SET @strPublicCommand=@strPublicCommand + 'strCity LIKE ''%' + @City + '%'' AND '
SET @strRegionalCommand=@strRegionalCommand + 'strCity LIKE ''%' + @City + '%'' AND '
SET @strNonProfitCommand=@strNonProfitCommand + 'strCity LIKE ''%' + @City + '%'' AND '
END
if len(@TotalEmployeesFrom)>0
BEGIN
SET @strPrivateCommand=@strPrivateCommand + 'lngEmployees >= ' + @TotalEmployeesFrom + ' AND '
SET @strPublicCommand=@strPublicCommand + 'lng200Empl >= ' + @TotalEmployeesFrom + ' AND '
SET @strRegionalCommand=@strRegionalCommand + 'lngEmployees >= ' + @TotalEmployeesFrom + ' AND '
SET @strNonProfitCommand=@strNonProfitCommand + 'lngEeCount >= ' + @TotalEmployeesFrom + ' AND '
END
if len(@TotalEmployeesTo)>0
BEGIN
SET @strPrivateCommand=@strPrivateCommand + 'lngEmployees <= ' + @TotalEmployeesTo + ' AND '
SET @strPublicCommand=@strPublicCommand + 'lng200Empl <= ' + @TotalEmployeesTo + ' AND '
SET @strRegionalCommand=@strRegionalCommand + 'lngEmployees <= ' + @TotalEmployeesTo + ' AND '
SET @strNonProfitCommand=@strNonProfitCommand + 'lngEeCount <= ' + @TotalEmployeesTo + ' AND '
END
if len(@NAICSCode)>0
BEGIN
SET @strPrivateCommand=@strPrivateCommand + 'lngNAICSCode = ' + @NAICSCode + ' AND '
SET @strPublicCommand=@strPublicCommand + 'lngNAICSCode = ' + @NAICSCode + ' AND '
SET @strRegionalCommand=@strRegionalCommand + 'lngNAICSCode = ' + @NAICSCode + ' AND '
SET @strNonProfitCommand=@strNonProfitCommand + 'lngNAICSCode = ' + @NAICSCode + ' AND '
END
if len(@ContactLastName)>0
BEGIN
SET @strPrivateCommand=@strPrivateCommand + 'PRIVATE.lngCompID IN (SELECT lngCompID FROM PRIV_OFCRS WHERE strLastName LIKE ''' + @ContactLastName + '%'') AND '
SET @strPublicCommand=@strPublicCommand + '[PUBLIC].lngCompID IN (SELECT lngCompID FROM PUB_OFCRS WHERE strLastName LIKE ''' + @ContactLastName + '%'') AND '
SET @strRegionalCommand=@strRegionalCommand + 'REGIONAL.lngCompID IN (SELECT lngCompID FROM REG_OFCRS WHERE strLastName LIKE ''' + @ContactLastName + '%'') AND '
SET @strNonProfitCommand=@strNonProfitCommand + 'NON_PROFIT.lngCompID IN (SELECT lngCompID FROM NON_OFCRS WHERE strLastName LIKE ''' + @ContactLastName + '%'') AND '
END
SET @strCommand=@strPrivateCommand + '''' + @PrivateComp + ''' = ''Y'' UNION ' + @strPublicCommand + '''' + @PublicComp + ''' = ''Y'' UNION ' + @strRegionalCommand + '''' + @RegionalComp + ''' = ''Y'' UNION ' + @strNonProfitCommand + '''' + @NonProfitComp + ''' = ''Y'' ORDER BY strCompany'
EXECUTE sp_executesql @strCommand
Here is how I call it from my CF page:
<cfstoredproc procedure="GetAdvancedSearchCompaniesTest" datasource="FactBook_Read">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" variable="@CompanyName" value="#CompanyName#" null="no">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" variable="@City" value="#City#" null="no">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" variable="@TotalEmployeesFrom" value="#TotalEmployeesFrom#" null="no">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" variable="@NAICSCode" value="#NAICSCode#" null="no">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" variable="@TotalEmployeesTo" value="#TotalEmployeesTo#" null="no">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" variable="@ContactLastName" value="#ContactLastName#" null="no">
<cfprocparam type="In" cfsqltype="CF_SQL_CHAR" variable="@PrivateComp" value="#PrivateComp#" null="no">
<cfprocparam type="In" cfsqltype="CF_SQL_CHAR" variable="@PublicComp" value="#PublicComp#" null="no">
<cfprocparam type="In" cfsqltype="CF_SQL_CHAR" variable="@RegionalComp" value="#RegionalComp#" null="no">
<cfprocparam type="In" cfsqltype="CF_SQL_CHAR" variable="@NonProfitComp" value="#NonProfitComp#" null="no">
<cfprocresult name="AdvancedSearchCompanies">
</cfstoredproc>
I'm totally stumped as to why this will only run in query analyzer and not in my page. Every other variable passes and works great, but @ContactLastName won't for some reason if I'm running it through the cfstoredproc tag.
Thanx for any help ya'll can give,
Mischa
April 1, 2003 at 12:40 am
I have done a fair amount of ASP work calling SQL Server stored procedures (which I admit is not the same as ColdFusion) and in these situations I would try the following:
1. Print out on the client the value of ContactLastName that you pass to the function. Sometimes the value you pass is not what you expect.
2. Change the stored procedure to output @strCommand so that when you call it from ColdFusion you can see exactly what it is creating.
Sometimes there is something going on in the client app that is not what you expect. If you have better debugging tools use them but in ASP the only real way to debug is to add in print statements.
Jeremy
April 1, 2003 at 8:39 am
I agree Jeremy - sometimes printing the SQL command is the easiest way to determine a problem.
My first impression is that I don't see code to compensate for those names that have single quotes O'Reilly, etc. This plays havoc with the SQL command.
Guarddata-
April 1, 2003 at 11:54 am
I'm removing single quotes on the submission application. When I output the strCommand in Query Analyzer, it comes back fine. However, when I output to my CF app, I only get the first few lines back, which is possibly part of the error. I've completely changed my approach at this point, because my T-SQL skills simply can deal with this much conditional processing.
I've moved this part of the application to CF, so I have more flexibility (flexibility due to my programming limitations).
Thank you all for your suggestions as they certainly helped me along in making my decision. Hopefully I'll get close to at least one of my deadlines.
Mischa
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply