Server error HURRY!!
-
My procedure works great in the analyzer and in the development envitroment of asp.net. However when i excute a reader with the stroed procedure i am receiving a server error.
=============================================
DECLARE @SQL VARCHAR(8000)
IF EXISTS
(SELECT Email, FirstName, UserType FROM Users
WHERE
Email = @Admin_Email AND
FirstName = @Admin_FName
AND
UserType = @Admin_UserType)
BEGIN
SET
@SQL ='
Select UserID,
FirstName,
LastName,
CompanyName,
CompanyPhoto,
CompanyDescr,
UserType,
Website,
ActiveInd,
Address1,
Address2,
City,
Zip,
WkPhone,
HmPhone,
Fax,
Title,
Note,
StateCD,
CountryCD,
Email
From Users
WHERE ' + @GenericColumn + ' = ''' + @GenericValue + ''''
EXEC
(@SQL)
END
=================================================
Server Error in '/SuperCenter' Application.
Line 26: Incorrect syntax near '='.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Line 26: Incorrect syntax near '='.
Source Error:
Dam again!
-
exec sp_Executesql 'N(@SQL) -- (N stand for Nvarchar)
-
i am not sure what you are telling me here.
Can you please explain a little better?
thanks!
erik
Dam again!
-
Check the values you pass to your SP.
There must be single quote in one of parameters you pass to SP.
Did anybody tell you to get rid of dymamic SQL?
_____________
Code for TallyGenerator -
Add PRINT @SQL to see the resulting string, I'll bet it has to do with the parameters @GenericColumn or @GenericValue.
Andy
-
I have found that carriage return line feeds work okay in the editor, but there is a problem during execution. So, I use a cleanup function before running the EXEC(). The cleanup function replaces all non-printing characters spaces, then reduces any sets of multiple spaces to single space.
Hope this helps!
CREATE FUNCTION UDF_TRIM ( @INPUT_STRING VARCHAR(8000) )
/***********************************************************************************
Created By: SprocKing
Date: 20050401
Purpose: This function removes non-printing characters and extra spaces
from a string.
Modified by:
Date:
Note:
************************************************************************************/
/*TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_
DECLARE @INPUT_STRING VARCHAR(8000)
SET @INPUT_STRING = ' asdf ' + CHAR(13) + CHAR(10) + ' asdf asdf a d s f ' + CHAR(13) + CHAR(10)
print 'TEST' + dbo.UDF_TRIM( @INPUT_STRING ) + 'TEST'
TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_TESTING_*/
RETURNS VARCHAR(8000)
AS
BEGIN
IF ISNULL( @INPUT_STRING, '' ) = ''
RETURN('')
DECLARE @COUNTER INT
DECLARE @SIZE INT
DECLARE @SPACE_FLAG BIT
DECLARE @SPACE CHAR
DECLARE @OUTPUT_STRING VARCHAR(8000)
DECLARE @BUILD_STRING VARCHAR(8000)
DECLARE @CURRENT_CHARACTER CHAR
DECLARE @ASCII INT
SET @SPACE_FLAG = 0
SET @SPACE = CHAR(32)
SET @BUILD_STRING = ''
SET @OUTPUT_STRING = ''
SET @SIZE = LEN( RTRIM( @INPUT_STRING ) )
SET @COUNTER = 1
/***********************************************************************************
Replace all non-printing characters with spaces
************************************************************************************/
WHILE @COUNTER <= @SIZE
BEGIN
-- Step through input string
SET @CURRENT_CHARACTER = SUBSTRING( @INPUT_STRING, @COUNTER, 1 )
SET @ASCII = ASCII( @CURRENT_CHARACTER )
-- Concatenate printing characters and spaces
IF @ASCII BETWEEN 32 AND 126
SET @BUILD_STRING = @BUILD_STRING + @CURRENT_CHARACTER
ELSE
SET @BUILD_STRING = @BUILD_STRING + @SPACE
-- Increment counter
SET @COUNTER = @COUNTER + 1
END
-- Reset
SET @COUNTER = 1
SET @SIZE = LEN( @BUILD_STRING )
/***********************************************************************************
Remove extra spaces
************************************************************************************/
WHILE @COUNTER <= @SIZE
BEGIN
-- Step through new string
SET @CURRENT_CHARACTER = SUBSTRING( @BUILD_STRING, @COUNTER, 1 )
-- Concatenate printing characters and single spaces
IF NOT( @CURRENT_CHARACTER = @SPACE AND @SPACE_FLAG = 1 )
BEGIN
SET @OUTPUT_STRING = @OUTPUT_STRING + @CURRENT_CHARACTER
END
-- Set space flag
IF @CURRENT_CHARACTER = @SPACE
SET @SPACE_FLAG = 1
ELSE
SET @SPACE_FLAG = 0
-- Increment counter
SET @COUNTER = @COUNTER + 1
END
-- Remove any potential ending space before returning
RETURN( LTRIM( @OUTPUT_STRING ) )
END
-
Good God man,, What did you just send me?
Dam again!
-
It's a cleanup function. Use it just before running EXEC().
-- Concatenate your sql statement
SET @SQL = <<snip>>
-- Print for troubleshooting
PRINT @SQL
-- Remove non-printing characters
SET @SQL = dbo.UDF_TRIM( @SQL )
-- Execute
EXEC( @SQL )
-
o ok... thank you very much!!
I will add this now..
thanks!
Dam again!
-
-
Forgive me, I was a beginner when I first wrote it.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply