September 19, 2005 at 2:41 pm
I have three sp's, each for a different data type.
For some reason the char type gives this error. Invalid column name "Hello"
Have converted to simple query for testing
declare @column varchar(50)
declare @value varchar(50)
declare @key int
set @column='FieldData'
set @value='hello'
set @key=58
print @column
print @value
exec('UPDATE [PrintFields] Set '+@column+' = '+@value+'
where PrintFields.PrintfieldKey = '+@Key+'')
the other sp where @value is type int works perfectly and code is identicle except for the @value datatype.
any ideas
September 19, 2005 at 2:46 pm
Don't do that in dynamic sql, you'll be totally be missing the point of sps.
update tablename set colname = @Value where key = @key
Also read this :
September 20, 2005 at 5:26 am
Hi Kevin
No big deal - you just have some quotes missing. You can either:
exec('UPDATE [PrintFields] Set ' + @column + ' = ''' + @value + ''' where PrintFields.PrintfieldKey = ' + @key)
(the @value value must be in quotes - but you knew that anyway!). Or if the proliferation of quotes is too hard to read then add:
declare @s-2 varchar(2000)
set @s-2 = 'UPDATE [PrintFields] Set ' + @column + ' = ' + char(39) + @value + char(39) + ' where PrintFields.PrintfieldKey = ' + ltrim(str(@Key))
--print @s-2
exec (@s)
Cheers.
September 20, 2005 at 10:48 am
Hi Igor
Have you tried your code?
Regards.
PS As I said the quotes get a bit messy - just for the record they're all single quotes - no " (double quotes).
September 20, 2005 at 11:12 am
That's why I read the article and stopped using dynamic sql... too much troubles for no rewards at all (most of the time).
September 20, 2005 at 12:55 pm
Thanks for the input guys. I have removed the sp's as suggested although this has created alot more code in my app. Still needed to add the extra quotes though.This is for a new VB.Net 2005 app that I am writing for learning. Once again cheers you saved me after spending an entire day trying to stumble through it.
Regards
Kev
September 20, 2005 at 12:58 pm
We never said to remove the sp, we suggested you avoid dynamic sql IN THE SP. Can you post the whole code so we can show you how to convert it to static sql?
September 21, 2005 at 12:53 pm
thanks anyway but I'll leave it as it is now and move on. As I said this is all part of a learning curve, and as the old saying goes. "If it aint broke, don't fix it"
cheers
Kev
September 21, 2005 at 12:55 pm
Good... another hacker playground is born. Don't forget to update your resume cause that'll be your fault if the server gets destroyed or data stolen.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply