SQL wont work - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL wont work

Author

Topic

malhyp
Starting Member

21 Posts

Posted - 2005-12-12 : 08:30:11

Can anyone suggest whey this SQL wont work?

Someone has suggested that I have a few " in the wrong place.
I am missing the basic understanding about variable / string concatenation.

SELECT SupplierName, Location, ShortDescription FROM Query1 WHERE 'TimberSpecies' LIKE '%" & " + Replace(rsSearchResult__MMColParam, "'", "''") + " & "%' AND 'CategoryTable' LIKE '%" & " + Replace(rsSearchResult__MMColParam2, "'", "''") + " & "%' AND 'Location' LIKE '%" & " + Replace(rsSearchResult__MMColParam3, "'", "''") + " & "%' ORDER BY SupplierName"

Thanks

Mally.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-12-12 : 08:34:01

Why don't you give us what the statement looks like from debug right before it's sent to SQL Server.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

malhyp
Starting Member

21 Posts

Posted - 2005-12-12 : 08:39:38

Sorry, not sure how to do that. Also the SQL I am using is ANSI.

Thanks Mally.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-12-12 : 08:54:26

Actually, you're not. But, try this:


SELECT 
	SupplierName, 
	Location, 
	ShortDescription 
FROM 
	Query1 
WHERE 
	TimberSpecies LIKE '%' + Replace(rsSearchResult__MMColParam, ', ','''') + '%' 
	AND CategoryTable LIKE '%' + Replace(rsSearchResult__MMColParam2, ', ','''') + '%' 
	AND Location LIKE '%' + Replace(rsSearchResult__MMColParam3, ', ','''') + '%' 
ORDER BY 
	SupplierName

I'm not sure if this is what you are looking for, but that code will at least compile. If you need more help, check out this weblog entry:

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

We should be able to help you better if you post in that format.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources