September 24, 2002 at 10:27 am
"The identifier that starts with 'INSERT tbl_discs (Code, Price .... ArtistID)' is too long. Maximum length is 128."
The code it throws it for is :
Declare @strSQL varchar(512)
SELECT @strSQL = "INSERT tbl_Discs (Code, Price, BrandID, FormatID, MarkAsNew, PackOnly, Discontinued, PLU, CategoryId, BrandSeriesID, ArtistID) VALUES ('" + @strCode + "'," + convert(varchar, @monPrice) + "," + convert(varchar,@intBrandID) + "," + convert(varchar,@intFormatID) + "," + convert(varchar,@boolNew) + "," + convert(varchar,@boolpackOnly) + ", " + convert(varchar,@boolDiscontinued) + ", '" + @strPLU + "', " + convert(varchar,@intCategoryID) + ", " + convert(varchar,@intBrandSeriesID) + ", " + convert(varchar,@intArtistID) + ")"
September 24, 2002 at 11:00 am
I'm assumming this error is because you have
QUOTED_IDENTIFIER set to ON. When QUOTED_IDENTIFIER is ON SQL thinks you are using double qoutes around identifiers. You have two option to fix this problem.
1) Turn QUOTED_IDENTIFIER OFF prior to issuing this command like so:
SET QUOTED_IDENTIFIER OFF
2) Or rewrite you code to not use double quotes.
I normally use option 2. Your call.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 24, 2002 at 11:51 am
If i remove double quotes and use single instead how should I enclose a value string in the query ?
What happens when a single quote appears in a value string as well ?
i use the standard replace(strXYZ, "'", "''") to make any strings safe before passing them to the SP.
September 24, 2002 at 12:16 pm
Here are two options for you. You can either use two single quotes to represent a single single quote, or you can use the char function insert the character. Take you pick.
declare @STR varchar (100)
set @STR = 'values (''abc'')'
print @STR
set @STR = 'values (' + char(39) + 'abc' + char(39) + ')'
print @STR
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 25, 2002 at 4:35 am
To save headache with your code there is no need to change the replace function just double the ' quotes you already have in your select statement. It will look likes this when done.
Declare @strSQL varchar(512)
SELECT @strSQL = 'INSERT tbl_Discs (Code, Price, BrandID, FormatID, MarkAsNew, PackOnly, Discontinued, PLU, CategoryId, BrandSeriesID, ArtistID) VALUES (''' + @strCode + ''',' + convert(varchar, @monPrice) + ',' + convert(varchar,@intBrandID) + ',' + convert(varchar,@intFormatID) + ',' + convert(varchar,@boolNew) + ',' + convert(varchar,@boolpackOnly) + ', ' + convert(varchar,@boolDiscontinued) + ', ''' + @strPLU + ''', ' + convert(varchar,@intCategoryID) + ', ' + convert(varchar,@intBrandSeriesID) + ', ' + convert(varchar,@intArtistID) + ')'
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
September 25, 2002 at 5:54 am
so can I just check ....
I should use an apostrophe instead of speach marks to enclose my statement.
e.g.
set @STR = 'statement'
continue replacing single '(apostrophe) in strings with two apostrophies.
and where I would enclose a value using a single apostrophe originally use 2 apostorphies instead ?
Am i right ?
Thanks
Dan
September 25, 2002 at 8:05 am
Yep. I think you got it....
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply