Author |
Topic |
OBINNA_EKE
Posting Yak Master
234 Posts |
Posted - 2006-01-04 : 20:08:37
|
This works ShowComboLocation 'SELECT * FROM dbo.iViewAll 'but this one does not work ShowComboLocation 'SELECT * FROM dbo.iViewAll WHERE PROPERTYTOWN LIKE ' %FRANCE%' 'it is showing Server: Msg 170, Level 15, State 1, Line 1Line 1: Incorrect syntax near '%'.Pls help |
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-01-04 : 20:20:26
|
Try this:ShowComboLocation 'SELECT * FROM dbo.iViewAll WHERE PROPERTYTOWN LIKE ''%FRANCE%'' 'Note that this '' is two single quotes, not a double quote.Edit: FIRST!!!CODO ERGO SUM |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-01-04 : 20:21:13
|
use double single quote ShowComboLocation 'SELECT * FROM dbo.iViewAll WHERE PROPERTYTOWN LIKE ''%FRANCE%'' ' -----------------[KH]2006 a new beginning |
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-01-04 : 20:21:52
|
Write it as followsShowComboLocation 'SELECT * FROM dbo.iViewAll WHERE PROPERTYTOWN LIKE ''%FRANCE%'' '(ie. single quotes within the string should be replaced with 2 single quotes) |
|
|
OBINNA_EKE
Posting Yak Master
234 Posts |
Posted - 2006-01-05 : 15:26:51
|
Guys none of the solution worked pls help, |
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-01-05 : 16:15:19
|
What is the result? Same Error ? Different Error ? or not returning any value?First try ShowComboLocation 'SELECT * FROM dbo.iViewAll' and c whether that works. |
|
|
OBINNA_EKE
Posting Yak Master
234 Posts |
Posted - 2006-01-05 : 16:20:28
|
It worked but does not work with [ LIKE ''%FRANCE%'' ' ]Pls help |
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-01-05 : 18:02:37
|
Hi OBINNA_EKE,When answering can u answer it in a bit detail? Its very difficult to do mind reading.What do u mean by "does not work" ?Is it giving an error?Is it not returning any result ?Did u copy & paste what v gave right away or did u type it urself ? |
|
|
OBINNA_EKE
Posting Yak Master
234 Posts |
Posted - 2006-01-05 : 18:49:55
|
This is the SPCREATE PROCEDURE [dbo].[ShowComboLocationx]@Keyword varchar(2000) ASEXEC(@keyword)GOit is showingServer: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near '%'. |
|
|
OBINNA_EKE
Posting Yak Master
234 Posts |
Posted - 2006-01-05 : 19:21:53
|
ShowComboLocationx 'SELECT * FROM dbo.iViewAll WHERE PROPERTYTOWN LIKE ''%FRANCE%'' ' |
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-01-05 : 19:32:38
|
quote: Originally posted by Srinika Did u copy & paste what v gave right away or did u type it urself ? please answerin query analyzer copy & paste & run the following :ShowComboLocationx 'SELECT * FROM dbo.iViewAll WHERE PROPERTYTOWN LIKE ''%FRANCE%'' ' I was successfull with the following:use pubsCREATE PROCEDURE [dbo].[ShowComboLocationx]@Keyword varchar(2000)ASEXEC(@keyword)GOShowComboLocationx 'Select * from authors where phone like ''%08%'''
I also get the same error when one-single quote set is sorrounding %FRANCE% |
|
|
OBINNA_EKE
Posting Yak Master
234 Posts |
Posted - 2006-01-06 : 15:40:22
|
Thank you Srinika (you code worked) and all others above that supplied me with code However, the SP executes via Query Analyzer but not in my C# function, pls check the error here http://194.247.250.130/amber/searchrent.aspx?keyword=&Type=S PutLocationsInDropDownList("SELECT * from dbo.iViewAll WHERE PropertyArea LIKE ''%ton%''"); |
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-01-06 : 20:54:40
|
If u do something like that then it should bePutLocationsInDropDownList("SELECT * from dbo.iViewAll WHERE PropertyArea LIKE '%ton%'"); ie. sorround the %ton% with single set of 2 single quotes as'%ton%' |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-01-06 : 21:52:02
|
in SQL, a string is enclosed in single quote not double quote.for exampledeclare @str varchar(100) select @str = 'This is a string' so if you need to include a single quote in the string you need to use the single quote twice to escape it.example select @str = 'This string is with a single quote ''.' in C#, a string is define in double quote. As shown here in the Hello World exampleConsole.WriteLine("Hello World !"); Which means, you don't have to escape the single quote to use it in C#.Console.WriteLine("This string is with a single quote '"); Hope this clear things up for you.-----------------[KH] |
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-01-06 : 22:18:44
|
And also in SQL QA, any text constant which is supposed to be inside single quotes should be in 2 quotes setie.in C#PutLocationsInDropDownList("SELECT * from dbo.iViewAll WHERE PropertyArea LIKE '%ton%' ");in SQL QAExec ('SELECT * from dbo.iViewAll WHERE PropertyArea LIKE ''%ton%'' ') |
|
|
OBINNA_EKE
Posting Yak Master
234 Posts |
Posted - 2006-01-07 : 06:15:18
|
GUYS thanks a lot, I really appreciated all the help I got, PutLocationsInDropDownList("SELECT * from dbo.iViewAll WHERE PropertyArea LIKE '%ton%' ");it still doesn't work, it complains about Line 1: Incorrect syntax near 'LIKE'.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 1: Incorrect syntax near 'LIKE'.I will reverting it my code back (ie fixing the url not to show error ); SQL+C# compiler won this time Any other suggestion will be appreciated |
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-01-07 : 10:18:49
|
This problem seems to be something to do with c#.May be it considers both single quote & dbl quote as text delimeters.Try the following:PutLocationsInDropDownList("SELECT * from dbo.iViewAll WHERE PropertyArea LIKE " & chr(39) & "%ton%" & chr(39) );[ I'm not a C# guy and I assumed that the ampersand (&) does concatenation of the strings, if not use any such instead of & ] |
|
|
|